Calculate Stock Beta in Excel: A Step-by-Step Guide

Search Fintrev

Search our guides, reviews and glossary.

To calculate stock beta in Excel, download historical adjusted closing prices for the stock and a benchmark index such as the S&P 500, convert both price series into periodic returns, and enter =SLOPE(stock returns, index returns). The result is the stock’s beta: the average percentage move in the stock for each 1% move in the index over the period you chose. You get the same number by dividing the covariance of the two return series by the variance of the index returns, or by running a linear regression with the stock’s returns as the dependent variable.

What beta measures

Beta compares a stock’s movements with those of a benchmark. A beta above 1 means the stock has tended to amplify market moves: with a beta of 1.5, the stock has risen about 1.5% on average when the index rose 1%, and fallen about 1.5% when the index fell 1%. A beta below 1 means the stock has tended to dampen market moves. A beta near zero means the stock’s returns have had little linear relationship with the index, and a negative beta means the stock has tended to move against it.

Beta is backward-looking. It describes how a stock behaved relative to an index in the sample you measured, not how it will behave next month.

Calculating beta in Excel step by step

Step 1: Download price data for the stock and the index

Download historical prices (usually as a CSV file) and use the adjusted close column, because it accounts for stock splits and dividends. Raw closes would show a split as a sudden price collapse and distort the returns. For the benchmark, a broad index such as the S&P 500 is the usual choice for U.S. stocks.

Download both series for the same dates and the same frequency (daily, weekly or monthly).

Step 2: Line up the dates

Put the data on one sheet:

Each row must refer to the same date for both securities. If one series has a date the other lacks (common when the stock trades on a foreign exchange with different holidays), delete that row so every return pairs up correctly. Sort the data from oldest to newest.

Step 3: Convert prices to returns

Beta is calculated from returns, not prices. If your first price is in row 2, enter this in D3:

=B3/B2-1

and this in E3:

=C3/C2-1

Fill both formulas down to the last row. The first row of data has no return because there is no prior price.

Step 4: Calculate beta

In any empty cell, enter:

=SLOPE(D3:D757,E3:E757)

Adjust the ranges to your data. SLOPE takes the dependent variable (known_y’s) first, so the stock returns come first and the index returns second. Reversing them gives a meaningless number.

Three Excel formulas that return beta

Besides SLOPE, two formulas return the same beta on the same data.

Covariance divided by variance. Beta equals the covariance between stock and index returns divided by the variance of the index returns:

=COVARIANCE.S(D3:D757,E3:E757)/VAR.S(E3:E757)

Use matching functions: COVARIANCE.S with VAR.S (sample versions), or COVARIANCE.P with VAR.P (population versions). The older COVAR computes population covariance but the older VAR computes sample variance, so pairing them divides by different denominators (n and n − 1) and gives a slightly low beta. The gap is small with hundreds of observations but grows with short samples.

Correlation times relative volatility. Beta also equals the correlation between the two series multiplied by the ratio of their standard deviations:

=CORREL(D3:D757,E3:E757)*STDEV.S(D3:D757)/STDEV.S(E3:E757)

Running a full regression for more detail

SLOPE gives beta alone. A regression tells you how reliable that beta is.

Data Analysis ToolPak. Enable it under File > Options > Add-ins, then go to Data > Data Analysis > Regression. Set the Input Y Range to the stock returns and the Input X Range to the index returns. In the output:

• The coefficient on the X variable is beta.
• The intercept is alpha, the average return not explained by the index.
• R Square shows the share of the stock’s return variation explained by the index. A low R-squared means beta describes only a small part of the stock’s risk.
• The standard error and t Stat for the X coefficient show how precisely beta was estimated. The t-stat (beta ÷ standard error) tests whether beta differs from zero. To test whether beta differs from 1, calculate (beta − 1) ÷ standard error.

LINEST. =LINEST(D3:D757,E3:E757,TRUE,TRUE) returns the same statistics as an array. =INDEX(LINEST(D3:D757,E3:E757),1) returns only the slope.

Examples

These examples use hypothetical numbers to show the mechanics.

