Beta, calculating it, is easy

Beta by definition is a measure which indicates the price movements for a given stock with reference to the overall market movement i.e. index in this case. A higher beta value means that the stock fluctuations will be higher on both up and down side with reference to the overall market (index). It's a very basic statistical measure.

I was trying to evaluate the beta of my own portfolio, and the first basic step is to calculate the beta for a given stock. Though, this information is readily available online, knowing the basics is always better. Therefore, I decided to look at calculating the beta for a given stock.

In the current example, I have used NIFTY (index) and ICICI Bank (stock in question) to calculate beta for the last three months data.

1. Figure out stock (ICICI Bank in this case) in question, and the benchmark index (NIFTY in this case). I have used GOLDMAN BEES NIFTY ETF as the data was readily available
2. Find out the closing prices, sorted in the descending order (new to old) 





3. Calculate the percentage difference e.g. for 13 Mar 2015, calculate the price movement for the stock as (C2-C3)/C2, which is actually the % change in price from the previous day
4. Format the cells to percentage with two decimal places
5. Calculate the price movement for the index as =(B2-B3)/B3
6. Once we have all the data, we can use the following formula in excel to calculate the beta COVAR(E2:E62,D2:D62)/VAR(D2:D62)
7. Beta for ICICIBank=COVAR(E2:E62,D2:D62)/VAR(D2:D62)=1.77 (based on the last three months data)
8. It is quite obvious that larger the size of the data, more correct the number will be. For example, when I used one month data to calculate beta, it came at around 1.5 (one month) instead of 1.77 (three month).

To be continued..

---

vA