Pick an asset (stock, ETF, etc.) that has been around for at least 5 years, download the monthly adjusted closing prices and compute monthly returns for the asset and for the market portfolio.
Make sure that you align asset returns with the other variables by date. Conduct the following exercise.
1. Using monthly data, estimate the CAMP equation:
The dependent variable (Y) is the excess return of the stock, and the independent variable (X) is the excess return of the market portfolio.
- a) data (with variable names clearly marked);
- b) regression output;
- c) line fit plot.
In your reports, answer the following questions:
- 1) Report the coefficient estimates, t-statistics, as well as the adjusted R-square;
- 2) Interpret the beta coefficient and comment on the amount of the market risk this security has;
- 3) Interpret the alpha coefficient. Evaluate the performance of this asset. Did it outperform/underperform once its market risk exposure is accounted for;
- 4) If you are the Chief Investment Officer of Vanguard, would you consider incorporating this security into your portfolio? Discuss how you would execute this strategy?
Some frequently asked questions:
1. What time period should I use? Should I use 20 years, 10 years, or 5 years?
Answer: The decision is yours. Generally speaking, if the returns are from a stable distribution, you want to use as many observations as possible to increase the power of the regression and minimize estimation error. On the other hand, if you think the return distribution has changed and that the more recent data are more appropriate to infer future performance, you want to use only the more recent observations. So the optimal time period is a tradeoff between the two issues mentioned above. If you feel that your stock has been pretty stable over the last 20 years, you want to use all 20 years of data. But if you think last 5 years are more representative, you want to use 5 years.
2. Where do I get data on the returns and on the risk free rate?
You can get SP 500 index (GSPC) closing prices from Yahoo finance. Use the monthly adjusted closing prices to compute returns. Similarly, using a stockâ€™s ticker symbol, you can download its adjusted closing price and compute monthly returns. You can align the data series of the market portfolio and the stock by the dates.
You can use the risk free rate posted on Blackboard â€œF-F_Research_Data_Factors 2019â€.1 Again, you need to make sure that you align the risk free rate, the market and stock returns by the dates. Note that the variable â€œrfâ€ is the monthly risk free rate in %. You will need to convert it to decimals if your stock and market returns are in decimals.
3. How do I conduct regression analysis?
In Excel, go to â€œDataâ€, then â€œData Analysisâ€ and select â€œRegressionâ€. You need to specify that the Y variable is the excess return of the stock and X variable is the excess return of the market portfolio.
If you donâ€™t see â€œData Analysisâ€ in your current Excel settings, you can go to â€œFileâ€, select â€œOptionsâ€, then â€œAdd-insâ€, select â€œAnalysis ToolPakâ€. Then repeat the procedures in the prior paragraph.