Question: Please use the data provided in the excel file and answer the following questions: a) Compute the following S&P 500, Stock A and Stock B:
Please use the data provided in the excel file and answer the following questions:
a) Compute the following S&P 500, Stock A and Stock B:
i. Monthly returns and Annualized returns
ii. Monthly Standard Deviation and Annualized Standard Deviation.
b) Which stock (Stock A or Stock B) is most risky if they are held alone and not in a portfolio?
c) Now consider the following two portfolios:
X: A portfolio with a weight of 99% in S&P 500 and 1% in Stock A
Y: A portfolio with a weight of 99% in S&P 500 and 1% in Stock B.
Compute the annual standard deviation of portfolio X and Y.
d) How does each stock affect the variability of the portfolio and how does it compare with your answer in (b) above?
e) Perform a regression for each stock on the S&P 500 returns and explain your
i. results in (b) and (c). (Note: When you use the regression function in Excel, Y is the stock returns and X is the S&P 500 returns).
| Date | S&P 500 | Stock A | Stock B |
| 1/2/13 | -1.70% | 6.13% | 1.66% |
| 2/2/13 | -2.31% | 9.87% | -13.27% |
| 3/2/13 | 4.37% | -1.37% | 10.55% |
| 4/2/13 | -5.06% | 6.87% | 1.01% |
| 5/2/13 | -1.19% | 2.17% | -4.26% |
| 6/2/13 | -7.15% | -23.97% | -11.37% |
| 7/2/13 | -8.23% | 1.64% | -9.66% |
| 8/2/13 | 0.64% | 7.71% | 7.35% |
| 9/2/13 | -10.14% | -31.48% | -15.36% |
| 10/2/13 | 7.35% | 0.57% | -8.18% |
| 11/2/13 | 5.96% | -4.81% | 25.44% |
| 12/2/13 | -5.50% | 9.09% | -9.91% |
| 1/3/13 | -2.46% | 0.59% | 3.90% |
| 2/3/13 | -1.72% | -5.78% | 0.92% |
| 3/3/13 | 0.89% | -19.17% | 14.70% |
| 4/3/13 | 8.12% | -12.68% | 15.19% |
| 5/3/13 | 6.18% | 21.02% | 0.06% |
| 6/3/13 | 1.48% | 9.15% | 9.24% |
| 7/3/13 | 2.18% | -4.54% | 7.78% |
| 8/3/13 | 2.34% | -3.86% | -1.86% |
| 9/3/13 | -1.06% | 15.78% | 0.97% |
| 10/3/13 | 5.89% | 21.47% | 16.70% |
| 11/3/13 | 1.51% | 14.93% | 1.42% |
| 12/3/13 | 4.39% | 5.34% | -3.75% |
| 1/4/13 | 2.20% | 1.56% | -7.19% |
| 2/4/13 | 1.40% | 4.52% | 10.73% |
| 3/4/13 | -1.20% | -1.99% | -0.55% |
| 4/4/13 | -2.56% | 7.06% | -13.15% |
| 5/4/13 | 1.24% | -13.23% | 4.08% |
| 6/4/13 | 2.00% | 20.27% | -3.36% |
| 7/4/13 | -3.88% | 6.45% | -4.37% |
| 8/4/13 | 0.11% | 4.93% | 1.98% |
| 9/4/13 | 1.91% | -9.88% | 1.46% |
| 10/4/13 | 1.66% | 1.21% | -5.90% |
| 11/4/13 | 4.43% | 9.83% | 7.57% |
| 12/4/13 | 3.34% | 3.93% | 1.84% |
| 1/5/13 | -2.74% | 2.32% | 1.14% |
| 2/5/13 | 2.09% | 1.90% | 7.76% |
| 3/5/13 | -1.86% | -1.66% | -3.17% |
| 4/5/13 | -2.66% | -3.25% | -7.48% |
| 5/5/13 | 3.59% | 6.34% | 6.66% |
| 6/5/13 | 0.99% | -4.96% | 3.02% |
| 7/5/13 | 4.22% | 5.72% | 5.53% |
| 8/5/13 | -0.78% | 0.76% | -5.65% |
| 9/5/13 | 0.93% | -1.10% | -5.07% |
| 10/5/13 | -2.19% | 2.38% | -4.12% |
| 11/5/13 | 3.82% | 4.73% | 8.39% |
| 12/5/13 | 0.19% | 7.09% | -1.18% |
| 1/6/13 | 3.90% | 6.08% | 5.05% |
| 2/6/13 | -0.36% | 4.96% | -4.29% |
| 3/6/13 | 1.76% | -0.61% | 3.99% |
| 4/6/13 | 1.15% | 3.93% | -6.59% |
| 5/6/13 | -3.30% | 0.26% | -5.94% |
| 6/6/13 | -0.19% | 4.88% | -2.32% |
| 7/6/13 | -0.28% | 9.96% | 3.26% |
| 8/6/13 | 2.30% | 2.65% | 8.56% |
| 9/6/13 | 1.81% | -4.76% | 12.07% |
| 10/6/13 | 3.60% | 1.92% | 13.93% |
| 11/6/13 | 2.13% | 1.71% | 3.20% |
| 12/6/13 | 0.91% | 1.91% | 1.87% |
Step by Step Solution
There are 3 Steps involved in it
Get step-by-step solutions from verified subject matter experts
Study Help