please aolve it with excel formulas on excel

Excel Master It! Problem The CAPM is one of the most thoroughly researched models in financial economics. When beta is estimated in practice, a variation of CAPM called the market model is often used. To derive the market model, we start with the CAPM: E(Ri)=Ri+[E(Rw)Rf] Because CAPM is an equation, we can subtract the risk-free rate from both sides, which gives us: E(R0)Rf=[E(RM)Rf] This equation is deterministic, that is, exact. In a regression, we realize that there is some indeterminate error. We need to formally recognize this in the equation by adding epsilon, which represents this error: E(Ri)Rj=E(RM)Rj+E Finally, think of the above equation in a regression. Because there is no intercept in the equation, the intercept is zero. However, when we estimate the regression equation, we can add an intercept term, which we will call alpha: E(Ri)Rf=i+[E(Rw)Rf]+ This equation, known as the market model, is generally the model used for estimating beta. The intercept term is known as Jensen's Page369 alpha, and it represents the excess return. If CAPM holds exactly, this intercept should be zero. If you think of alpha in terms of the SML. if the alpha is positive, the stock plots above the SML and if alpha is negative, the stock plots below the SML. a. You want to estimate the market model for an individual stock and a mutual fund. First, go to finance.yahoo.com and download the adjusted prices for the last 61 months for an individual stock, a mutual fund, and the S\&P 500. Next, go to the St. Louis Federal Reserve website at www.stlouisfed.org. You should find the FRED database there. Look for the 1-Month Treasury Constant Maturity Rate and download this data. This series will be the proxy for the risk-free rate. When using this rate, you should be aware that this interest rate is the annualized interest rate, so while we are using monthly stock returns, you will need to adjust the one-month T-bill rate. For the stock and mutual fund you select, estimate the beta and alpha using the market model. When you estimate the regression model, find the box that says "Residuals" and check this box when you do each regression. Because you are saving the residuals, you may want to save the regression output in a new worksheet. 1. Are the alpha and beta for each regression statistically different from zero? 2. How do you interpret the alpha and beta for the stock and the mutual fund? 3. Which of the two regression estimates has the highest R-squared? Is this what you would have expected? Why? b. In part (a), you asked Excel to return the residuals of the regression, which is the epsilon in the regression equation. If you remember back to basic statistics, the residuals are the linear distance from each observation to the regression line. In this context, the residuals are the part of the monthly return that is not explained by the market model estimate. The residuals can be used to calculate the appraisal ratio, which is the alpha divided by the standard deviation of the residuals. 1. What do you think the appraisal ratio is intended to measure? 2. Calculate the appraisal ratios for the stock and the mutual fund. Which has a better appraisal ratio? 3. Often, the appraisal ratio is used to evaluate the performance of mutual fund managers. Why do you think the appraisal ratio is used more often for mutual funds, which are portfolios, than for individual stocks