Question;This question asks you to test CAPM by looking at the historical performance of small stocks and value stocks using Excel. The data are in CAPM data.xls. We will focus on five risky assets: four stock portfolios called small-low, small-high, big-low, big-high, and a value-weighted stock index that we will treat as the market portfolio. Here ?small? and ?big? refer to market capitalization, while ?low? refers to growth stocks (low book-market ratios), and ?high? refers to value stocks (high book-market ratios). Thus for example the small-low portfolio is a portfolio of growth stocks with small market capitalization. The data set runs from January 1930 to December 2004 and contains excess returns Ri?Rf (where Rf is the return on 90-day Treasury bills) for all five risky portfolios. In parts a)-f) of this problem we will focus on the period 1/1930-12/1963 only. The test on the period 1/1964-12/2004 has already been finished in the spreadsheet for reference and comparison.a) Download the data. What are the average excess returns for the five risky portfolios during 1/1930-12/1963? Use Excel to compute your answers in row 6, columns I-M.b) What are the betas of the five portfolios during 1/1930-12/1963? To answer, in I7 write =SLOPE(B$6:B$413,$F$6:$F$413). This expression will compute the slope coefficient?i of a linear regression Ri? Rf =?i +?i (Rm? Rf) +?iin which the left hand side variable is contained in B$6:B$413 in the spreadsheet (excess return on small-low portfolio), while the right hand side variable is contained in $F$6:$F$413 (excess return on market). Thus, by definition, Excel computes the beta of the small-low portfolio in I7. Repeat the same procedure for columns J-M in row 7 to compute the betas of all other portfolios for 1/1930-12/1963.c) What are the alphas of the five portfolios? To answer, in row 8 let?s compute the intercepts in the linear regression discussed in part b). Use the INTERCEPT command in Excel to do this. (The intercept is, by definition, the alpha.)d) What expected excess returns are predicted by CAPM? According to the CAPM equation we should have E[Ri]? Rf =?i (E[Rm]? Rf). Following this equation, in row 9, compute the product of?i, and E[Rm]? Rf for all five portfolios.e) Compare rows 6 and 9. Do you see a big difference between the CAPM predicted mean excess returns and the actual mean excess returns? You can also do this comparison by looking at the magnitude of the alphas (which are the difference between the predicted and true mean excess returns).f) Plot the security market line predicted by CAPM, as well as the actual position of the five portfolios in (beta, expected return) space. If you successfully finished part a)-f), you should be able to see the plot show up below the table. Does CAPM work in this data? Please turn in a printout of your table and figure with the solution.g) Compare your results with the later time period (1/1964-12/2004). Does CAPM work over the more recent period? Which portfolio has the highest alpha?
Paper#55882 | Written in 18-Jul-2015Price : $27