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)=Rf+pE(RM)Rfd Excel Master coverage online Because CAPM is an equation, we can subtract the riskffec rale from both sides, which gives us: E(R1)Rj(E(RN)Rj) This equation is deterministic, that is, exact. In a regression, we realize that there is some indeferminate ertor. We necd to formally recognize this in the equation by addine epsilon, which represents this error: E(R1)Rf=E(RM)Rj]+ Finally, think of the above equation in a regression. Because there is no intercepe 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(R2)Ryi+(E(RM)Rj+t This equation is oficn called the "market" model, though it is not the only equation with that name, which is a sevirce of confasion. The intercept term is known as Jenser's alpha, and it represents the "excess" return. Ir CAPM holds cxactly, this intereept should be zero. If you think of alpha in terms of the SML. if the alpha is positive, the Mock plots above the SMI, and if the alpha is negative, the stock plots below the SMI. a. You want to estimate the market model for an individual stock and a mutual fund. Firs, 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 $00. Next, go to the Federal Reserve Bank of St. Louis website at www,stlouisfed.org. You should find the "FRFD" database there. Look for the " 1 -Month Treasury Constant. Maturity Rate" and download these data. This series will be the proxy for the riskfree rate. When using this rate, you should be aware that this interest rate is the annualized interest rate. Because we are using monthly stock returns, you will need to adjust the 1 -month T bill rate. For the stock and mutual fund you seiect, 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 higher Rsquared? 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 remembet back to basie statisties, the residuals are the disance from each observation to the regression line. In this context, the reviduals are the part of the monthly return that in not explained by the market model csumate. 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 iniended to measure? Pape 39y 2. Calculate the appraisal ratios for the stock and the mutual fund. Which has a better appraisal ratio? 3. Often, the apprassal ratio is used to evaluate the performance of mutual fund managens. Why do you think the appraisal ratio is used more often for mutual funds, which are portfolios, than for individual stucks? Pree 394
