Please use the data provided in the excel file and answer the following questions: 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. A. Which stock (Stock A or Stock B) is most risky if they are held alone and not in a portfolio? B. 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. C. How does each stock affect the variability of the portfolio and how does it compare with your answer in (b) above? A B B D 1 Date N 2 3 4 Stock B 1.66% -13.27% 10.55% 1.01% Stock A 6.13% 9.87% -1.37% 6.87% 2.17% -23.97% S&P 500 -1.70% -2.31% 4.37% -5.06% -1.19% -7.15% -8.23% 0.64% -10.14% 1/2/13 2/2/13 3/2/13 4/2/13 5/2/13 6/2/13 7/2/13 8/2/13 9/2/13 . N 5 6 7 8 9 10 11 12 13 14 15 -4.26% -11.37% -9.66% 7.35% 00 10/2/13 11/2/13 12/2/13 1.64% 7.71% -31.48% 0.57% -4.81% 9.09% 0.59% -5.78% -19.17% -12.68% 21.02% -15.36% -8.18% 25.44% -9.91% 3.90% 0.92% 14.70% 1/3/13 2/3/13 3/3/13 16 15.19% 4/3/13 5/3/13 0.06% log 9.15% 9.24% 6/3/13 7/3/13 8/3/13 7.35% 5.96% -5.50% -2.46% -1.72% 0.89% 8.12% 6.18% 1.48% 2.18% 2.34% -1.06% 5.89% 1.51% 4.39% 2.20% 1.40% -1.20% -2.56% 1.24% -4.54% -3.86% 15.78% 7.78% -1.86% 0.97% 9/3/13 17 18 19 20 21 22 23 24 25 26 27 28 29 30 10/3/13 11/3/13 12/3/13 4 21.47% 14.93% 5.34% 1.56% 4.52% -1.99% 7.06% -13.23% 16.70% 1.42% -3.75% -7.19% 10.73% 1/4/13 2/4/13 3/4/13 -0.55% -13.15% 4/4/13 5/4/13 4.08% B C D 2.00% 6/4/13 7/4/13 . 20.27% 6.45% 31 32 33 34 35 36 37 38 -3.88% 0.11% 1.91% 1.66% 8/4/13 9/4/13 10/4/13 11/4/13 12/4/13 1/5/13 2/5/13 3/5/13 4/5/13 4.93% -9.88% 1.21% 9.83% 3.93% 2.32% -3.36% -4.37% 1.98% 1.46% -5.90% 7.57% 1.84% 1.14% 7.76% -3.17% -7.48% 5/5/13 4.43% 3.34% -2.74% 2.09% -1.86% -2.66% 3.59% 0.99% 4.22% -0.78% 0.93% -2.19% 6.66% 6/5/13 7/5/13 8/5/13 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 9/5/13 1.90% -1.66% -3.25% 6.34% 4.96% 5.72% 0.76% -1.10% 2.38% 4.73% 7.09% 6.08% 4.96% -0.61% 3.93% 3.02% 5.53% -5.65% -5.07% -4.12% 10/5/13 11/5/13 8.39% 12/5/13 3.82% 0.19% 3.90% -0.36% 1.76% -1.18% 5.05% 1/6/13 2/6/13 4.29% 3/6/13 10 4/6/13 5/6/13 1.15% -3.30% -0.19% 6/6/13 3.99% -6.59% -5.94% -2.32% 3.26% 8.56% 12.07% 13.93% 56 7/6/13 8/6/13 0.26% 4.88% 9.96% 2.65% -4.76% 1.92% 5 -0.28% 2.30% 1.81% 57 58 59 9/6/13 10/6/13 3.60% 56 7/6/13 -0.28% 9.96% 3.26% 57 8/6/13 2.30% 2.65% 8.56% 58 9/6/13 1.81% -4.76% 12.07% 59 10/6/13 3.60% 1.92% 13.93% 60 11/6/13 2.13% 1.71% 3.20% 61 12/6/13 0.91% 1.91% 1.87% Please use the data provided in the excel file and answer the following questions: 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. A. Which stock (Stock A or Stock B) is most risky if they are held alone and not in a portfolio? B. 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. C. How does each stock affect the variability of the portfolio and how does it compare with your answer in (b) above? A B B D 1 Date N 2 3 4 Stock B 1.66% -13.27% 10.55% 1.01% Stock A 6.13% 9.87% -1.37% 6.87% 2.17% -23.97% S&P 500 -1.70% -2.31% 4.37% -5.06% -1.19% -7.15% -8.23% 0.64% -10.14% 1/2/13 2/2/13 3/2/13 4/2/13 5/2/13 6/2/13 7/2/13 8/2/13 9/2/13 . N 5 6 7 8 9 10 11 12 13 14 15 -4.26% -11.37% -9.66% 7.35% 00 10/2/13 11/2/13 12/2/13 1.64% 7.71% -31.48% 0.57% -4.81% 9.09% 0.59% -5.78% -19.17% -12.68% 21.02% -15.36% -8.18% 25.44% -9.91% 3.90% 0.92% 14.70% 1/3/13 2/3/13 3/3/13 16 15.19% 4/3/13 5/3/13 0.06% log 9.15% 9.24% 6/3/13 7/3/13 8/3/13 7.35% 5.96% -5.50% -2.46% -1.72% 0.89% 8.12% 6.18% 1.48% 2.18% 2.34% -1.06% 5.89% 1.51% 4.39% 2.20% 1.40% -1.20% -2.56% 1.24% -4.54% -3.86% 15.78% 7.78% -1.86% 0.97% 9/3/13 17 18 19 20 21 22 23 24 25 26 27 28 29 30 10/3/13 11/3/13 12/3/13 4 21.47% 14.93% 5.34% 1.56% 4.52% -1.99% 7.06% -13.23% 16.70% 1.42% -3.75% -7.19% 10.73% 1/4/13 2/4/13 3/4/13 -0.55% -13.15% 4/4/13 5/4/13 4.08% B C D 2.00% 6/4/13 7/4/13 . 20.27% 6.45% 31 32 33 34 35 36 37 38 -3.88% 0.11% 1.91% 1.66% 8/4/13 9/4/13 10/4/13 11/4/13 12/4/13 1/5/13 2/5/13 3/5/13 4/5/13 4.93% -9.88% 1.21% 9.83% 3.93% 2.32% -3.36% -4.37% 1.98% 1.46% -5.90% 7.57% 1.84% 1.14% 7.76% -3.17% -7.48% 5/5/13 4.43% 3.34% -2.74% 2.09% -1.86% -2.66% 3.59% 0.99% 4.22% -0.78% 0.93% -2.19% 6.66% 6/5/13 7/5/13 8/5/13 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 9/5/13 1.90% -1.66% -3.25% 6.34% 4.96% 5.72% 0.76% -1.10% 2.38% 4.73% 7.09% 6.08% 4.96% -0.61% 3.93% 3.02% 5.53% -5.65% -5.07% -4.12% 10/5/13 11/5/13 8.39% 12/5/13 3.82% 0.19% 3.90% -0.36% 1.76% -1.18% 5.05% 1/6/13 2/6/13 4.29% 3/6/13 10 4/6/13 5/6/13 1.15% -3.30% -0.19% 6/6/13 3.99% -6.59% -5.94% -2.32% 3.26% 8.56% 12.07% 13.93% 56 7/6/13 8/6/13 0.26% 4.88% 9.96% 2.65% -4.76% 1.92% 5 -0.28% 2.30% 1.81% 57 58 59 9/6/13 10/6/13 3.60% 56 7/6/13 -0.28% 9.96% 3.26% 57 8/6/13 2.30% 2.65% 8.56% 58 9/6/13 1.81% -4.76% 12.07% 59 10/6/13 3.60% 1.92% 13.93% 60 11/6/13 2.13% 1.71% 3.20% 61 12/6/13 0.91% 1.91% 1.87%