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:
- Create a table with each option symbol in the chain and double check to make sure all option symbols are correct.
- Use the
OPTIONDATAformula to get the IV for each contract. - Use a the chart option and select a line graph.
- Put the strike on the X-axis and implied volatility on the Y-axis.
- 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.
