The following table shows historical stock prices of Spider S&P 500 ETF and Amazon stock. The prices listed are the adjusted closing prices on the first day of each month from 9/1/2016 to 8/1/2018. The monthly risk-free rates for the T-bills are also listed in the table. Using the price data, calculate the monthly returns for the Spider S&P 500 ETF and the Amazon stock, respectively, from September 2016 to July 2018 (note that the monthly return for August 2018 cannot be calculated). For example, the monthly return for SPY in September 2016 can be calculated as r_i = (205.5252 - 208.1)/208.1 = -0.0119 = -1.19%. Its excess return isR_i = r - r_f = -1.19% - 0.015% = -1.1915%.

Apply the single-index model and regression analysis to find the alpha (_i) and beta (_i) for Amazon stock. What is the total risk, systematic risk, and firm-specific risk for that stock?

Note:

The single-index model describes security returns by R_i= +R_M + e_i where R_i=r_{i,/sub>-rf and Rm=rm-rf}

(You need to calculate these: R_i and R_m)

Now you can run the regression Y_i = + X_i + e_i

When you run Regression with Excel, it will ask you to "Input Y Range" & "Input X Range". You need to enter R_i values for Y_i & R_M values for X_i. You also need to check mark "Residuals" to calculate the e_i values. Once you have run the regression analysis, you will see the results of the analysis in three tables: SUMMARY OUTPUT, ANOVA, & RESIDUAL OUTPUT.

Get the relevant values for each item in the following equation.

Variance (R_i) = Variance (_i + _iR_M + ei=_i) = Variance (_iR_M) + Variance (e_i)

2_i= _i²²_M+ ² (e_i)

Or, Total Risk (2_i) = Systematic risk ( _i²²_M) + Firm-specific risk (² (e_i)).

For example, in the ANOVA table, can be found as the Coefficient for the Intercept and as the Coefficient for X Variable 1.

Single-Index Model Data Set Date

Adj Close: SPY

Adj Close: AMZN

Rf (Monthly): T-bills

9/1/2016 208.1 837.31 0.01500%

10/1/2016 205.525 789.8 0.02000%

11/1/2016 213.0964 750.57 0.02417%

12/1/2016 216.1423 768.66 0.03417%

1/1/2017 221.3068 823.48 0.04083%

2/1/2017 230.0023 845.04 0.03917%

3/1/2017 229.2923 886.54 0.05417%

4/1/2017 232.5757 924.99 0.06083%

5/1/2017 235.858 994.62 0.05917%

6/1/2017 236.2097 968 0.06833%

7/1/2017 242.2404 987.78 0.07917%

8/1/2017 242.9471 980.6 0.08000%

9/1/2017 246.6185 961.35 0.08167%

10/1/2017 253.6826 1105.28 0.08167%

11/1/2017 261.4366 1176.75 0.08917%

12/1/2017 263.2616 1169.47 0.09833%

1/1/2018 279.5203 1450.89 0.10583%

2/1/2018 269.3568 1512.45 0.11333%

3/1/2018 260.9286 1447.34 0.13417%

4/1/2018 263.3276 1566.13 0.13583%

5/1/2018 269.728 1629.62 0.13917%

6/1/2018 270.0673 1699.8 0.14750%

7/1/2018 281.33 1777.44 0.15500%

8/1/2018 281.7 1882.62 0.16083%