excel 2

Use the space below to identify all the terms in the equation:

The single index model may be interpreted as a single-variable regression equation of Ri on the market excess return RM. Identify the intercept, slope and error term in the space below:

2. Use the Excel spreadsheet provided in Isidore's resource section labeled "Calculation of beta - Tesla Motors.xls" to estimate the CAPM parameters for Tesla's  stock. For this you will do the following:

1. Compute excess returns for Tesla's common and ordinary stock and for the S&P500 index.

1. Plot in a chart the S&P500 excess returns (horizontal axis) and Tesla excess returns (vertical axis). Using a ruler draw a line that best fits the relation between the S&P500 and Tesla excess returns.

1. Estimate CAPM parameters:
   1. Estimate CAPM’s beta for Tesla using the formula. By definition beta is the covariance between the asset and the market excess returns divided by the variance of the market excess returns.
   2. Estimate CAPM’s beta for Tesla using the =SLOPE function in Excel
   3. Regress excess market returns on excess Tesla returns using the Data Analysis Toolpack in Excel. Request test statistics at the 95% significance and model residuals
   4. Write the regression equation for Tesla's excess returns (include t-statistics for each coefficient in parentheses) and make a prediction for Tesla's return next month if the S&P500 returns increase by 1%. Express your answer in annualized terms (EARs).
   5. Repeat this exercise for GM’s stock.

1. Using Tesla's regression equation, draw Tesla's Security Characteristic Line. Plot Tesla's most recent monthly return in the chart, calculate alpha for that month and determine whether the stock is over or underpriced.