1.Using the data I provided, calculate 60 months of returns for the SPDR S&P 500 Index ETF, Microsoft, Exxon, and the iShares 1-3 Year Treasury Bond ETF. (Please compute simple monthly returns not continuously compounded returns.) Compute the average monthly returns for the SPDR S&P 500 Index ETF, Microsoft, Exxon, and the iShares 1-3 Year Treasury Bond ETF. If you form an equally weighted portfolio with these three equities and one bond, what is your portfolio's average monthly return?

2.Using the data from problem 1, calculate excess returns for the SPDR S&P 500 Index ETF, Microsoft and Exxon. Use the monthly returns on the iShares 1-3 Year Treasury Bond ETF as your monthly risk-free return. Compute the average monthly excess returns for the SPDR S&P 500 Index ETF, Microsoft and Exxon. If you form an equally weighted portfolio with these three equities, what is your portfolio's average monthly excess return?

3.Regress excess Microsoft returns on the excess SPDR S&P 500 Index ETF returns. What is Microsoft's alpha?

4.Using your regression results from problem 3, what is Microsoft's beta? Note that beta is shown as a number rather than a percentage.

5.Using your regression results from problem 3, determine whether Microsoft's alpha and beta are different from zero at the 10% level of significance. How many of Microsoft's alpha and beta are significant at the 10% level of significance.Enter 0, 1, or 2 in the answer box.

6.Use equation 8.10 to decompose total risk for Microsoft into systematic risk and firm-specific risk. That is, calculate total risk, systematic risk and firm-specific risk for Microsoft. What percentage of Microsoft's total variance is Microsoft's firm-specific variance?

7.Regress excess Exxon returns on the excess SPDR S&P 500 Index ETF returns. What is Exxon's alpha? Enter your answer rounded to two decimal places.

8.Using your regression results from problem 7, what is Exxon's beta? Note that beta is shown as a number rather than a percentage.

9.Using your regression results from problem 7, determine whether Exxon's alpha and beta are different from zero at the 10% level of significance. How many of Exxon's alpha and beta are significant at the 10% level of significance.Enter 0, 1, or 2 in the answer box.

10.Use equation 8.10 to decompose total risk for Exxon into systematic risk and firm-specific risk. That is, calculate total risk, systematic risk and firm-specific risk for Exxon. What percentage of Exxon's total variance is Exxon's firm-specific variance?

11.Use equation 8.10 to estimate the covariance of Microsoft and Exxon excess returns. Note that covariance is shown as a number rather than a percentage.

12.Use equation 8.10 to estimate the correlation of Microsoft and Exxon excess returns. Note that correlation is shown as a number rather than a percentage.

Data for Index Models Problem on QPS #3

Ticker Name of Exchange Traded Fund or Stock

1 SPY SPDR S&P 500 ETF

2 SHY iShares 1-3 Year Treasury Bond ETF

3 MSFT Microsoft Corporation

4 XOM Exxon Mobil Corporation

Adjusted Closing Prices:

Date SPY SHY MSFT XOM

1/1/16$175.89$79.55$50.27$60.44

2/1/16$175.74$79.60$46.43$62.23

3/1/16$186.60$79.71$50.76$65.49

4/1/16$188.30$79.73$45.83$69.25

5/1/16$191.50$79.64$48.71$69.74

6/1/16$191.18$80.12$47.36$74.05

7/1/16$199.18$80.08$52.46$70.27

8/1/16$199.42$79.89$53.18$68.84

9/1/16$198.43$80.00$53.64$69.54

10/1/16$195.97$79.96$55.80$66.38

11/1/16$203.19$79.58$56.12$69.55

12/1/16$206.09$79.58$58.26$72.55

1/1/17$211.02$79.77$60.61$67.43

2/1/17$219.31$79.75$59.99$65.36

3/1/17$218.63$79.80$62.12$66.52

4/1/17$221.76$79.95$64.58$66.23

5/1/17$224.89$80.00$65.88$65.30

6/1/17$225.23$79.94$65.39$66.10

7/1/17$230.98$80.08$68.97$65.54

8/1/17$231.65$80.24$70.93$62.50

9/1/17$235.15$80.09$71.04$67.78

10/1/17$241.89$80.02$79.33$68.91

11/1/17$249.28$79.84$80.27$68.86

12/1/17$251.02$79.73$81.99$69.79

1/1/18$266.53$79.67$91.07$72.84

2/1/18$256.84$79.51$89.88$63.20

3/1/18$248.80$79.70$87.89$62.89

4/1/18$251.09$79.51$90.06$65.54

5/1/18$257.19$79.78$95.18$68.48

6/1/18$257.51$79.79$95.37$70.44

7/1/18$268.25$79.74$102.60$69.40

8/1/18$276.81$80.01$108.64$68.26

9/1/18$277.21$79.90$111.04$73.14

10/1/18$259.22$80.02$103.70$68.54

11/1/18$264.03$80.31$107.66$68.39

12/1/18$239.38$80.81$99.04$59.25

1/1/19$260.06$81.27$101.83$63.68

2/1/19$268.49$81.19$109.24$68.67

3/1/19$272.15$81.70$115.49$70.99

4/1/19$284.51$81.84$127.89$70.53

5/1/19$266.37$82.44$121.12$62.18

6/1/19$283.53$82.81$131.67$68.10

7/1/19$289.21$82.77$133.94$66.08

8/1/19$284.37$83.42$135.50$60.86

9/1/19$288.57$83.31$137.11$63.53

10/1/19$296.31$83.58$141.39$60.79

11/1/19$307.04$83.54$149.29$61.30

12/1/19$314.41$83.54$156.05$63.54

1/1/20$315.83$84.29$168.45$56.57

2/1/20$290.83$84.90$160.31$46.84

3/1/20$253.02$85.96$156.48$35.07

4/1/20$286.83$86.22$177.82$42.92

5/1/20$300.50$86.18$181.82$42.00

6/1/20$304.49$86.21$202.49$42.11

7/1/20$323.84$86.30$203.98$39.62

8/1/20$346.44$86.29$224.40$37.61

9/1/20$332.14$86.29$209.78$32.96

10/1/20$325.15$86.26$201.94$31.32

11/1/20$360.52$86.28$213.51$36.61

12/1/20$372.29$86.29$222.42$40.53

1/1/21$370.07$86.37$231.96$44.09