Monthly Returns: You now need to calculate the monthly "periodic" returns for all three stocks and the S&P index. Adapting the holding period return formula (End - Beg) / Beg for each period, you need to enter the following formula in cell G4. Note that you skip G3 because the data from the previous month has been omitted.
=(B4-B3)/B3 (B3 and B4 are cell references to the above spreadsheet).
Now copy that formula to the bottom of the data set and repeat the process (using the correct cell references) for the other stocks and the index. Format columns G, H,I, J, and Kas percentages.
YOU WILL USE THE PERIODIC RETURNS FOR ALL PORTFOLIOS STATISTICS.
Step 2) Cumulative Returns:We will now create a "normalized" index for each stock price which will represent cumulative returns over the entire period. You want a starting value of 1 for your index for each stock. So in cell L3, type 1.0000, and in cell L4 type
=L3*(1+G4) = Cumulative return for t-1 times (one plus the monthly return for month t)
Repeat for the other stocks and the index using the correct cell references. These cumulative return columns should be formatted as numbers. For each stock and the index, the row 3 cell should be 1.0000, and subsequent cells should represent the impact of the evolving monthly returns. Note that if your price declined from the 1st month to the 2nd month that your "cumulative return" will be <1.0000.This is because the cumulative return is equal to 1 plus the rate of loss for the first month in decimal form.
YOU WILL USE THE CUMULATIVE RETURNS FOR GRAPHING AND GEO-MEAN CALCULATIONS ONLY.
Step 3) Graphing: The last step is to graph the cumulative returns data for each of your stocks with the index value from step 2) on the y-axis and the "date" on the x-axis. Use a line graph, and allow the lines to cross. Choosing an option that forces the lines not to cross will result in misleading and erroneous graphs.Place this graph on the bottom of your "Monthly Data" sheet.