14. Without using any Excel function, but rather the formulas in Chapter 12 and
ID: 2744369 • Letter: 1
Question
14. Without using any Excel function, but rather the formulas in Chapter 12 and your answers to Question 10 and 11, a) Calculate the beta of the two stocks (show your formula work). b) According to these estimates, which company is riskier? c) How do your beta estimates differ from the ones you can get using the “slope” or “linest” functions in excel? Carry out those computations and report them here.
Month
Monthly Returns (%)
Month
Monthly Returns (%)
Month
Monthly Returns (%)
Ended
Yahoo
S&P500
Ended
Yahoo
S&P500
Ended
Yahoo
S&P500
Dec-07
-13.241
-0.219
-0.692
Dec-06
-5.442
-5.018
1.403
Dec-05
-2.610
2.457
0.036
Nov-07
-13.794
-1.980
-4.182
Nov-06
2.544
1.767
1.899
Nov-05
8.818
8.806
3.778
Oct-07
15.861
24.632
1.590
Oct-06
4.193
18.534
3.257
Oct-05
9.249
17.595
-1.668
Sep-07
18.093
10.096
3.736
Sep-06
-12.314
6.174
2.575
Sep-05
1.561
10.650
0.810
Aug-07
-2.237
1.029
1.497
Aug-06
6.227
-2.087
2.376
Aug-05
-0.060
-0.612
-0.912
Jul-07
-14.302
-2.430
-3.097
Jul-06
-17.758
-7.805
0.616
Jul-05
-3.781
-2.172
3.717
Jun-07
-5.470
4.979
-1.660
Jun-06
4.463
12.778
0.133
Jun-05
-6.855
6.088
0.143
May-07
2.354
5.628
3.486
May-06
-3.630
-11.035
-2.875
May-05
7.826
26.032
3.179
Apr-07
-10.387
2.885
4.428
Apr-06
1.612
7.164
1.342
Apr-05
1.770
21.877
-1.896
Mar-07
1.393
1.938
1.116
Mar-06
0.624
7.551
1.244
Mar-05
5.051
-3.979
-1.769
Feb-07
9.007
-10.379
-1.950
Feb-06
-6.735
-16.188
0.271
Feb-05
-8.350
-3.900
2.103
Jan-07
10.846
8.908
1.511
Jan-06
-12.264
4.291
2.648
Jan-05
-6.555
1.468
-2.437
Month
Monthly Returns (%)
Month
Monthly Returns (%)
Month
Monthly Returns (%)
Ended
Yahoo
S&P500
Ended
Yahoo
S&P500
Ended
Yahoo
S&P500
Dec-07
-13.241
-0.219
-0.692
Dec-06
-5.442
-5.018
1.403
Dec-05
-2.610
2.457
0.036
Nov-07
-13.794
-1.980
-4.182
Nov-06
2.544
1.767
1.899
Nov-05
8.818
8.806
3.778
Oct-07
15.861
24.632
1.590
Oct-06
4.193
18.534
3.257
Oct-05
9.249
17.595
-1.668
Sep-07
18.093
10.096
3.736
Sep-06
-12.314
6.174
2.575
Sep-05
1.561
10.650
0.810
Aug-07
-2.237
1.029
1.497
Aug-06
6.227
-2.087
2.376
Aug-05
-0.060
-0.612
-0.912
Jul-07
-14.302
-2.430
-3.097
Jul-06
-17.758
-7.805
0.616
Jul-05
-3.781
-2.172
3.717
Jun-07
-5.470
4.979
-1.660
Jun-06
4.463
12.778
0.133
Jun-05
-6.855
6.088
0.143
May-07
2.354
5.628
3.486
May-06
-3.630
-11.035
-2.875
May-05
7.826
26.032
3.179
Apr-07
-10.387
2.885
4.428
Apr-06
1.612
7.164
1.342
Apr-05
1.770
21.877
-1.896
Mar-07
1.393
1.938
1.116
Mar-06
0.624
7.551
1.244
Mar-05
5.051
-3.979
-1.769
Feb-07
9.007
-10.379
-1.950
Feb-06
-6.735
-16.188
0.271
Feb-05
-8.350
-3.900
2.103
Jan-07
10.846
8.908
1.511
Jan-06
-12.264
4.291
2.648
Jan-05
-6.555
1.468
-2.437
Explanation / Answer
Answer a Month Monthly Returns (%) Month Monthly Returns (%) Month Monthly Returns (%) Ended Yahoo Google S&P500 Ended Yahoo Google S&P500 Ended Yahoo Google S&P500 7-Dec -13.24 -0.219 -0.692 6-Dec -5.44 -5.02 1.403 5-Dec -2.61 2.457 0.036 7-Nov -13.79 -1.98 -4.182 6-Nov 2.544 1.767 1.899 5-Nov 8.818 8.806 3.778 7-Oct 15.861 24.632 1.59 6-Oct 4.193 18.53 3.257 5-Oct 9.249 17.6 -1.67 7-Sep 18.093 10.096 3.736 6-Sep -12.3 6.174 2.575 5-Sep 1.561 10.65 0.81 7-Aug -2.237 1.029 1.497 6-Aug 6.227 -2.09 2.376 5-Aug -0.06 -0.61 -0.91 7-Jul -14.3 -2.43 -3.097 6-Jul -17.8 -7.81 0.616 5-Jul -3.78 -2.17 3.717 7-Jun -5.47 4.979 -1.66 6-Jun 4.463 12.78 0.133 5-Jun -6.86 6.088 0.143 7-May 2.354 5.628 3.486 6-May -3.63 -11 -2.88 5-May 7.826 26.03 3.179 7-Apr -10.39 2.885 4.428 6-Apr 1.612 7.164 1.342 5-Apr 1.77 21.88 -1.9 7-Mar 1.393 1.938 1.116 6-Mar 0.624 7.551 1.244 5-Mar 5.051 -3.98 -1.77 7-Feb 9.007 -10.379 -1.95 6-Feb -6.74 -16.2 0.271 5-Feb -8.35 -3.9 2.103 7-Jan 10.846 8.908 1.511 6-Jan -12.3 4.291 2.648 5-Jan -6.56 1.468 -2.44 TOTAL -1.877 45.087 5.783 -38.48 16.126 14.889 6.064 84.31 5.084 AGV RETURN -0.1564167 3.75725 0.481916667 -3.20667 1.343833 1.24075 0.505333 7.025833 0.423667 CALCULATION OF VARIANCE OF YAHOO (SAY X) CALCULATION OF VARIANCE OF GOOGLE (SAY Y) Year Return (x) X-X(bar) {X-X(bar)}^2 Year Return (Y) Y-Y(bar) {Y-Y(bar)}^2 2005 -0.16 0.80 0.63 2005 3.76 -0.29 0.08 2006 -3.21 -2.25 5.08 2006 1.34 -2.70 7.28 2007 0.51 1.46 2.13 2007 7.03 2.98 8.90 Total -2.86 7.84 Total 12.13 16.26 X(bar) -0.95 VAR(X) 2.61 Y(bar) 4.04 VAR(Y) 5.42 SD (X) 1.62 SD (Y) 2.33 CALCULATION OF VARIANCE OF S & P500 (SAY M) The following formula has been used Year Return (M) M-M(bar) {M-M(bar)}^2 2005 0.48 -0.23 0.05 mean of yahoo X(bar) = Total return/no. of year=-2.86/3=-0.95 2006 1.24 0.53 0.28 variance of yahoo VAR(X)= square of Sum total of deviation of X around X(bar)/no of year 2007 0.42 -0.29 0.09 Var (X)= 7.84/3=2.61 Total 2.15 0.42 Standard deviation of yahoo = square root of VAR(X)=SQRT of 2.61=1.62 M(bar) 0.72 VAR(M) 0.14 SD (M) 0.37 The same formula will be applicable for google and s&p 500 Now calculation of Beta of Yahoo Beta (X) Beta (X) = covariance of (X,M)/VAR(M) Calculation of corelation between yahoo & market X-X(bar) M-M(bar) {X-X(bar)}*{M-M(bar)} 0.80 -0.23 -0.19 -2.25 0.53 -1.18 1.46 -0.29 -0.43 Total -1.80 Cov (X,M)= total of {X-X(bar)}*{M-M(bar)}/ no. of year = -1.80/3= -0.60 Beta (X)= -0.60/0.14 -4.32 Now calculation of Beta of google Beta (Y) Beta (Y) = covariance of (Y,M)/VAR(M) Calculation of corelation between yahoo & market Y-Y(bar) M-M(bar) {Y-Y(bar)}*{M-M(bar)} -0.29 -0.23 0.07 -2.70 0.53 -1.42 2.98 -0.29 -0.87 Total -2.22 Cov (Y,M)= total of {Y-Y(bar)}*{M-M(bar)}/ no. of year = -2.22/3= -0.74 Beta (Y)= -0.74/0.14 -5.35 Beta (YAHOO)= -4.32 Beta (GOOGLE)= -5.35 Answer b Beta signify the maovement of the stock with 1% movement in the market here the beta is in negative so if the market move 1% up then the stock of yahoo moves down by 4.32 and stock of google move down by 5.35 so , yahoo stock will be less risker than google Answer c :- Beta slope function calculation using Excel are as follows Slope function ofyahoo beta -4.32 Slope function of Google beta -5.35 The beta can be calculated in two ways first is covariance - variance method and second is slope function method. In both case we conclude that yahoo is less risker than google as it fell less than google i.e 4.32 in comparison to 5.35
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.