Q1. CAPM test [65 pts] This question asks you to test CAPM by looking at the historical performance of stocks using Excel. The data are in CAPM data 2022.xlsx 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 1927 to December 2021 and contains excess (simple) returns Ri Rf (where Rf is the return on 90-day Treasury bills) for all five risky portfolios. 1Here is a recommended set of steps that will allow you to answer assessed questions (a)-(d) below. Note: in step 15 we will focus on the period 1/1927-12/1963 only. In step 6, we repeat the same analysis for the period 1/1964-12/2021. Your solution for Q1 should consist of answers to questions (a)-(d) below. 1. Download the data from Canvas and calculate the (arithmetic) average excess returns for the five risky portfolios during the period 1/1927-12/1963. 2. Calculate the betas of the five portfolios during 1/1927-12/1963. Use the SLOPE function in Excel that computes the slope coefficient i of a linear regression Ri Rf =i+i(RmRf)+i Note that you have been provided the excess market returns. 3. Calculate the alphas of the five portfolios during 1/1927-12/1963 using the INTERCEPT function in Excel. (The intercept is, by definition, the alpha.) 4. Calculate the expected excess returns predicted by CAPM for this period. According to the CAPM equation we should have E[Ri] Rf = i(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 step 1 as your estimate of the expected excess market return.) 5. Plot the security market line predicted by CAPM, as well as the actual position of the f ive portfolios in (beta, expected excess return) space. 6. Now repeat the steps above for the time period 1/1964-12/2021. Your solution for Q1 should include the following: (a) Provide tables reporting the mean excess return, beta, alpha, and the CAPM predicted excess return for the five portfolios. Round the numbers to three decimal places. For each of the two time periods you should have a completed table as below: Table 1: CAPM test Small Big Low High Low High Market mean excess return beta alpha CAPM pred. excess return [25 pts] 2(b) Provide graphs of the security market line and the actual position of the five portfolios for both time periods. [25 pts] (c) Provide a brief comment on the difference between the CAPM predicted mean excess returns and the actual mean excess returns in the two periods. You can also do this comparison by looking at the magnitude of the alphas (which represent the difference between the predicted and actual mean excess returns). Compare the two time periods: does CAPM hold in either period? [5 pts] (d) Which portfolio has the highest alpha? Provide a brief comment. Q2. Portfolio choice [35 pts] [10 pts]