[dc]I[/dc] received a question from a reader who asked, “Can you calculate volatility in Excel?” The answer is, yes you can, but there are a few things you need to know. Without going into too much detail here, there are many ways to calculate volatility. Two of the most common measures are implied and historical (also called realized or statistical) volatility. It is fairly simple to calculate historical volatility in excel, and I will show you how in this post. Calculating implied is quite a bit more complicated. You technically can do it in excel, but you have to impute it from an option price. In addition, there’s actually a volatility surface, or different values of implieds for different strike prices and maturities. That’s a topic for another day; today let’s just look at how to calculate a simple historical volatility in Excel.
1. Collect your raw data, in the form of a closing price for each time period. Many people do not know, but Yahoo Finance is a good source of daily data that can be downloaded into a spreadsheet. (See this example for SPY.) Your data will likely include other data points such as high, low, volume, etc, but just ignore everything except the close.
2. The first step is to convert the prices into a return series. Again, let’s not dig too deeply into the theory in this post, but prices are somewhat arbitrary. Is a $50 price a change a lot? Well, that depends on the price of the asset and how much prices usually change. Converting to returns is nothing more than changing the price series into a series of percentage changes. This is the first step in nearly all quantitative or mathematical market analysis. In Excel, start at the second price from the top in your series (assuming closing prices are in a column with the newest price at the bottom). In the cell to the right of prices, divide the second price by the first and subtract one, as in the pic. Copy this formula down the entire column.
3. Next, find the standard deviation of the returns. The formula for standard deviation in Excel is =STDEV(…), and takes a range of prices as an input. In the graphic, I have calculated a 10 day standard deviation of prices, but that is for the illustration only. Set your window to whatever time period you want to evaluate, and, again, copy the formula down. Twenty days is a good starting point if you haven’t done this analysis before.
4. So far, the procedure has been straightforward: calculate a return series, and then calculate the standard deviation of that series. There is one more step, which is perhaps the only part of this that is conceptually a little bit complicated. You have calculated the standard deviation of the returns for whatever the time interval of your data is. If you have daily data, you have calculated a daily standard deviation, and so on for hourly, weekly or any period. Historical volatility is the annualized standard deviation of returns. We must multiple the standard deviation by an annualization factor, which is the square root of how ever many of your periods are in a year. This example is daily data; there are 262 trading days in a year, so we multiply the standard deviation by SQRT(262). If you are using weekly data, the annualization factor is SQRT(52), etc.
This is one example, but a slightly more complex example, with graphs, can be found step by step on the tabs in this spreadsheet. We will consider exactly what this measure of volatility is, what it does, what we can do with it, and, even more importantly, what’s wrong with it in a future post.
Edit: This sheet and article use an annualization factor of 262. There is some debate over what number to use here, but you’ll see it doesn’t really make much of a difference. 252 is probably the most accurate number (and is what I use in my calculations now), but, as long as you’re consistent in your calculations, the actual number doesn’t really matter.
Excellent information. Is there an excel spreadsheet calculation that can be used to determine a portfolio’s suseptability to tail risk?
Managing and assessing tail risk is one of the most challenging tasks that managers and traders face. There is no calculation or test that can do this. There is product (even though some claim to) that can effectively manage tail risk. Some portion of it is unhedgeable, but there is a lot that can be done to mitigate the potential damage and to prepare a portfolio for unforeseeable events.
This is a major focus of our consulting work at Waverly Advisors. Please contact me if you’re interested in discussing further. (AdamHGrimes at gmail dot com)
what if i have data for more than 3-4 yrs..still il multiply std deviation with sqrt of 262 or it will be sqrt of total no of trading days
“We must multiple the standard deviation by an annualization factor, which is the square root of how ever many of your periods are in a year.” The length of your data window doesn’t change that.
or you can grab an ATR indicator and set the period to 252(trading days)….
ATR has nothing to do with historical volatility. Completely different calculation and application.
For the return that you have calculated in C3, the answer was 0.3%. However B3/B2-1, 1029.25/1032.00 -1 the answer is -0.3%. Why is it that the answer that you have calculated has become positive?
Kelvin,
The value in cell C3 is negative. Are you familiar with the convention of putting negative numbers in parenthesis? I.e. (1) = -1?
Hello.. Thank you very much for this information. I would like to know how you would do for implied volatility on excel as well. Thank you..
That requires a very different procedure as you have to work backwards from options prices to figure out what volatility is needed to “justify” the current options prices.
trading days in the year is 252?
The annualization factor you use is not that important (as long as you’re consistent.)
shouldn’t your annualization factor be proportional to the number of time frames included ie 20 day STDev therefore sqrt ( 262/20 )??
No… this is incorrect as you have calculated the standard deviation for daily returns, the sample of days taken is 20… not to confuse with the duration of returns (which is one day here)
In the Excel Spreadsheet example included it shows 20 & 90 day HVol. I think this is actually 60 day HVol. If you look at the number of cells used in the calculations it is 60 not 90. I was trying to recreate and checking my numbers against those listed and they didn’t match until I figured this out.
Thank you James. You might be right. I might’ve mixed trading and calendar days in that other sheet or something like that. Thanks for the heads up.
What’s the difference between a 20 days volatility and a 90 days volatitliy?
In this case, the lookback period over which they are calculated. Both numbers are (nearly always) annualized, but the difference is in the time window used for the calculation.
Thank you so much for this information..
could you please tell me how can i calculate realized skewness
Simple analysis of returns will let you do this. Excel can do it.
Very useful refresher. Thanks. helped me with looking into a question of an investment manager.
You’re welcome
Adam, I have tried to program your “sigma-indicator” (sigma-calculation) in my chart program (TC2k). Unfortunately I don`t get your results, so I still have a bug and can`t figure it out. Let me paraphrase the sigma concept: 1. Calculate 20d standarddeviation of daily returns (on the close-price) and then 2. set this in relation to current price: (current price-change in % / std (20d price changes) ). Thanks so much for taking time to answer. Very grateful.
Oh, happy now. I have figured it out. 🙂
I have to exclude the current day… Now I have nearly your numbers. Thanks a lot!
Sorry for the late reply, but yes that’s it: offset the vol measure so you are comparing TODAY’s return to YESTERDAY’s vol measure.
Hi Adam, just some short questions:
1. In the Return calculation: Why do you subtract by -1 ? I mean calculating the quotient is ok, but why are you subtracting by -1 ?
2. One is calculating the standard dev. -> is this only neccessary because volatility is mathematically standard dev. ?
3. The squreroot correction is only neccessary because of the annualized standard dev. correct ?
Thanks,
Markus
Pingback: T?keáttételes ETF-ek: csak profiknak | lustaport
Dear sir
Thanks for your idea but what the use of having this idea . I doing trading self ,i come to know volatility is important factor for srike price movement. I want to know how to calculate option strke price premium low and high
That’s a totally different question. Volatility is an input to the pricing formula (which is trivial, once you have volatility). Be aware that there’s a tremendous difference between calculating theoretical pricing for a strike and trading.
Hello Adam,
Thanks for posting this. Can you please help me to lead towards the calculation for implied volatility for stock/each option series?
I would really appreciate it.
Thanks!
That’s a very different topic, but I may cover that in the near future.
Hi Adam,
I am trying to create a monthly volatility index of the main stock exchange of India (BSE Sensex). Any ideas on how I can go about doing that?
There are a lot of issues in doing that and a lot of decisions that need to be made, but the math is pretty well known. I think I’d start by doing some broad research on volatility indexes.
Why did you use arithmetic return and not logarithmic return?
Practical commentary . I was fascinated by the details – Does anyone know if my business would be able to get a template Census BC-600 example to work with ?
Hi Debroah, my assistant got ahold of a sample Census BC-600 example with this link
https://goo.gl/D5AOCa.Thanks for the post.
I am doing stdev for the whole year (I assume there is no need of annualization factor then). I do that over 7 years but then how can I obtain a historical volatility for the whole period? Can I in somehow add it?
Pingback: The Ultimate Guide to Volatility Stop-Losses
Dear Adam
This article was very useful.
May I ask whether we can calculate 5 year volatiliy if we use 5 year daily data and use SQRT(252*5)?
Thanks and kind regards,
Emre
Hi Adam, is there any difference in the annualization procedure when using log returns?
Pingback: Så här räknar man ut volatilitet i excel | SAMUELSSONS RAPPORT
Pingback: Så här räknar man ut volatilitet i excel | Aktiefeed.se
Pingback: Living Your Ideals and Other Great Ideas to Start the Week | Betting Blogs
Pingback: Dash of Insight| Weighing the Week Ahead: Are Stocks Ready for Stronger Economic News?
Pingback: Big Week for Data - TradingGods.net
Adam, can you also show how to calculate your p-value to test a method for an edge in excel also? You mentioned in your book and many other places but I didn’t find anywhere where you showed the calculations. That would be very useful. Thx!
I second this request, also interested in the excel work behind the book example