Fin 380 Group Project: The CAPM and the Index Model
The project has two parts. In the first part, you need to calculate the monthly returns for Google stock and the market index from 2010 to 2017. The second part applies the CAPM to estimate the beta and to evaluate the performance of Google. Each group should submit a written report that explains the methodology used for data analysis and interprets the main results.
Data
Download the month-end price data for Google and S&P500 from Yahoo!Finance over the period Jan 2010 to December 2017.
Download the excel spreadsheet (Fama-French 3factors.CSV) from Canvas. The spreadsheet already contains the one-month T-bill rate (column RF) over the same period downloaded from Professor Kenneth French’s website: http://mba.tuck.dartmouth.edu/pages/faculty/ken.french/data_library.html.
Outline of Report: CAPM Analysis (2 to 3 pages)
Use standard format: 12 point font, double spaced, and 1 inch margins.
- Use the month-end closing stock prices and market proxy index values from Yahoo!finance to calculate the rates of return to be used in the estimation of the Index Model.
- Estimate the Index Model for Google using the previous 96 monthly rates of return ending December 31, 2017 and the S&P 500 index returns. The excess S&P 500 index returns are the independent variables (plotted on the X axis), and the excess Google returns are the dependent variables.
- The main body of the report should include the main output of the regression analysis for Google: the derived regression equation (estimate for beta), the R-square, and a scatter plot of the excess returns and the fitted regression line (Security Characteristic Line).
- Next insert the Security Market Line (SML) plot in the main body of the report and use it as the fundamental guide to assess the investment opportunity for Google. The report should explain how to construct the SML including the choice of the proxy for the risk-free rate and the market return, the source of beta estimate, and the derivation of each stock’s expected return based on CAPM.
- Highlight any abnormal returns and include it in the overall buy-hold-sell recommendation. Present the results of your calculations as an SML graph with your Google actual expected return clearly plotted.
- Repeat your analysis using Fama-French 3 factors model, and compare the abnormal returns (Alpha) that you obtain from CAPM and Fama-French 3 factors. The point here is to understand the abnormal returns depend on what model you use to benchmark.