Question

Part 1:

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 is R_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_ivalues 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.5252	789.82	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.7288	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.78	1882.62	0.16083%

Part 2:

Assume your complete portfolio is a single security portfolio; all the money is invested in Amazon stock. Analyze your portfolio’s performance relative to a passive investment strategy of holding Spider S&P 500 ETF (SPY). Your analysis should cover different performance measures, including Sharpe ratio, M² measure, Treynor ratio, Jensen’s alpha, and information ratio. By which measures did your single-security portfolio outperform the market? Which performance measure might be most relevant to your single-security portfolio?

Answer 1

Solution:

Please find below the excel solution.

-x-