Calculating Historical Volatility

The above spreadsheet will download historical stock prices from the web and calculate the historical standard deviation for the range of values that you specify.

You can download stock data as far back as Yahoo!'s database allows and choose your own historical lookback period. And it's free.

Volatility is the most crucial of all option trading concepts. Volatility indicators provide traders with an estimate of how much movement a stock can be expected to make over a given time frame. This is crucial in determining whether an option is likely to expire in or out of the money by the expiration date.

Understanding volatility also helps traders understand whether an option is cheap/expensive relative to the historical facts of the underlying instrument.

There are two types of volatility that we will be looking at: Implied Volatility and Historical Volatility.

Volatility Definition

Historical Volatility is a statistical calculation that tells option traders how rapid price movements have been over a given time frame. The most common method of calculating historical volatility is called the Standard Deviation.

Standard Deviation measures the dispersion of a set of data points from its average. The more disperse (spread out) the data is, the higher the deviation. This deviation is referred by traders as volatility.

Don't get too caught up in trying to understand the how's and whys of the standard deviation, just accept that all traders use this method for determining historical volatility. However, if you want more of an explanation you can refer to Appendix C of Option Volatility & Pricing for a calculated example of standard deviation.

Assets that have large and frequent price movements are said to be volatile or said to be of high volatility. Consequently, assets whose price movements are slow and predictable are said be low volatile instruments. Take a look at the following examples of high and low volatile assets.

Take a look at the examples below of a highly volatile stock and a low volatile stock;

High Volatility

Low Volatility

Why is volatility so important to option traders? Because volatility is a measure of the possible price changes of the asset in the future. Assets that have high volatility can be expected to have large price changes in the future. As a result, options that are based on assets with high volatility can be expected to have higher prices.

The higher the volatility, the more likely it is that the underlying asset will trade higher (or lower) than the exercise price by the expiry date.

Implied Volatility

Implied volatility is the markets view of where volatility will be in the future. To determine an option's implied volatility, the trader must use a pricing model.

But for now, take a look at the following illustration;

Historical Volatility tells us how volatile as asset has been in the past. Implied Volatility is the markets view on how volatile as asset will be in the future.

We can tell how high/low implied volatility is by comparing the market price of an option to the options theoretical fair value. This is why we need to use an option pricing model - to determine the fair value of an option and hence know if the market price for the option is over/under valued.

When the market price of an option is higher than it's theoretical value (based off past information) it is considered expensive and so to if the market price of the option is less than the theoretical price, it is considered cheap.

The Volcone Analyzer

Another way to look at implied volatility is to compare the current level of implied volatility to the average level of implied volatility for the same option.

It's a sound approach, however, building your own database of implied volatility data for every US stock requires a huge investment of time and resources. If you're interested in this idea though, then I suggest you take a look at the Volcone Analyzer Pro by Options University. It instantly tells you if an option is cheap or expensive relative to the historical volatility levels.

Comments (33)

As soon as Yahoo add support for this FOREX historical data will just work like stocks do now with the spreadsheet.

Luis

July 29th, 2010 at 11:39am

Great job. How about a similar sheet for FX? Should be a simple modification to the macro, but it is protected.

Thanks

SAIFEE

June 25th, 2010 at 3:38am

VERY GOOD JOB DONE

Peter

February 2nd, 2010 at 4:47am

Hi Mike, the risk free rate is the current level of interest rates and dividend schedules/yields can be obtained from your broker.

Mike

February 1st, 2010 at 1:55pm

That would be on the "Basic" tab

Mike

February 1st, 2010 at 1:54pm

I am fairly new and was wondering where would I get the figures to enter into cell #'s C8 & C9?

Peter

January 14th, 2010 at 11:29pm

Mmm, works fine for me. What symbol are you trying? Can you retrieve that data directly from the Yahoo! site?

Kyle

January 12th, 2010 at 6:50am

"Error! Symbol not found, or Yahoo! has changed the data format".

Have they changed the code?

Peter

January 11th, 2010 at 6:16am

Yep, the lookback period is configurable, so you can enter 100 if you like...I chose 50 as a default arbitary value.

Milos

January 11th, 2010 at 3:58am

Shouldn't the rolling window be bigger than 50? When the sample is small you can get evolution of volatility just by chance - Say you had one day very extraordinary large observation, this was in day 1, and on day 51 you have relatively small observation. Once you delete day one and add day 51 you have a change and volatility become time varying. I have seen that in other places 100 day rolling window is used, that is why I am asking

