In: Finance
Monthly adjusted closing prices for Stock A for the months of November, October, Sept, Aug, July, June and May are $98, 100, 90, 100, 110, 105, 100 respectively. The corresponding market index are $100, 105, 110, 115, 118, 120 and 118. Based on the above data,
1. The annualized volatility of stock A is...
2. The beta of stock A is...
3. If the market index goes up from $100 to $120, what is the estimated percentage change in stock A
A | B | C | D | E | F | G | H | I | J |
2 | Price | Return | |||||||
3 | Stock A | Market | Stock A | Market | |||||
4 | May | 100 | 118 | ||||||
5 | June | 105 | 120 | 5.00% | 1.69% | =(E5-E4)/E4 | |||
6 | July | 110 | 118 | 4.76% | -1.67% | ||||
7 | August | 100 | 115 | -9.09% | -2.54% | ||||
8 | September | 90 | 110 | -10.00% | -4.35% | ||||
9 | October | 100 | 105 | 11.11% | -4.55% | ||||
10 | November | 98 | 100 | -2.00% | -4.76% | ||||
11 | Average | -0.04% | -2.69% | =AVERAGE(G5:G10) | |||||
12 | St. Dev. | 8.46% | 2.48% | =STDEV.S(G5:G10) | |||||
13 | Covariance with market | 0.051% | =COVARIANCE.S(F5:F10,G5:G10) | ||||||
14 | 1) | ||||||||
15 | |||||||||
16 | Hence the volatility of the Stock A is | 8.46% | |||||||
17 | |||||||||
18 | 2) | ||||||||
19 | |||||||||
20 | Beta of stock A | = Cov (rA,rM)/ Var(rM) | |||||||
21 | =0.051% / (2.48%^2) | ||||||||
22 | 0.83 | ||||||||
23 | |||||||||
24 | Hence Beta of stock A is | 0.83 | |||||||
25 | |||||||||
26 | 3) | ||||||||
27 | |||||||||
28 | Return of the market | =($120 - $100)/$100 | |||||||
29 | 20.0% | ||||||||
30 | |||||||||
31 | Estimated % change in stock A | =Beta for Stock A*Return on market | |||||||
32 | =0.83*20% | ||||||||
33 | 16.61% | =D24*D29 | |||||||
34 | |||||||||
35 | Estimated % change in stock A | 16.61% | |||||||
36 |