Market Data Logo
Last Update: April 30, 2023

How To Calculate Implied Volatility In Your Spreadsheet

Using the OPTIONDATA formula, you can calculate implied volatility for any option.

Using Google Sheets it is easy to calculate implied volatility of any option that is currently trading by using the OPTIONDATA formula. Just use =OPTIONDATA("OPTION SYMBOL", "iv") and the formula will return the result. The result will be returned as a decimal. Use the Format as a percent button to format the result if you prefer to view the implied volatility as a percent.

For example, =OPTIONDATA("AAPL240119C00150000","PRICE,IV") will return the current price and the implied volatility of a $150 AAPL Call expiring on January 19, 2024. 

Make sure you have installed the Market Data Google Sheets Add-On before using these formulas.

Graph & Calculate The Implied Volatility Curve in Google Sheets

One thing you can do with implied volatilities is view the option volatility curve visually in a chart. Follow these steps:

  1. Create a table with each option symbol in the chain and double check to make sure all option symbols are correct.
  2. Use the OPTIONDATA formula to get the IV for each contract.
  3. Use a the chart option and select a line graph.
  4. Put the strike on the X-axis and implied volatility on the Y-axis.
  5. Set the horizontal and vertical min and max values according to your preferences to modify the scale of the chart.

Important: Make sure all the options you are using have the same expiration or the curve will not be correct.

Advanced Charting Tip: View Multiple Expiration Curves

You can analyze the volatility from options with different expirations on a single chart by building multiple tables and putting each expiration as a new series in the chart. This will let you see each expiration date as a different line in the chart.

On this page
    Add a header to begin generating the table of contents
    Share this article
    Comments & Questions
    Shopping Basket