How to Calculate Stock Volatility in Excel?

Volatility is very important for traders. The volatility of any stock affects a lot of things such as the position sizing, risk management, option pricing as well as stoploss placement. In this blog we will see how we can calculate the volatility of any stock using just Excel. We will go through the entire process to understand how to measure risk accurately and how volatility can be used to compare the stocks objectively.

Key Takeaways

1. Volatility is an important metric that tells you how much a stock moves, but it does not tell you the direction. 
2. Usually, daily volatility information is required. You can find daily volatility using =STDEV.S() and then multiply by SQRT(252) to get annual volatility. 
3. A short lookback reacts faster but is noisy, while a long lookback is more stable. 
4. Historical volatility is what the stock actually did, and implied volatility is what the market expects.

What Stock Volatility Means in Trading

Let us first understand what is the meaning of volatility in stock market.  Stock volatility measures how much a stock’s price fluctuates over time. If the stock is highly volatile, then it makes large price moves and it has bigger daily swings. This also means that this talk will carry higher short term risk as well as high short term reward. On the other hand a low volatility stock moves more steadily and has smaller fluctuations.

One important thing to note here. Volatility does not give the sense of direction to the trader. It is very much possible that a stock can be extremely volatile while falling as well as rising. In trading, volatility is commonly expressed as:

• Daily volatility
• Weekly volatility
• Annualized volatility

The most widely used approach is historical volatility, which is based on past price movements.

Before You Start: Data, Time Period, and Return Method

We will need the below data in excel to calculation volatility. 

1. Historical Price Data

The most common data that we will require is daily closing prices. This can be easily downloaded from NSE website or from broker API. Traders must remember to ensure that if there using the daily data then the closing prices should be adjusted for all kinds of Corporate actions such as bonus and stocks splits. 

2. Lookback Period

The next thing that the trader should finalise is the look back period. Some of the most common look back period are:

• 20 trading days
• 30 trading days
• 60 trading days
• 252 trading days (1 trading year)

The choice of the look back period will have different results. If the look back period is short then the result will react faster than it will be more noisy. On the other hand, long look back periods offer higher stability and are slower to adapt.

3. Return Calculation Method

The final step is to do finalize the return calculation. Returns can be calculated using simple returns as well as Log Returns

Step 1: Import Daily Closing Prices into Excel

Start with a spreadsheet containing:

Step 2: Calculate Daily Returns

Volatility is calculated using percentage returns between consecutive days.

Simple Returns

The simple return formula is:

Rt​=Today Close – Yesterday Close / Yesterday Close

Log Returns 

Professional traders often prefer log returns because they are mathematically cleaner for statistical analysis. The Log return formula is:

R_t = ln (Today Close/Yesterday Close)

Simple returns are sometimes used because their easier to understand. However they are slightly less accurate for larger moves. On the other hand log returns provide better statistical Modelling and are additive over type and usually preferred in quantitative finance. However they are slightly harder for beginners

Step 3: Use STDEV to Calculate Volatility and Annualize It

Once daily returns are calculated, volatility is simply the standard deviation of returns. In excel this can be done using 

=STDEV.S(C2:C31)

This gives daily volatility.

Annualizing Volatility

To convert daily volatility into annualized volatility, the formula to be used is 

Annual volatility = daily volatility x sqrt(252)

Where:

252 = approximate trading days in a year

Excel formula:

=STDEV.S(C2:C31)*SQRT(252)

Historical vs Implied Volatility in Excel

Here we would like to clarify one thing. The calculation that shown is for the historical volatility. This is the past price movement and this is what the stock actually did. There is also a concept called implied volatility which is based on option prices and which means what the market is expected to do.

As we saw historical volatility is relatively easier to calculate using past returns, standard deviation and historical prices. Implied volatility is not so easy. To calculate implied voltality be required the option prices the black holes model as well as the market expectations. Calculating IV in Excel is more advanced because it requires:

• Option premium
• Strike price
• Time to expiry
• Risk-free rate
• Iterative solving

Final Thoughts

Learning how to calculate stock volatility in Excel gives traders a much deeper understanding of market risk. This can help traders to understand what volatility means and they can create their historical vol data. A lot of traders can use historical volatility for doing the following:

• Size positions intelligently
• Compare stocks objectively
• Improve risk-adjusted performance
• Understand market behavior