Beta is a financial term widely used in fundamental analysis. It is used to gauge or measure the volatility of a particular stock as compared to the market index (Ex. S&P 500 or Nifty 50).
In simple terms, Beta refers to the sensitivity of a stock price with respect to an index or benchmark. It is regarded as a very efficient and effective measurement of the risk of security.
Formula to calculate Stock Beta
Beta in mathematical terms is referred to as the relationship between Co-Variance of the particular market index or benchmark and the Variance of an asset (can be anything, stock, mutual fund, or whole portfolio).
Note: Strategy Relying solely upon Beta value ( β ) may not result in accurate prediction as Beta is calculated on the basis of the historical performance of the stock/asset and does not take into consideration the future prospects. The value of the Beta is dependent on the time period chosen for calculation.
Mathematics Formula:
Beta is the covariance of the return of an asset with the return of the benchmark, divided by the variance of the return of the benchmark over a certain period. The formula of Beta by this definition stands:
where:
βp: Beta
rp: the return of the portfolio, asset, or stock
rb: the return of the benchmark or market index
Understanding Beta
Now, as we know what is Beta and how to calculate it, we still need to understand the numerical value which would be equated from the above formula for the value of βp, what does it mean, and what does it signify?
So, as Beta is a ratio, it cannot be negative and can take values only starting from 0.
- One (1): indicates a stock has the same volatility as the market
- More than one (>1): indicates a stock that’s more volatile than its benchmark
- Less than one (<1): is less volatile than its benchmark
- 1.5: is 50% more volatile than its benchmark
- Zero (0): indicates no correlation with the chosen benchmark (e.g. cash or other non relatable assets)
If the benchmark or index returns 10%, then a stock with a beta value of 1.5 should return 1.5 times 10% = 150% or more.
Steps to calculate Beta in Excel
Let’s get started with the calculation of beta in Excel by implementing the above mathematical formula.
Step 1. Fetch the Historical Data
To calculate Beta, our first step is ought to be getting the historical data of a stock of a company.
- Go to Yahoo Finance, search for the company or ticker symbol which you want to analyze.
- Now, go to Historical Data, select the time period for which you need to analyze and then confirm it by clicking Apply.
- Download the .csv file of Historical Data
Step 2. Enter data in your Excel Spreadsheet
- Arrange the historical data downloaded with closing prices of the stock to watch and the index/benchmark to compare with.
In our example below, we have used Tata Consultancy Services (TCS) as the stock to analyze and the stock market index used is Nifty 50 to compare, from a historical period from 1st Jan 2019.
Step 3. Calculate Covariance
To calculate the covariance of the stock with its index in Excel, we use the =COVARIANCE.S(val1, val2) formula of Excel, which calculates the Covariance of a sample.
- To apply it, we do so by typing the formula name or selecting it to insert
- As a next step, we insert values into our formula, which is range of values (price) of our stock to analyze for val1 and for val2 – the index/benchmark we’ll be using to compare, insert by selecting the whole column or dragging to all the values.
- Make sure you enter correct values in right order which is – of the stock and then of the index.
- Insert the values by selecting all the value in the column, as shown
Step 4. Calculate Variance
To calculate the variance of the index/benchmark in Excel as per our formula to calculate Beta, we use the =VARI.S(val) formula of Excel, which calculates the variance of a sample.
- To apply it, we do so by typing the formula name or selecting it to insert
- As a next step, we insert values into our formula, which is range of all values (price) of the index/benchmark we’ll be using to compare.
- Insert the values by selecting all the value in the column, as shown
Step 5. Calculating Beta
The process is simple now, we just need to divide the value of Coriance by Variance as per the formula of Beta –
The value we get of Beta might be of varied decimal places, to solve that we approximate the value of our Beta value by using the decrease decimal tool, as shown:
The approximate value helps in better visualization and analysis.
As a final result, we get the value of Beta to be 0.261 which is <1 hence, we interpret that for the specified period the stock price of TCS was less volatile than that of the Nifty 50 index.
Example 2: For clarity
Now, we have calculated beta value on a small dataset (only 30 entries) if, you have noticed in our previous example. But now, to clear your concepts well and help you understand better, we take another example of how to calculate beta.
Here, we calculate the beta value of BAJAJFINSV (BAJAJ FINSERV LTD.) with respect to the BANK NIFTY for a period of 1 year (i.e. 2021), as you may notice for 249 entries. All prices are as per NSE (National Exchange of India). This example also demonstrates that we can take any index or benchmark to compare our stock performance with it:
Here, in this example, we get the Beta value equal to 1.228 which is >1, and demonstrates that the stock of Bajaj Finance was more volatile than that compared to Bank Nifty for the same period (1 Year).
Conclusion
That’s it for the tutorial. We hope that you have learned well about Beta Value, what it is, and how to calculate Beta in Excel. And you are ready to implement it yourself and estimate the performance of your desired stock, mutual fund, or portfolio.