Finance Assignment: Calculating Portfolio Returns and Risk

Question 15 pts

In the Excel File:  QPS3 Data for 1 to 12, I downloaded 61 months (January   2015 to January 2020) of monthly adjusted closing prices for the SPDR S&P 500 Index ETF (symbol = SPY), the i Shares 1–3 Year Treasury Bond ETF (symbol = SHY), Microsoft Corporation (symbol = MSFT), and Exxon Mobil Corporation (symbol = XOM). I used end of month data.

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?  Enter your answer rounded to two decimal places.  Do not enter % in the answer box.  For example, if your answer is 0.12345 or 12.345% then enter as 12.35 in the answer box.

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?  Enter your answer rounded to two decimal places.  Do not enter % in the answer box.  For example, if your answer is 0.12345 or 12.345% then enter as 12.35 in the answer box.

3.  Regress excess Microsoft returns on the excess SPDR S&P 500 Index ETF returns. What is Microsoft’s alpha? Enter your answer rounded to two decimal places.  Do not enter % in the answer box. For example, if your answer is 0.12345 or 12.345% then enter as 12.35 in the answer box.

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.  Enter your answer rounded to two decimal places.  For example, if your answer is 123.45% or 1.2345 then enter as 1.23 in the answer box. 

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? Enter your answer rounded to two decimal places.  Do not enter % in the answer box.  For example, if your answer is 0.12345 or 12.345% then enter as 12.35 in the answer box.

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.  Do not enter % in the answer box.  For example, if your answer is 0.12345 or 12.345% then enter as 12.35 in the answer box. 
8.  Using your regression results from problem 3, what is Exxon’s beta? Note that beta is shown as a number rather than a percentage.  Enter your answer rounded to two decimal places.  For example, if your answer is 123.45% or 1.2345 then enter as 1.23 in the answer box.
9.  Using your regression results from problem 3, 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? Enter your answer rounded to two decimal places.  Do not enter % in the answer box.  For example, if your answer is 0.12345 or 12.345% then enter as 12.35 in the answer box. 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. Enter your answer rounded to four decimal places.  For example, if your answer is 123.456% or 1.23456 then enter as 1.2346 in the answer box.

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. Enter your answer rounded to four decimal places.  For example, if your answer is 12.345% or .12345 then enter as 0.1235 in the answer box.