Q$1.$ 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. $1$Here 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 Q$1$ 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 $\backslash$beta i of a linear regression Ri $$Rf $=\backslash$alpha i$+\backslash$beta i$($Rm$$Rf$)+\backslash$epsi 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 $=\backslash$beta 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.