Transcribed image text : Finding Beta with CAPM Note: This information is also in your fertbook. 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 ised. To derive the market model, start with the CAPM: E ( A j ? ) = R f ? + q [ ? E ( P N ? ) ? ? R d ? Since CAPM is an equation, you can subtract the risk.free rate from both sides, which gives you: E ( A i ? ) ? R r ? = ? ( E ( P k ? b ? R d ? This equation is deterministo-that is, exact, In a rearession, voull realize that there's some indeterminate error, You need to formally recoanize this in the equation by adding epsilon, which represents this error: E ( A j ? ) ? A i ? = ? ? E ( A d ? ) ? A d ? + E Finally, think of the above equation in a regrestion. Since there's no intercept in the equation, the intercept is zero. However, when you estimate the regression equation, you can add an intercept term, which is called alpha here: E ( A j ? ) ? A f ? = ? i ? + ? [ E ( R M ? ) ? A j ? + c The intercept term is known as Jensen's alpha. and it represents the "excess" return. If CAPM holds exactly, this intercept should be zero. Thirk of alpha in teems of the SML: If the alpha is positive, the stock plots above the SML; it the alpha is negative, the stock plots beiow the SML. Your first create a scatter plot and then perform a regression analysis for ElY stock and the mutual fund. Then, use those resuls to compare and analyze the results. A. Scatter Ploting and Regression Analysis Use the following steps to create the scatter plot: 1. Go to the Part 4 Stock Data tab in your Excel spreadsheet. Highlight column K-M headings, then hold down theCtrl button and select cels K3 through M ? 62. 2. Go to the Insert tab, click on Scatter Chart, and select the first style. Risk Premium numbers ( x -axes fange) and the number tor the asset you're comparing as they-axes range. Output to a new worksheet (do not type a name in the text box). Select the checkboxes for Labels, Confidence Level, and Residuals. Click OK . [ An image of Pegression dalogue bex] 4. Your regression analysis will open in a new worksheet. Rename the worksheet based on the premium being compared to the SsP premium, for example: "ELY Regression Analysis." B. Answer the following questions: 1. In this regression, A i ? is the return on the stock, and A n ? is the risk-free rate for the same period. AMr is the return on a stock market index, such as the SSP 500 index. a is the regression intercept, and A is the siope (and the stock's entimated beta). 4 represents the residuals for the regression. The intercept, ? i ? , is often caled Jensen's alpha. What does in measure? it an asset has a positive Jensen's alpha, where would is plot with respect to the SML? A t ? ? A ij ? = ? 1 ? + ? i ? ? A M e ? ? A M ? ? + ? f ? 2. Is the alpha of eather ELY or the musual fund signiticantly more of less than zero? Hint: The alpha is the intercept.) 3. How do you interpret the beta for the stock and the mutual fund? Phint: The beta is next to the coefficient.) 4. Which of the two regression estimates has the highest f.squared? Is thes what you would have expected? Use the scatterpiot to explain why. [An image of a dialogue box of quick layout with 11 layouts.] 5. Move the y formulas and the R 2 to the bottom right-hand comer of the chart. Now, use the following steps to create a regression analysis for ELY and for the Mutual Fund: 1. First. check to see that you have the ability to run the analysis. Go to the Data tab in Excel, and look for the Data Analysis feature shown in the following image. [An image of the Excel sheet menu bar with the data analysis seiection under data tab.] If you don't see Dala Analysis, you might need to add Analysis Toolpak in Excel. For instructions on how to load the Analysis Toolpak inte your version of Excel, visit https:iisupport.office.comien-usiarticle-Load-the-Analysis-ToolPak-6a63e598-cd6d-42e3-9317-6b40ba1a66b4. (support. office.comien-us/article-Load-the-Analysis-ToolPak-6a63e598-cd6d-42e3-9317-6b40ba1a66b4) . Contact Microsoft Support if you have any issues with this add in. Once you've completed all these steps, you can continue with your projoct. 2. Click on Data Analysis. A dialog box with a list of analysis tools will open. Select Regression from the list and click OK. An image of data analysis thalogue box with regression option selected in it. 3. Next, another dialog box opens for you to select your Inputs and Output for the regression. Select the input data ranges by highlighting S\&P Transcribed image text : Finding Beta with CAPM Note: This information is also in your fertbook. 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 ised. To derive the market model, start with the CAPM: E ( A j ? ) = R f ? + q [ ? E ( P N ? ) ? ? R d ? Since CAPM is an equation, you can subtract the risk.free rate from both sides, which gives you: E ( A i ? ) ? R r ? = ? ( E ( P k ? b ? R d ? This equation is deterministo-that is, exact, In a rearession, voull realize that there's some indeterminate error, You need to formally recoanize this in the equation by adding epsilon, which represents this error: E ( A j ? ) ? A i ? = ? ? E ( A d ? ) ? A d ? + E Finally, think of the above equation in a regrestion. Since there's no intercept in the equation, the intercept is zero. However, when you estimate the regression equation, you can add an intercept term, which is called alpha here: E ( A j ? ) ? A f ? = ? i ? + ? [ E ( R M ? ) ? A j ? + c The intercept term is known as Jensen's alpha. and it represents the "excess" return. If CAPM holds exactly, this intercept should be zero. Thirk of alpha in teems of the SML: If the alpha is positive, the stock plots above the SML; it the alpha is negative, the stock plots beiow the SML. Your first create a scatter plot and then perform a regression analysis for ElY stock and the mutual fund. Then, use those resuls to compare and analyze the results. A. Scatter Ploting and Regression Analysis Use the following steps to create the scatter plot: 1. Go to the Part 4 Stock Data tab in your Excel spreadsheet. Highlight column K-M headings, then hold down theCtrl button and select cels K3 through M ? 62. 2. Go to the Insert tab, click on Scatter Chart, and select the first style. Risk Premium numbers ( x -axes fange) and the number tor the asset you're comparing as they-axes range. Output to a new worksheet (do not type a name in the text box). Select the checkboxes for Labels, Confidence Level, and Residuals. Click OK . [ An image of Pegression dalogue bex] 4. Your regression analysis will open in a new worksheet. Rename the worksheet based on the premium being compared to the SsP premium, for example: "ELY Regression Analysis." B. Answer the following questions: 1. In this regression, A i ? is the return on the stock, and A n ? is the risk-free rate for the same period. AMr is the return on a stock market index, such as the SSP 500 index. a is the regression intercept, and A is the siope (and the stock's entimated beta). 4 represents the residuals for the regression. The intercept, ? i ? , is often caled Jensen's alpha. What does in measure? it an asset has a positive Jensen's alpha, where would is plot with respect to the SML? A t ? ? A ij ? = ? 1 ? + ? i ? ? A M e ? ? A M ? ? + ? f ? 2. Is the alpha of eather ELY or the musual fund signiticantly more of less than zero? Hint: The alpha is the intercept.) 3. How do you interpret the beta for the stock and the mutual fund? Phint: The beta is next to the coefficient.) 4. Which of the two regression estimates has the highest f.squared? Is thes what you would have expected? Use the scatterpiot to explain why. [An image of a dialogue box of quick layout with 11 layouts.] 5. Move the y formulas and the R 2 to the bottom right-hand comer of the chart. Now, use the following steps to create a regression analysis for ELY and for the Mutual Fund: 1. First. check to see that you have the ability to run the analysis. Go to the Data tab in Excel, and look for the Data Analysis feature shown in the following image. [An image of the Excel sheet menu bar with the data analysis seiection under data tab.] If you don't see Dala Analysis, you might need to add Analysis Toolpak in Excel. For instructions on how to load the Analysis Toolpak inte your version of Excel, visit https:iisupport.office.comien-usiarticle-Load-the-Analysis-ToolPak-6a63e598-cd6d-42e3-9317-6b40ba1a66b4. (support. office.comien-us/article-Load-the-Analysis-ToolPak-6a63e598-cd6d-42e3-9317-6b40ba1a66b4) . Contact Microsoft Support if you have any issues with this add in. Once you've completed all these steps, you can continue with your projoct. 2. Click on Data Analysis. A dialog box with a list of analysis tools will open. Select Regression from the list and click OK. An image of data analysis thalogue box with regression option selected in it. 3. Next, another dialog box opens for you to select your Inputs and Output for the regression. Select the input data ranges by highlighting S\&P