Example 1: Covariance and variance

Suppose a stock’s daily returns have a sample covariance of 0.00018 with the index, and the index’s daily returns have a sample variance of 0.00012. Beta = 0.00018 ÷ 0.00012 = 1.5. Over that sample, the stock moved about 1.5% on average for each 1% index move, in either direction.

Example 2: Correlation and standard deviations

Suppose the correlation between a stock and the index is 0.6, the stock’s daily standard deviation is 2% and the index’s is 1%. Beta = 0.6 × (2% ÷ 1%) = 1.2. The stock is twice as volatile as the index, but because only part of its movement tracks the market, its beta is 1.2 rather than 2.

Example 3: Portfolio beta

A portfolio’s beta is the value-weighted average of its holdings’ betas. A portfolio with 60% in a stock with beta 1.2 and 40% in a stock with beta 0.8 has a beta of (0.6 × 1.2) + (0.4 × 0.8) = 0.72 + 0.32 = 1.04, assuming both betas were measured against the same index.

Choices that change the result

Financial sites often publish different betas for the same stock because they make different choices. When you compare stocks, apply the same method to all of them.

• Period length. Some providers use three years of data, others five. A longer window smooths out temporary episodes but may include years when the company looked very different.
• Return frequency. Daily data gives many observations but can understate beta for thinly traded stocks whose prices react to market news with a delay. Weekly or monthly returns reduce that effect but give fewer data points: five years of monthly returns yields only 60 observations.
• Benchmark. Beta against the S&P 500 will differ from beta against a total market, sector or international index. Choose the index that represents the market the stock’s investors actually hold.
• Excess returns. Strict CAPM regressions subtract each period’s risk-free rate from both the stock and index returns. Over short intervals this usually moves beta only slightly, but the intercept then becomes CAPM alpha.
• Adjusted beta. Some providers pull raw beta toward 1, because betas tend to drift toward the market average over time. A widely used version is adjusted beta = 0.67 × raw beta + 0.33 × 1. In Excel: =0.67*F2+0.33, where F2 holds the raw beta. A raw beta of 1.5 becomes 1.335.

Rolling beta

A single beta assumes the relationship stayed constant over the whole period. A rolling beta shows how it changed. With returns in D and E starting in row 3, enter this in F254 for a window of 252 trading days (about one year):

=SLOPE(D3:D254,E3:E254)

Then fill it down. Because the references are relative, each row calculates beta over the most recent 252 returns. Charting column F against the dates shows whether the stock’s market sensitivity has been rising, falling or swinging around. For monthly data, a 36- or 60-month window is a common choice.

Reading low and high beta

Low-beta stocks have moved less than the market. Holding them can soften a portfolio’s swings in a falling market, though industry-specific risks still apply and do not show up in beta. A low beta can also come from a volatile stock with little correlation to the index.

High-beta stocks have amplified market moves. Short-term traders sometimes favor their larger swings, which bring correspondingly larger losses when the market moves against them. In a bull or bear market, a high-beta stock has historically tended to exaggerate the trend.

Beta captures only the market-related part of risk. It says nothing about company-specific events, valuation or balance-sheet strength, so it belongs alongside fundamental analysis rather than replacing it.

Using beta in valuation

The most common use of a calculated beta is the CAPM cost of equity:

Expected return = risk-free rate + beta × (expected market return − risk-free rate)

As a hypothetical illustration, a 4% risk-free rate, a 5% equity risk premium and a beta of 1.2 give 4% + 1.2 × 5% = 10%. Analysts use that figure as the discount rate for equity cash flows in a DCF model, so beta moves the valuation directly: here, each 0.1 change in beta shifts the cost of equity by 0.5 percentage points.

FAQs

The Bottom Line

Investors comparing stock risk and analysts building DCF models can calculate beta themselves in Excel with SLOPE or a full regression. Period length, return frequency and benchmark all change the result, so apply the same method to every stock you compare. Check R-squared and a rolling beta before relying on the number, and use beta alongside fundamental analysis, because it captures only the market-related part of risk.