14. Without using any Excel function, but rather the formulas in Chapter 12 and

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

Google

S&P500

Ended

Yahoo

Google

S&P500

Ended

Yahoo

Google

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

Google

S&P500

Ended

Yahoo

Google

S&P500

Ended

Yahoo

Google

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

• Browse (All)
• Browse 1
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.