Stock Beta Calculation in a Spreadsheet: Step by Step Tutorial

Beta is an important parameter that investors must take into consideration before buying any stock, mutual fund, or any similar financial instrument. It measures the volatility of an instrument against a benchmark and therefore helps assess the risk factor of your investment. In this post, we’ll learn a step-by-step process for stock beta calculation in a spreadsheet. The read-only version of the spreadsheet is available for you to access free of cost from the end of this post.

Stock Beta Overview

Investing in stocks has a systematic risk associated with it as the markets do not move linearly. Since the stock market is volatile, it is important to know how much risk you are getting into before investing your hard-earned money. A metric known as “Beta” helps you measure this risk.

Beta is expressed as a numeric value, and if the value is greater than 1 it indicates the particular stock is more volatile and hence more “risky” as compared to the overall market. If you are looking for a long-term investment, it’s ideal to pick up stocks whose Beta is less than or equal to 1. However, if you are into stock options selling, it’s better to pick up stocks with high beta as premium decay may be faster for volatile stocks.

Stock Beta Calculation Formula

Beta is expressed as the covariance of the return of a stock divided by the variance of the return of the benchmark over a certain time period:

Beta  = Covariance (Stock Return, Benchmark Return) / Variance (Benchmark Return)

Steps to calculate Stock Beta in a Spreadsheet

For this tutorial, we’ll use Google spreadsheet for stock beta calculation. Google spreadsheet allows you to fetch historical stock prices using the Google Finance function, and hence there is no manual work involved to operate this spreadsheet.

Let’s first look at the end state of the spreadsheet and then we can backtrack on how it was built:

Cell B2 contains the symbol of the stock for which the Beta value is calculated. In this case, it is NSE:TCS

And cell A2 contains the benchmark symbol of NSE Nifty against which the Beta is calculated.

You may change the values in this cell to calculate the beta value for the stock you want

From row 6 onwards, we have fetched the close price for the stock and benchmark symbols using the Google finance function.

If you highlight cells A7 or E7, you’ll see the formula to fetch the data.

=GOOGLEFINANCE(B2, “close”, TODAY()-1000,TODAY())

The above formula takes “NSE:TCS” as input from cell B2, the value “close” instructs the function to only fetch the close price, and the next two parameters ensure that the values are fetched for the past 1000 calendar days. Please note that you should fetch close price for atleast a year to get an accurate value of Beta.

And the data should be fetched for the same historical period both for the stock symbol and the benchmark symbol.

Next is the day-wise return calculation at columns C and G of the spreadsheet. Below formula is used for the same:

=(B9–B8)/B8

Finally, the Beta is calculated at cell E2 using the below formula:

=covariance.p(C:C,G:G)/VARP(G:G)

The above formula calculates the covariance of stock returns against benchmark returns and then divides it by the variance of benchmark returns to calculate the Beta.

Access to Spreadsheet

We hope you got a fair idea of stock beta calculation in a spreadsheet. You may access the spreadsheet using the below link:

https://docs.google.com/spreadsheets/d/1-HJGfsml7NnE2TBHUAXnj4c8IEYeWi24uP6bZ_udNkI/edit?usp=sharing

Please feel free to copy the spreadsheet and use it for your analysis.