How to Calculate Volatility of a Stock or Index in Excel

Calculate an asset's potential for value changes based on historical performance.

By Rachel Warren – Updated Jun 24, 2025 at 12:22 PM EST | Fact-checked by Frank Bass

Key Points

Use Excel to calculate daily returns and standard deviation to gauge stock volatility.
Annualize volatility by multiplying daily standard deviation by the square root of 252.
Remember, standard deviation assumes normal distribution, which may not always apply.

Volatility is troublesome for many investors. Value changes in your stocks, your portfolio, or an index can keep you up at night -- or worse, push you to make emotional decisions you later regret. Understanding an asset's potential for volatility by way of a few calculations can help you keep a level head when stock prices falter.

Loading paragraph...

Stock charts showing volatility. — Image source: Getty Images.

Loading image...

Loading paragraph...

Calculations for index volatility in Excel. — Chart by author.

Loading image...

1. Calculate percentage changes. Using data in Column C, calculate the daily percentage change in the index. Starting with cell D4, the formula is the current day's closing value divided by the previous day's closing value minus 1, or (C4/C3)-1.

Pro tip: If you place your cursor in D4, where you have the formula, you can quickly populate cells D5 through D12. Do this by hovering your cursor over the lower right corner of D4 until you see a plus sign, then click, hold, and drag your cursor to D12.

2. Find the daily standard deviation. Now, you will use the STDEV.S function in Excel. Place your cursor in D13 and type '=STDEV.S(D4:D12)' without the quotation marks. This formula calculates the standard deviation of the dataset.

3. Annualize the standard deviation. Next, you will convert the daily value to an annualized one. To do this, multiply the daily volatility by the square root of 252, which is the number of trading days in a year. In cell D14, type '=SQRT(252)*D13' without the quotes. This tells you the annualized volatility of the index is 10.39% based on the sample data.

If you want to know the asset's weekly volatility, multiply the daily volatility by the square root of 5, or the number of trading days in a week. Using the formula '=SQRT(5)*D13' indicates that the weekly volatility is 1.46%.

Loading paragraph...

Calculating portfolio volatility

You can also calculate the volatility of an entire portfolio, but this formula is far more complex. To keep things simple, we will explain the formula assuming a two-stock portfolio. The data points you need are:

The portfolio weight of Stock 1 and Stock 2. Calculate this by dividing the value of each position by the total portfolio value. Let's label these values W1 and W2.
The standard deviation of Stock 1 and Stock 2. You can calculate these values using the formula explained above. We will label these SD1 and SD2.
The covariance, or relational movement, between Stock 1 and Stock 2. This will be one value for your two-stock portfolio, and we can call it C12. Use the CORREL function in Excel to calculate this value, applied to the two series of daily values for the two stocks.

The portfolio variance formula is: (W1^2)(SD1^2)+(W2^2)(SD2^2)+(2xW1xW2xC12). Note that '^2' means to the power of 2.

The logic underlying the formula is complicated. As you can see, the equation will be increasingly cumbersome as you add more stocks to your portfolio.

Loading paragraph...

Drawbacks of using standard deviation as a volatility measure

Standard deviation measurements assume returns are normally distributed. Normal distribution, known as the bell curve, assumes more results clustered near the center and fewer results that are significantly above or below average.

In reality, stock prices and index values can have asymmetrical distributions. They can stay unusually high or low for long periods. In addition, stock or index volatility can change over time, which challenges the assumption of a constant statistical distribution of returns.

While performing historical volatility calculations helps construct a view of an asset's behavior, investors only have data for what's already occurred. Unfortunately, there is no guarantee that historical patterns will repeat. For that reason, standard deviation analysis should be one aspect of a much broader analysis to predict how a stock might behave.

Loading paragraph...

About the Author

Rachel Warren is a contributing Motley Fool stock market analyst covering pharmaceuticals, biotechnology, medical devices, information technology, and consumer goods. Prior to The Motley Fool, Rachel was a paralegal and legal administrator. She holds a bachelor’s degree in paralegal studies from Liberty University School of Law.

TMFRachelW

Rachel Warren has no position in any of the stocks mentioned. The Motley Fool has no position in any of the stocks mentioned. The Motley Fool has a disclosure policy.