How to Calculate Stock Beta in Excel

Beta (β) measures the volatility of a stock in relation to a market such as S&P 500 or any other index. It is an important measure to gauge the risk of a security.

The market itself is considered to have a Beta of 1. Using regression analysis, the beta of the stock is calculated. If the beta of the stock is greater than 1, this means the stock’s prices are more volatile than the market, and vice verse. For example, if a stock has a beta of 1.2, this means that a 1% change in the market index will bring about a 1.2% change in the stock’s price. Stocks with high beta are considered to be more risky compared to the ones with low beta.

Formula:

$\beta = Covar \left ( r_{s}-r_{m} \right )/Var\left ( r_{m} \right )$

Where,

r_s represents the returns of the stock, and rm represents the returns of the market or benchmark.

Calculating Beta in Excel

Beta of a stock can be calculated in excel using two methods.

1. Using the formula , i.e. $\beta = Covar \left ( r_{s}-r_{m} \right )/Var\left ( r_{m} \right )$
2. Using the slope function in Excel

Steps

Step 1

Download the historical prices for the stock and the benchmark index. For this example, let’s take IBM stock and S&P500 benchmark index. You can do this using the download stock data spreadsheet.

Step 2

The next step is to calculate the percentage returns for both the stock and the index. To do so, you can use the formula =((Cell2-Cell1)/Cell1)*100, where cell 1 refers to the previous period data point, and cell 2 refers to a current period data point.

Step 3

Calculate Stock’s Beta using one of the two methods.

Method 1 – Calculate Beta using the formula

$\beta = Covar \left ( r_{s}-r_{m} \right )/Var\left ( r_{m} \right )$

Method 2 – Calculate Beta using excel’s slope function

Beta = SLOPE(range of % change of equity, range of % change of index).