Wednesday, September 18, 2013

Calculating Implied Volatility using Excel

The following is a guest post by trader, blogger and good friend Gavin from Options Trading IQ. Check out his website for useful information and a transparent approach to trading.

Options prices or premiums fluctuate with market sentiment on a daily basis and are driven by a number of factors which include the underlying price of the security, current interest rates, the strike price of the option and implied volatility.

Options are the right but not the obligation to purchase (or sell) as security at a specific price on or before a certain date.  The theoretical price is created by traders who are trying to determine the chance that an underlying security will reach a strike price before the expiration date.  To do this a trader needs to imply how much the market will move over a specific period, which is reflected by implied volatility.

What is implied volatility?

Implied volatility is the market’s estimate of how much a security will move during a certain period of time on an annualized basis.  For example, an implied volatility of 20% means that traders believe that a specific security will move 20% either higher or lower from current levels.  Using the Dow Jones Industrial average as an example at 15,000 traders would expect the index to either move to 18,000 or 12,000 with 12 months.

Implied volatility is generally quoted in percent terms, and is different for each strike price and maturity date.  For example, an S&P 500 “at the money” call option will likely have a different implied volatility than a 10% out of the money call option. The difference is based on supply and demand for the option which will either increase or decrease the premium for a specific strike price.

One of the most popular gauges of implied volatility is the VIX index.  The VIX reflects the implied volatility for the “at the money” strike prices of the S&P 500 index. Historical Versus Implied Volatility Volatility not only refers to the market’s estimate of what will happen in the future but also the actual movements of a security on a historical basis.  Historical volatility is measured in percentage terms and reflects the standard deviation of a time series.  Historical volatility is obviously different from implied volatility as it is not a market estimate and reflects the actual movements of a security when observing a security in the rear view mirror.

Additionally, to gauge implied volatility, many traders track historical implied volatility which is past implied volatility levels.  Many traders view historical implied volatility levels on a chart to gauge future movements of implied volatility.

The below chart shows the difference in implied volatility and historical volatility of GOOG over the past
12 months.

Calculating Implied Volatility

Implied volatility is the most crucial component on the Black Scholes options pricing model. It is also the most subjective and therefore the area where we are most able to gain an edge. But how do you calculate implied volatility? Most brokers will show implied volatility as part of the option chain, but it’s always good to have a backup plan. Thankfully I have a spreadsheet that calculates IV for you.

You can adjust any variable in the parameters section. For example, your scenario might be that you expect volatility to rise from 0.20 to 0.23 over the next 5 days. You would change the volatility value and also the expiry time to take into account the passage of 5 days, then using the Goal Seek function in excel, calculate the option values. Note that this designed for European options, not American options.

The spreadsheet also gives you other cool data such as the change in greeks for a given change in volatility, time to expiry, stock price etc.