When we model a problem, we will usually have a set of inputs, feed it into the model and we get the output. There are also times, when we have a target output and we want to know what the input value (out of all the other inputs) would be in order to achieve that output value.

Take the example where we compute the value of a European-style call option using the Black-Scholes formula. Often times, traders will want to know the implied volatility given the option price.

Well, you could estimate the implied volatility if you implement the Newton-Raphson method or other numerical methods, but a quick and easy way to do this in Excel will be to use GoalSeek.

Excel file can be found at: https://drive.google.com/file/d/1fY6Og1zIhJq1PvTZftAbC7zQ0teXQlYi/view?usp=sharing

For illustration, I will assume:

  • Underlying price = 50
  • Strike/Exercise price = 52
  • Volatility = 20% (Initially)
  • Time to Maturity = 6 months (0.5 years)
  • Risk-free rate = 3% p.a.

Using the Black-Scholes formula, we would get the call option price as 2.28.

But, if the call option price is 5.20, then what would the implied volatility be?