How to Calculate Implied Volatility in Excel: 2 Simple Methods

Method 1 – Calculate Implied Volatility for Specific Call Option Price by Iteration

Calculate the call option price using the Black Scholes Model formula. Change the implied volatility until the Option Price matches our expected value.

Include the data for the Underlying Price, Strike Price, Volatility, Time to Maturity, and Risk-free Rate for 2 cases.
The Volatility should be slightly different when calculating Implied Volatility for a specific Target Call Option Price.

how to calculate implied volatility in excel

Calculate the conditional probability d1 by using the following formula in cell C11.

=(LN(C5/C6)+(C9+0.5*C7^2)*C8)/(C7*SQRT(C8))

Note: The formula is derived from Black Scholes Model.

Apply the following formula in cell C12 to determine the probability for the option to expire on money (d2).

=C11-C7*SQRT(C8)

We used the SQRT function to determine the square root of cell C8.

Calculate the statistical measures (normal distribution) N(d1) in cell C13 by writing the following formula.

=NORM.S.DIST(C11,1)

We used the NORM.S.DIST function to calculate the normal distribution in Excel.

Write the following formula in cell C14 to get the statistical measures (normal distribution) N(d2).

=NORM.S.DIST(C12,1)

Calculate the Call Option Price in cell C15 by writing the following formula.

=C5*C13-C6*EXP(-C9*C8)*C14

Calculate the same parameters for the second case, like in the picture below.

The Call Option Price for both cases is a bit different than Target Call Option Price.
Use the Call Option Prices to determine our Implied Volatility for Target Call Option Price.
Write the following formula in cell F17 to calculate the approximate Implied Volatility.

=C7+(C17-C15)/(F15-C15)*(F7-C7)

Method 2 – Use Goal Seek Feature to Calculate Implied Volatility in Excel

Instead of calculating the implied volatility by manual iteration, we can use the Goal Seek feature of Excel to do the same task. This method is much simpler and more accurate. Follow the stepwise procedures given below for this method.

Fill up some input data for Underlying Price, Strike Price, Implied Volatility (arbitrary), Time to Maturity (in years), and Risk-free Rate.

Calculate the data for d1, d2, N(d1), N(d2), and Call Option Price following any case from Method 1.

Use the Goal Seek feature.
Go to the Data tab and select What-If Analysis > Goal Seek.

A Goal Seek window will appear.
Write the cell reference $C$15 (Target Call Option Price) in the Set to section.
Select the cell by clicking on the upload icon beside Set to.
Give the Target Call Option Value (we set 50) in the To Value section.
Write the cell reference $C$7 (Implied Volatility) by changing cell section.
Press OK.