Question

You are estimating the beta of the stock of Calico,Inc. using historical return information. The following information is available:

Historical return %:

Year Calico,Inc. Viprot, inc. LCK Index ( Market proxy)

2009 28 24 18

2008 18 -20 15

2007 15 18 22

2006 13 15 19

2005 -6 -8 -12

2004 24 15 36

2003 -20 -18 -28

2002 -8 -6 -13

Using the historical return data and simple linear regression ( may use calculator, showing calculator entries or Excel or any Statistical Package, showing work):

a. Estimate the betas for Calico,Inc. Viprot,Inc. & LCK Index.

b. Using the formula in the book: Adjusted beta = 0.67(historical beta) + 0.35(1.0), find the adjusted beta of Calico,Inc and Viprot,Inc

Answer 1

Let's use Excel to calculate Beta of the two stocks

For Calico Inc.

Here above, Input Y Range is for returns of the stock. Input X Range is for the returns of market.

After pressing OK, you will get a summary as snipped below.

The Yellow marked cell is the Beta

Beta of Calico Inc. is 0.737975

Adjusted beta = 0.67(historical beta) + 0.35(1.0) = 0.67 (0.737975) + 0.35 = 0.84444

----------------------------------------------------------------------------------------------------------------------------------------------------

Similarly, we will follow the steps to compute beta of Viprot, Inc.

The Yellow marked cell is the Beta

Beta of Viprot, Inc. is 0.558583

Adjusted beta = 0.67(historical beta) + 0.35(1.0) = 0.67 (0.558583) + 0.35 = 0.72425061

-------------------------------------------------------------------------------------------------------------------------------------------------------

We know that Beta of any stock or portfolio is essentially a relation between it's returns against market returns.

Thus, Market index's Beta will always be 1, and so will be the adjusted beta.

For your practice, you may try to make a similar table in excel with both the columns having historical return of LCK Index (Market proxy). The data regression data analysis will give the coefficient of X Variable as 1.

Further, it is evident from the formula of adjusted beta that it will also be 1 for the LCK Index.