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 = (104101) = 3 X 100 = 2.97%.
101 101
Similarly Second Return = (107104) = 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 (n1) 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 := 1735017300 = 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 covariance 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? X^{2} – (? X)^{ 2}
n
where n is number of observations
3) In case Excel is used we can find the covariance 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.