menoneduworld.com

Calculation of Beta

48. Financial Valuation of a Project: Part 11: Calculation of Beta

The systematic risk is always present in all shares and cannot be diversified. It is measured by beta (?). Beta is given by the formula:  ? = Covariance ( Stock Returns, Market returns)

Variance (Market Returns)

 

Beta values are normally available on the website of the stock Exchange. In India it is available on the website of National Stock Exchange (NSE) and Bombay Stock Exchange (BSE). However many analysts prefer calculating the same as the assumptions such as the period of calculations may not match. This week the steps in calculation of Beta for listed companies is sought to be simplified.

Step No 1  in Calculation of Beta for a listed Company (say XYZ)

 

Download using the internet the following information :

 

1)      Movement of share prices of Company XYZ.

2)      Corresponding movement of an Index say SENSEX.

 

 

Date

Price of

Value of BSE

 

Share  XYZ

SENSEX

Monday, July 18, 2011

101

17000

Tuesday, July 19, 2011

104

17300

Wednesday, July 20, 2011

107

17350

Thursday, July 21, 2011

103

17310

Friday, July 22, 2011

97

17140

Monday, July 25, 2011

98

17200

Tuesday, July 26, 2011

91

17340

Wednesday, July 27, 2011

89

17200

Thursday, July 28, 2011

96

17500

Friday, July 29, 2011

93

17450

Monday, August 01, 2011

101

17570

Tuesday, August 02, 2011

104

17790

 

Table 1 : Data of Price Movements of XYZ and SENSEX movements

 

It is to be noted here that we have taken only 12 observations as it is an illustration.

In practice, observations for at least 52 weeks (1 year) is taken.In fact the higher the number of observations and the more recent the data the better it is .

Step No 2: Calculate returns of the stock XYZ

Remember the formula for Returns is Returns = (Current Price – Previous Price)

Current Price

So First Return = (104-101) = 3  X 100 = 2.97%.

101                101

 

Similarly Second Return =        (107-104) = 2.88%

104

 

Similarly other returns can be similarly calculated.

 

Returns of Stock XYZ

Date

Price

Returns (%)

Monday, July 18, 2011

101

 

Tuesday, July 19, 2011

104

2.97%

Wednesday, July 20, 2011

107

2.88%

Thursday, July 21, 2011

103

-3.74%

Friday, July 22, 2011

97

-5.83%

Monday, July 25, 2011

98

1.03%

Tuesday, July 26, 2011

91

-7.14%

Wednesday, July 27, 2011

89

-2.20%

Thursday, July 28, 2011

96

7.87%

Friday, July 29, 2011

93

-3.13%

Monday, August 01, 2011

101

8.60%

Tuesday, August 02, 2011

97

-3.96%

 

Note : Since we have 12 observations, the total number of returns that can be calculated is 11.

In general form if we have ‘n’ observations, we shall get (n-1) figures of returns can be calculated.

 

Step No 3: Calculate returns on the INDEX for the same corresponding periods

 

The first return can be calculated as follows : =17300 - 17000 =  1.76%

17000

The second return is calculated as follows     := 17350-17300 =    0.29%

17300

 

Returns of BSE SENSEX

Date

SENSEX

RETURNS (%)

Monday, July 18, 2011

17000

 

Tuesday, July 19, 2011

17300

1.76%

Wednesday, July 20, 2011

17350

0.29%

Thursday, July 21, 2011

17310

-0.23%

Friday, July 22, 2011

17140

-0.98%

Monday, July 25, 2011

17200

0.35%

Tuesday, July 26, 2011

17340

0.81%

Wednesday, July 27, 2011

17200

-0.81%

Thursday, July 28, 2011

17500

1.74%

Friday, July 29, 2011

17450

-0.29%

Monday, August 01, 2011

17350

-0.57%

Tuesday, August 02, 2011

17790

2.54%

 

Note : Again we will have 11 figures for returns.

Also the dates for the calculations of returns on SENSEX and share XYZ is the same.

Step No 4: Calculate co-variance between the returns on SENSEX and share XYZ and

Variance of Returns on the SENSEX

 

Date

XYZ

BSE SENSEX

Tuesday, July 19, 2011

2.97%

1.76%

Wednesday, July 20, 2011

2.88%

0.29%

Thursday, July 21, 2011

-3.74%

-0.23%

Friday, July 22, 2011

-5.83%

-0.98%

Monday, July 25, 2011

1.03%

0.35%

Tuesday, July 26, 2011

-7.14%

0.81%

Wednesday, July 27, 2011

-2.20%

-0.81%

Thursday, July 28, 2011

7.87%

1.74%

Friday, July 29, 2011

-3.13%

-0.29%

Monday, August 01, 2011

8.60%

-0.57%

Tuesday, August 02, 2011

-3.96%

2.54%

 

Note :

1)      Covariance between 2 variables (X, Y) = n ? X Y  - (? X ) (? Y)

n

2)      Variance of x = n? X2 – (? X) 2

n

where n is number of observations

3)      In case Excel is used we can find the co-variance value by using the inbuilt formula of COVAR.

4)      In case Excel is used we can find the variance of Sensex returns by using  the inbuilt formula of VARP ( i.e. Variance of Population).

5)      It needs to be noted here that the formula of VAR should not be used as it gives the Variance of sample.

 

Thus in the above mentioned case we have :

 

Covariance between 2 Returns

0.00009

Variance of Sensex Returns

0.00012

 

Step No 5 : Calculation of  Beta of the Stock

 

? = Covariance ( Stock Returns, Sensex returns)

Variance (Sensex Returns)

 

In the above case :

Beta = Covariance/Variance

0.715

 

 

Final Conclusions:

1)      The shortcut in Excel is to use the Slope function.

2)      While using the slope function it needs to be remembered that Y (the dependent variable) is the stock returns while X (the independent variable) is the returns on the Sensex.

3)      It has been observed that some students directly use the slope function on the absolute values.

4)      This approach is incorrect. First as demonstrated above we need to first calculate returns.

5) In conclusion, the slope function in Excel should be used on the values of the returns to calculate the beta of the share.