web analytics

How Do You Calculate Volatility In Excel?

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

  • Awillms

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

    • http://www.adamhgrimes.com 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)

      • http://www.facebook.com/girish.menghani 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

        • http://www.adamhgrimes.com 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.

  • http://www.facebook.com/profile.php?id=30303728 Gerald Gjini

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

    • http://www.adamhgrimes.com Adam Grimes

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

  • 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?

    • http://www.adamhgrimes.com 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?

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

    • http://www.adamhgrimes.com 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.

  • John

    trading days in the year is 252?

  • Dave

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

    • 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)

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

    • http://www.adamhgrimes.com 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.