Reference no: EM133070459
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%
|