Stock Return Exercise Student Version 2021 Analysis

Instructions to Calculate Arithmetic and Geometric Mean Returns and Variance

Note to calculate APR, multiply geometric returns by m, the number of periods in a year. To calculate EAR = [(1+geometric mean return)^m - 1] To annualize other statistics, multiply by m for arithmetic returns and variance, and by the square root of m for standard deviation. Note: m may be either 365 or the number of trading days in a year (count the returns and divide by 3). The trading days method is preferred.
1.    Go to Moodle and download and open the spreadsheet “Stock Return Exercise Student Version 2021.XLSX”
2.    Begin on the BASIC tab. On this tab, you may not use any Excel Functions, such as =SUM(B2:B12), you may only use excel formulas, such as =B2+B3+B4+B5, EXCEPT in cells D26 to D29 as a check of your work. 
a.    For each month in 2020 calculate the return (r) and return relative (r + 1). Note that the dates go from most recent to oldest as you go down the column. 
b.    Calculate the Arithmetic and Geometric mean returns in cells D 20 and D21 respectively.
c.    Use the remaining columns (E and F) to help you calculate the variance and standard deviation in cells D22 and D23 respectively.

3.    Go to the DATA tab. Use Yahoo finance to find the daily closing and adjusted closing prices from January 2, 2018 to December 30, 2020 for the S&P 500 index (^GSPC). Enter the ticker into the quote lookup box, go to Historical Data, enter the dates, click Apply, and download the data. This will be our proxy for the market.  I suggest you download these prices into a separate spreadsheet, delete unnecessary columns, and then paste the results into the proper place (Cell Y14) in the “Stock Return Exercise Student Version 2021.XLSX” worksheet. Be careful to have the dates match those already in the worksheet. [For the fun of it, you can go to https://fred.stlouisfed.org/series/GOLDAMGBD228NLBM and download gold prices for the same time frame. You will have to average missing values so there is a price for each date you have stock prices for. You can than analyze gold in the same way you will be analyzing the stock returns.]

a.    Calculate the daily returns and return relatives for each of the 3 stocks, A, B, and C, and for the S&P 500 index. (Note you can enter formulas for one date, copy this down the rows, and then use block copy and paste operations to copy them from one stock to the next – just watch the last or bottom row.) Should you use the close or adjusted close to calculate the returns? Why?
b.    Use this information to calculate the Arithmetic Mean Return, Geometric Mean Return, Variance, and Standard Deviation for the returns (arithmetic) and return relatives (geometric) for each of the 3 stocks, A, B, and C, and for the S&P 500 index. After this, have Excel calculate the skewness and kurtosis of the return and log-return distributions. Finally, calculate the Sharpe Ratio of each stock. To calculate the Sharpe Ratio, average the risk-free rate over the 3 years and then either annualize this by multiplying by 360 (which is the convention with US Treasuries) and using annualized expected returns and standard deviation or use daily expected returns and daily standard deviation. Note that you can use any formula or function available in Excel to do this (you only have to avoid Excel functions on the Basic tab), and that you can enter these formulas for one stock and then block copy and paste them for the remaining stocks.  

Instructions to Calculate Standard Deviation and Sharpe Ratios

c.    Which of the stocks has the highest expected return? Which has the highest growth rate of returns? Which has the highest level of total risk? Be sure to tell me what metric or measure you used to determine your answer to each of these questions. Does there seem to be a relationship between total risk and return? Is this consistent with theory? Are the returns of any of the stocks normally distributed? If they are normally distributed, the calculated skewness should be zero +/- the 2*SES calculation and the calculated kurtosis should be 3 +/- the 2*SEK calculation. Are the log-returns closer to a normal distribution? Which of the stocks has the highest Sharpe Ratio?

4.    Go to the PORTFOLIO RETURNS tab. These columns contain the excess returns for each of the stocks and the S&P 500 index. The excess return is the return less the return on a risk-free asset, the 10-year constant maturity Treasury bond in this case (from column A on the DATA tab).
a.    Compute the excess portfolio returns (column E) for the 3 stocks using the weights given in cells B2 for Stock A, C2 for stock B, and D2 for Stock C. 
b.    Click on Insert in the ribbon bar, Scatter, and select the dots only pattern. Click on Select Data. Enter the Stock name (A, B, C, Portfolio respectively) for the series name, click in the series X values and then swipe the Market values, click in the Series Y values and do the same for Stock A values. When you select these values, be very careful NOT to include the column name, i.e. the “A” or “S&P500” in the data – include only the numbers! Right click on the cloud of dots in the graph and click ADD TRENDLINE. Check the Display equation on chart and display R-Squared value on chart boxes and then close.
c.    Repeat this except using the values for stocks B and then C and then the portfolio instead of A. You should end up with 4 different graphs showing the relationship between each stock, the portfolio and the market returns. Alternatively, you may click on the chart, copy and paste it, then click on Select data and change the Series name and Series Y values.

d.    Click the Data tab in the ribbon bar and then on Data Analysis. Click on Covariance and then OK. For the input range select all of the excess returns including the Row containing their labels, MKT, A, B, C, and Portfolio, but do not include the date column. Click the Labels in First Row check box. Click OK. Change the name of the resulting worksheet to “Covariance”. It might also be helpful to do the same for Correlation – but include the output on the same sheet as the Covariance, to the right of the Covariance table. Which company has the greatest covariance with the market? Which has the least? Which 2 companies have the greatest covariance with each other?

Instructions to create regression analysis, residual plots, and normal probability plots

Note that this should technically be done with the raw returns or log-returns, and not with excess returns, but while using the excess returns might change the absolute value of the covariances, it will not change their relative values. Use beta=Covariance.X(Stock,MKT)/Var.X(MKT) to estimate the beta’s for each of the stocks, the portfolio, and the market itself just below the table of covariance’s. Note that you will have to replace the “X” in the formula with either “P” for population or “S” for sample – which is the appropriate choice here? Do these beta values match those you found in part 4 b and c? Now check this by using the covariance table to estimate the betas for each stock and the portfolio. Are the results the same?

e.    Go back to the Portfolio Returns tab. Click on Data Analysis again and then on regression.  Select the A returns for the Y-range and the MKT returns for the X-range. This time be sure to include the title at the top of the column, i.e. A, B, C, etc. and to click the Labels box in the Regression input box. Click the Residual Plots, Line Fit Plots, and Normal Probability Plots check boxes and then OK. Name the resulting worksheet “reg A on MKT”. 
f.    Repeat 4.e. substituting the returns for B and then for C instead of A and finally for the Portfolio as in 4.c. Name the resulting worksheets “reg B on MKT”, “reg C on MKT”, and “reg P on MKT” respectively.
g.    Which of the stocks (or portfolio) has the greatest percentage of variance in its returns explained by the market returns? How do you know?
h.    Are the beta coefficients significant for any of the four regressions? How do you know? Be specific in your answer. Which stock has the highest Beta?  The lowest? How does this compare with what you learned in part 4.c? 
i.    Are the alpha coefficients significant for any of the four regressions? How do you know – again, be specific in your answer.
j.    Do the residual plots and normal probability plots suggest the distributions are normal? How does this compare with what you learned in part 3.c. using skewness and kurtosis?

5.    What can you tell me about each of the companies using this analysis?
6.    Two of the companies are from the same industry while the third is from another industry. Which 2 companies are from the same industry? What can you tell me about the 2 industries represented by this data?
7.    Using your estimate of beta for each stock and the portfolio, estimate their return next year given an estimated risk free rate of .5% and market return of 8%.

Analysis and Conclusion

8.    Now turn to the PR FF tab. This contains the excess returns on the 3 stocks and the portfolio, along with the Fama French 3 Factor factors. Note that FF use a different risk-free rate (treasury bills) than the one we have been using (10-year constant maturity treasury bonds). The excess returns on this tab were calculated using the FF estimate of the risk-free rate. Repeat 4.e and 4.f. This time, the Y range is still one of the 3 stocks, but the x range will be $G$3:$I$758 – that is, the FF market risk premium, SMB and HML factors. Be sure not to use the same name for the regression output sheet – call these FF A, FF B, FF C, and FF P respectively. The regression output will now have an intercept (alpha), and coefficients for the market risk premium, SMB, and HML factors.
a.    Which of the stocks (or portfolio) has the greatest percentage of variance in its returns explained by this new model? How do you know? How does this compare to the result in part 4. g.?

b.    Are the market risk premiums betas significant for any of the four regressions? How does this compare with the result of 4.h.? Why has the market risk premium beta changed in size?
c.    Are the other beta coefficients significant for any of the four regressions? How do you know? Be specific in your answer. Which stock has the highest coefficient for the for the size premium beta and the value premium beta?  The lowest? 
d.    Are the alpha coefficients significant for any of the four regressions? How do you know – again, be specific in your answer. How does this compare with 4.i.? 

e.    What can you tell me about the 3 companies from this Fama French analysis, and how does it differ from your analysis in part 5 and 6? This website will help: http://www.efficientfrontier.com/ef/101/roll101.htm 
f.    Using your estimates of the betas for each stock and the portfolio, estimate their return next year given the August 2021, Last 3 months, and Last 12 Months risk premiums (market, size, and value) on the PR FF tab in cells L5 to O11. Use the most recent risk-free rate from the PR FF tab. Do not forget to annualize this risk-free rate using the trading days method before you use it in your return calculations. How does this compare with the estimates from part 7 above?

g.    Discuss the use of the CAPM and the Fama-French model to analyze these 3 companies – a few sentences will do.
9.    Use Save AS to change the name of the file by appending your team name at the end of the file name and save it to the desktop (or other location where you can easily find it). Post a copy to me via Moodle. Write out answers to the questions being sure to label the question number (or instruction number), put your team name on it, post it to Moodle along with the spreadsheet. Note that you can do this on a separate tab in your Excel file if you wish.