Artun

January 5th, 2010 at 11:32pm

There are couple of obvious fixes required to fit into actual trading volatility calculation, I think the most obvious one is there are not 365 trading days, so you need to use 252 trading days for the sqrt part.

Dave

November 7th, 2009 at 12:30pm

Peter - You're welcome. I thought that might be the case. The Offset() method of defining a range is an incredibly powerful tool, one which seems to be little known or used, even among savvy Excel users. I was one of them ... a whole new world was opened to me when I "discovered" it. Keep up the good work! Dave

Peter

November 5th, 2009 at 2:41pm

Hi Dave,

Volatility Days certainly is used in the calculation...it's written into the Macro...not in the formula. When you hit the "Extract Data" button the array for the volatility calculation changes according to what you've entered into the "Volatility Days" cell.

Having said that...I do appreciate your formula below. The reason I included it in the Macro was because I didn't know how to do it with a formula ;-) But now I know, thanks a lot...very useful!

Dave

November 5th, 2009 at 11:05am

Wont' accept less than symbol. =IF(ROW() is less than (Days+11),"",STDEV(OFFSET(C100,0,0,-Days,1))*SQRT($B$2)). Copy it to all the cells in column D.

Dave

November 5th, 2009 at 11:00am

=IF(ROW()<Days+11,"",STDEV(OFFSET(C100,0,0,-Days,1))*SQRT($B$2)) and copy it to all the cells in column D.

Dave

November 5th, 2009 at 10:57am

Clearly the "Volatiliy Days" variable isn't used in the volatility calculations, but it can be. Assign the name "Days" to cell B3, then relace the formulas in cell D100 with =IF(ROW()<Days+11,"",STDEV(OFFSET(C100,0,0,-Days,1))*SQRT($B$2)) and copy it to all the rest of the relevant cells in column D. Now you can vary the number of volatility days and the volatility will change accordingly.

Alan Chua

October 19th, 2009 at 4:03pm

Thanks so much Peter for a job well done. Your spreadsheet has helped me track volatility with ease.

Peter

October 13th, 2009 at 7:05am

Thanks very much for the positive feedback engineer!

engineer

October 12th, 2009 at 3:25am

This website describes the formula in depth and with details links.http://www.neuralmarkettrends.com/2007/05/29/calculating-historical-volatility/

engineer

October 12th, 2009 at 3:20am

I am an engineer and was studying pricing movement.I used and applied the online formula found on googling historical volatility formula.

I was trying to compare my result with yours.

thanks for the great work. I like your chart.

I tried other place like Ivolatility.combut it is a joke nowaday that they force people to sign up just to get a stock quote. and they try to charge you money for data download for historical volatility where people can use yours spreadsheet or programmers like us that can generate it from google or yahoo finiance api using very simple and basic programming calculation.

samantha

October 9th, 2009 at 2:25pm

thanks very much for sharing this.Great spreadsheetHappy Trading!

Kenneth K. Seibel

September 25th, 2009 at 12:09pm

Thanks a bunch for sharing your spreadsheet - you saved me many hours of work and your version is better taht the one I had intended to create!

Peter

September 14th, 2009 at 6:25am

Thanks Hazem! You could check out www.ivolatility.com.

Hazem

September 13th, 2009 at 11:36am

Great stuff... thanks for doing this and for sharing...I have a macbook and vb macros don't work on mac office. Is there any other alternative? An online version or something?Thanks...

Aydin

August 27th, 2009 at 4:14am

Thanx!

Peter

July 10th, 2009 at 7:32am

Hi Aydin, try this:

http://www.willowsolutions.com/tips/tips_2002_08.shtml

It does the same thing.

Aydin

July 2nd, 2009 at 5:39am

I appreciate your effort for the excel. Nice job... Can I please get the VBA password :)))

Peter

June 6th, 2009 at 5:49am

Hi Michael,

That's a good question and to be honest I've never thought about it before ;-). All the stats books I've looked at always used STDEV only...perhaps because the data used is only a sample dataset. I believe STDEVP is used when the population data is 100% complete.

What are your thoughts? Do you think STDEVP is a better alternative?

Michael

June 5th, 2009 at 9:34pm

Fantastic product! I really appreciate this...I just have one question: Why do you use sample standard deviation when computing volatility as opposed to standard deviation of the population (excel function: stdevp)

Peter

March 31st, 2009 at 8:07pm

Hi Ben, I plan to release the source code in the future, however, for the time being it is locked. You can check out a similar example from: