excel 2

FIN360 Sections 03 and 04

Excel Assignment #3

Assignment value: 50 pts. (Spreadsheet work, 25 pts.; this form, 25 pts.)

Name:_________________________________________________________________

The Single Index Model and the CAPM - Practical Applications

1. The single-index model goes as follows:

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.

1. Using security analysis with the single-index model:

Let’s assume that the only asset we can do fundamental analysis on is Tesla. You can find the optimal portfolio produced by combining Tesla with the market. This is, finding the investment opportunities set that produces the highest Sharpe Ratio.

Therefore, the risky portfolio would have two components, an active part (i.e. Tesla) and a passive part (i.e. the S&P500 ETF). The Sharpe ratio of an optimal portfolio with active and passive components exceeds the Sharpe ratio of the passive portfolio. In other words, it lies outside of the Efficient Frontier.

The Information Ratio measures by how much this portfolio exceeds the passive portfolio, a positive information ratio indicates that an active component benefits the investor’s portfolio. Measure Tesla’s information ratio using the single index regression output as follows: