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)

how to calculate implied volatility in excel result

 


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.

Use Goal Seek Feature to Calculate Implied Volatility in Excel

  • 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.

  • See the adjusted Implied Volatility for our Target Call Option Price.

how to calculate implied volatility in excel result

 


Download Practice Workbook

You can download the practice workbook from here.


Related Articles


<< Go Back to Volatility In Excel | Excel for Finance | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Mehedi Hasan Shimul
Mehedi Hasan Shimul

Md. Mehedi Hasan, with a BSc in Electrical & Electronic Engineering from Bangladesh University of Engineering and Technology, holds a crucial position as an Excel & VBA Content Developer at ExcelDemy. Driven by a deep passion for research and innovation, he actively immerses himself in Excel. In his role, Mehedi not only skillfully addresses complex challenges but also exhibits enthusiasm and expertise in gracefully navigating tough situations, emphasizing his steadfast commitment to consistently deliver exceptional and quality content.... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo