Question

I have an excel spreadsheet with a list of stocks and their adjusted closing prices from 1/1/2014 to 1/1/2019.

Assuming I create a portfolio of $10M that is equally weighted, please walk me through how to calculate the five year total return of the portfolio. Also, please explain how I can calculate the volatility (sigma squared) during the time frame.

Answer 1

• The five-year total return for each stock can be calculated as :
  • ((Closing price on 1/1/2019) / (Closing price on 1/1/2014)) - 1
• For calculating the five year total return of an equally weighted portfolio of $10M with N stocks, multiply the five-year total return for each stock with (1/N), or the weight of any one stock in the portfolio. For example, if the portfolio has 10 stocks, multiply the five-year total return of each stock with 1/10
• Volatility can be calculated in the same manner using the STDEV function in Excel. In the number1 argument in the STDEV function, input the list of closing prices of each stock to get the standard deviation of the stock prices for that period.  
• Variance is calculated as the square of STDEV. Basically Variance = STDEV ^ 2

In this way we can calculate both the total return and the volatiltiy