Calculating Implied Volatility in Excel

The Black-Scholes option pricing formula can’t be deconstructed to determine a direct formula for implied volatility. However, if you know the option’s price and all the remaining parameters (underlying price, strike price, interest rate, dividend yield, and time to expiration), you can use the Goal Seek feature in Excel to find it. This page explains how to do it in the Black-Scholes Calculator (but the logic is the same if you do it on your own and prepare all the Black-Scholes model formulas yourself).

I will illustrate the Excel calculation of implied volatility step-by-step on the example below.


You want to find implied volatility of a call option with strike price of 55 and 18 calendar days to expiration. The risk free interest rate is 1%; the underlying stock’s continuously compounded dividend yield is 2%. The underlying stock is currently trading at 53.20 and the option is trading at 1.40.

Setting the Input Parameters

First, you must set all the parameters that enter option price calculation:

Now you have entered all the parameters and the resulting option price appears in cell H4 (or H6 if you are working with a put option). Using the values in our example, it is 1.59 dollars per share.

Unless you were very lucky, it is not equal to the actual price at which the option is trading at the moment (in our example 1.40). The reason is the volatility parameter, where you have entered a number you just guessed.

Note: Do not type any numbers in cells H4 and H6 (the resulting option prices). These cells contain formulas and if you overwrite them, the spreadsheet will not work correctly.

Trial and Error Approach

Now you can try to find the implied volatility by trial and error by entering different values in cell C8. If the real option price is lower than your result (as in our example), try lower volatility, and vice versa. For example, you can try to enter 45% into cell C8 and get option price of 1.36. So you try 47% and get 1.45… and so on. As soon you get close enough to the real option price (depending on your desired level of accuracy), you are done. The implied volatility is in cell C8.

Using Excel Goal Seek

The Goal Seek feature in Excel does exactly the same thing, only the computer is able to perform this trial and error exercise in split of a second and get a very accurate result immediately.

Once you have the input parameters set, go to Excel main menu and select Data, Data Tools, What-If Analysis, Goal Seek (in Excel 2010 – the path may be slightly different in other versions).

The Goal Seek window pops up and asks you to enter three inputs:

Now press OK and the desired implied volatility appears in cell C8 (45.83% in our example). At the same time, the option‘s price (1.40 in our example) should appear in cell H4 (or H6 if it was a put). You can also see the option’s delta, gamma, theta, vega, and rho to the right of option’s price in cells J4 to N4 (or J6 to N6).

Black-Scholes Calculator + PDF Guide

This tutorial is part of the PDF Guide that comes with the Black-Scholes Calculator. You can see more information about all features, calculations, and guide contents here.

Related pages

cot futuresshort payoffexcel formulas standard deviationpopulation deviation calculatordow jones industrial average weightingannualized return formula excelvix index yahooblack scholes d1 d2protective collar optionoption theta formulaarithmetic in excelexcel exp functionformula to calculate range in excelinverse of uvxyskewness and kurtosis in statisticsexplain kurtosisbarclays vxxexcel annualized returnaverage of percentages calculatorproshares ultra vix short-term futuresinvest in vixtrading puts and calls for dummiesstats variance formulawww cboe comcalculate cv in excelweighted average method calculatorvix futuresmfe calculatorwhat is the wacc formulaitm callrho tradingcomputation formula for standard deviationinterpreting variance and standard deviationmerits and demerits of central tendencycovariance calculator financesharpe ratio formulafree float market capitalizationyahoo finance calculatorsetf short s&p 500percentiles calculator statisticsvix index etfkurtosis statisticscontango curveticker vixblack schole option pricing modeldays calculator excelsharpe ratio in excelstock rsi calculatorhow to read rsihow to calculate sample variance in statisticsvix future settlementmeasure of skewnesskurtosis formula in statisticsdetermining skewnessharmonic mean excelfixed income arbitrage examplehow to calculate formula in excelatr average true rangeoption greeks deltaexcel formula macroexcel formula for average percentagesharpe ratio calculatorxxv etnblack scholes calculator with dividend yieldoptions calculator excelexcel variance calculationsample kurtosiswhat does overbought and oversold meangamma calculatornotepad color codesmultiplication square rootstheory of rational option pricingwhat is market capitalization with exampleproshares trust ultra vix shorttrue standard deviation formulafutures expiry