How Do You Calculate Volatility In Excel?

[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.

AdamHGrimes

Adam Grimes has over two decades of experience in the industry as a trader, analyst and system developer. The author of a best-selling trading book, he has traded for his own account, for a top prop firm, and spent several years at the New York Mercantile Exchange. He focuses on the intersection of quantitative analysis and discretionary trading, and has a talent for teaching and helping traders find their own way in the market.

This Post Has 47 Comments

  1. Awillms

    Excellent information. Is there an excel spreadsheet calculation that can be used to determine a portfolio’s suseptability to tail risk?

    1. Adam Grimes

      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)

      1. Girish Menghani

        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

        1. Adam Grimes

          “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.

  2. Gerald Gjini

    or you can grab an ATR indicator and set the period to 252(trading days)….

    1. Adam Grimes

      ATR has nothing to do with historical volatility. Completely different calculation and application.

  3. Kelvin

    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?

    1. Adam Grimes

      Kelvin,
      The value in cell C3 is negative. Are you familiar with the convention of putting negative numbers in parenthesis? I.e. (1) = -1?

  4. Vishq

    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..

    1. Adam Grimes

      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.

  5. John

    trading days in the year is 252?

    1. Adam Grimes

      The annualization factor you use is not that important (as long as you’re consistent.)

  6. Dave

    shouldn’t your annualization factor be proportional to the number of time frames included ie 20 day STDev therefore sqrt ( 262/20 )??

    1. Abhishek Bhatnagar

      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)

  7. James

    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.

    1. Adam Grimes

      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.

  8. VP

    What’s the difference between a 20 days volatility and a 90 days volatitliy?

    1. Adam Grimes

      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.

  9. Nour

    Thank you so much for this information..
    could you please tell me how can i calculate realized skewness

    1. Adam Grimes

      Simple analysis of returns will let you do this. Excel can do it.

  10. jop

    Very useful refresher. Thanks. helped me with looking into a question of an investment manager.

  11. Socrates-Trader

    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.

    1. Socrates-Trader

      Oh, happy now. I have figured it out. 🙂
      I have to exclude the current day… Now I have nearly your numbers. Thanks a lot!

      1. Adam Grimes

        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.

  12. Markus

    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

  13. sreenivasa murthy

    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

    1. Adam Grimes

      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.

  14. Anndy

    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!

    1. Adam Grimes

      That’s a very different topic, but I may cover that in the near future.

  15. Shaurya Prabhat

    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?

    1. Adam Grimes

      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.

  16. iamspartacus

    Why did you use arithmetic return and not logarithmic return?

  17. Debroah Hanlin

    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 ?

    1. ryder.lowe

      Hi Debroah, my assistant got ahold of a sample Census BC-600 example with this link https://goo.gl/D5AOCa.

  18. Jonatan

    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?

  19. Emre Özlem

    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

  20. carolina

    Hi Adam, is there any difference in the annualization procedure when using log returns?

  21. Thomas

    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!

    1. James Dalton

      I second this request, also interested in the excel work behind the book example

Comments are closed.