1. CAPM test [15 pts ] This question asks you to test CAPM by looking at the historical performance of stocks using Excel. The data are in CAPM data. xisx on Canvas. 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 -to - market ratios ), and "high " refers to value stocks (high book -to-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 (simple ) returns R; - R (where R 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. a) Download the data from Canvas . What are the (arithmetic ) average excess returns for the five risky portfolios during 1/1930-12/1963? b) What are the betas of the five portfolios during 1/1930 -12 /1963 ? Use the SLOPE function in Excel that computes the slope coefficient B; of a linear regression Ri - Rf = a; + B;(Rm - Rf)+ E; Note that you have been provided the excess market returns. c) What are the alphas of the five portfolios during 1/1930 -12 /1963 ? Use the INTER- CEPT function in Excel to calculate this. (The intercept is, by definition, the alpha .) d) What expected excess returns are predicted by CAPM for this period? According to the CAPM equation we should have E [R ] - R = P; (E[Rm] - RF ). Compute this for all five portfolios, including the market portfolio. (You can take the average excess return of the market portfolio from part (a) as your estimate of the expected excess market return.) e) 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 represent 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 excess return) space. Does CAPM work in this period ? g) Now test CAPM by following the procedure outlined above for the time period 1/1964 -12 /2004 . Compare your results with the earlier time period . Does CAPM work over the more recent period ? Which portfolio has the highest alpha