How to Calculate Implied Volatility in Excel (2 Simple Methods)

Get FREE Advanced Excel Exercises with Solutions!

Implied volatility is a widely used parameter for the market price of any commodity. In this article, we will discuss about implied volatility and show 2 simple methods to calculate implied volatility in Excel.


What Is Implied Volatility?

Implied volatility is the probability of market price fluctuation for a commodity. It is frequently used to price option contracts where high implied volatility refers to the premium price and vice versa. It depends on supply, demand and time value mainly. We can determine the option price by using Black Scholes Model which will utilize the implied volatility, current stock price, strike price, and time until expiration. And we can back-calculate the implied volatility for a fixed option price using the Black Scholes Model.

The formula used in the model is:

C = SN (d1) – N (d2) Ke^(-rt)

Here,

  • C is the Option Premium.
  • S is the Price of the stock.
  • K is the Strike Price.
  • r is the Risk-free Rate.
  • t is the Time to Maturity.
  • e is the exponential term.
  • d1 is the Conditional Probability, d2 is the probability for the option to expire on money.
  • N(d1) and N(d2) are the statistical measures (normal distribution) for the values of d1 and d2 respectively.

How to Calculate Implied Volatility in Excel: 2 Simple Methods

In this section, we will use the Black Scholes Model formula to determine the implied volatility for a fixed option price with 2 simple methods. In the first method, we will iterate manually and use the Goal Seek feature of Excel in the second method.


1. Calculate Implied Volatility for Specific Call Option Price by Iteration

We can calculate the call option price using the Black Scholes Model formula. Later on, we will change the implied volatility until the Option Price matches our expected value. Follow the stepwise procedures given below for this method.

  • Firstly, include the data for the Underlying Price, Strike Price, Volatility, Time to Maturity, and Risk-free Rate for 2 cases.
  • Also, in the second case, the Volatility should be slightly different for calculating Implied Volatility for a specific Target Call Option Price.

how to calculate implied volatility in excel

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

  • Then, apply the following formula in cell C12 to determine the probability for the option to expire on money (d2).

=C11-C7*SQRT(C8)

Here, we have used the SQRT function to determine the square root of cell C8.

  • Afterward, calculate the statistical measures (normal distribution) N(d1) in cell C13 by writing the following formula there.

=NORM.S.DIST(C11,1)

Here, we have used the NORM.S.DIST function to calculate the normal distribution in Excel.

  • Further, write the following formula in cell C14 to get the statistical measures (normal distribution) N(d2).

=NORM.S.DIST(C12,1)

  • Afterward, let’s calculate the Call Option Price in cell C15 by writing the following formula there.

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

  • Later on, calculate the same parameters for the second case like in the picture below.

  • We can see the Call Option Price for both cases is a bit different than Target Call Option Price.
  • Now, let’s use the Call Option Prices to determine our Implied Volatility for Target Call Option Price.
  • Finally, write the following formula in cell F17 to calculate the approximate Implied Volatility there.

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

how to calculate implied volatility in excel result

Hurrah! We have successfully determined the Implied Volatility for Target Call Option Price.

Read More: How to Calculate Volatility for Black Scholes in Excel


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. Also, this method is much simpler and more accurate. Let’s follow the stepwise procedures given below for this method.

  • Primarily, 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

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

  • Then, we are ready to use the Goal Seek feature.
  • Further, go to the Data tab and select What-If Analysis > Goal Seek.

  • Consecutively, a Goal Seek window will appear.
  • Afterward, write the cell reference $C$15 (Target Call Option Price) in the Set to section.
  • Also, you can select the cell by clicking on the upload icon beside Set to.
  • Then, give the Target Call Option Value (we set 50) in the To Value section.
  • Again, write the cell reference $C$7 (Implied Volatility) in By changing cell section.
  • Also, press OK.

  • Finally, we will see the adjusted Implied Volatility for our Target Call Option Price.

how to calculate implied volatility in excel result

Read More: How to Calculate Realized Volatility in Excel


Download Practice Workbook

You can download the practice workbook from here.


Conclusion

Implied volatility is widely used for forecasting the future price of a commodity. In this article, we have shown 2 simple methods to calculate implied volatility in Excel. If you have any queries or suggestions, let us know in the comment section.


Related Articles


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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