Financial Modeling is the basic tool for company valuations. And for building a financial model, one parameter you would need is stock beta for calculating cost of capital. For those of you interested in knowing how to calculate beta practically, here is a step by step approach.

Beta = Covariance (stock returns with market returns) / Variance (Market returns)

Remember, that the beta calculation involves returns of market and stock. In our calculation we will consider the daily returns and compute the Beta with one year’s price data.

Follow the steps mentioned below to calculate beta for Infosys:

  • Get the price data for Infosys and Nifty for the past one year. (You can take the values from www.nseindia.com). Arrange the values as shown in the figure. We have taken data from 14th May 2010 to 17th May 2011.

  • Calculate the daily returns for both the series, as shown:

  • Find Variance of Nifty returns as Nifty is selected as the benchmark. You can make use of the excel formula of  =stdev(nifty returns time series). This will provide you with the standard deviation value. Square it to get the Variance as shown:

  • Find covariance of Nifty and Infosys Returns. You can use the =covar(stock returns time series, nifty returns time series) formula of excel. The application of the formula is as shown:

  • Beta = Covar (stock returns, market returns) / Variance (Market returns). Calculate beta as shown:

In this way you will be able to find beta for any company in the listed domain.

Points to be noted:

  1. When taking the time series data for the Nifty and the Stock , map the dates correctly (i.e. the stock price and Nifty value should be for the same day) to get accurate results.
  2. The value from the websites may differ slightly from what we calculate. This may happen as they may take dates or periods different from what we have considered. However the values will not vary much and most of the times the values match as it’s a general practice to calculate Beta based on past 52 week data ( 1 year).

Downloadable Resources

Attached herewith is the Beta_Calc.xlsx which demonstrates the 5 step procedure explained above to calculate beta. The case taken here is of Infosys and the Beta value works out to 0.46.

About the Author:

Sameer, has an extensive experience in the financial services, consulting and training domain. He has extensive knowledge of the financial services industry, where he has analyzed companies in the Business Services, Metals and Mining and Retail Companies, for the North American Markets and Canadian Geographies. In the Indian markets he has worked in Project Finance in the Infrastructure and Power Sectors.

He has authored research papers and trained 1000′s of senior people in the area of financial modeling, quantitative analysis and risk management. He holds a MBA degree from NITIE and is CFA Level III candidate.