The Black-Scholes model can be used to estimate * implied volatility*.

**Implied Volatility can be estimated using spot price, strike price, asset price, risk-free rate, time to maturity, and dividend yield. To achieve this, given an actual option value, you have to iterate to find the volatility solution. There are various techniques available; however we will use the Newton-Raphson bisection method.**

**Newton-Raphson Method and Implied Volatility**

Implied Volatility is distinctively different from historical volatility measures. The term implied volatility comes from the fact that the volatility is removed from the market prices of options. Using Black-Scholes option pricing model, we can calculate Implied Volatility using trial and error. A great benefit of Newton-Raphson bisection method is that it gives fast convergences and the error approximation reduces rapidly with each additional iteration. The equation to calculate Implied Volatility of an option:

where,

P_{m} is the market price of the option which we are trying to solve a fit for,

P_{t} is the option price given by Black-Scholes equation,

σ is the implied volatility

Once Black-Scholes is structured, we use an iterative technique to solve for σ.

**An Example…**

An itm option has 10 days for expiration. The strike price is 55 and the current stock price is 50. The stock has daily volatility of 0.03. The risk free interest rate is assumed to be 0.02.

Alternatively, d2 can be calculated as

Thus, the European Call value can be calculated as

where,

C = call value,

S = spot price of 50,

t = expiry period which is 10 days,

Norm = Normal probability distribution with mu=0 and sigma = 1,

K = option strike price of 55,

r = risk free rate of interest – we have assumed to be 0.02 in this example,

σ = daily stock volatility of 3% or 0.03

In excel, the formula can be written as

50*NORM.S.DIST(-0.95145,TRUE)-55*EXP((-0.02) * 10/365)*NORM.S.DIST(-1.04632,TRUE) .

**Implied Volatility Calculator in Excel – VBA Function to Calculate Implied Volatility using Newton-Raphson method**

The spreadsheet is quite straightforward. Just enter current stock price, strike price, risk free rate, days to maturity, dividend yield (if any) and the option price. The VBA computes implied volatility and back solves the option price which you have entered. As a verification, the option price calculated using Black-Scholes equation must equal to the option price specified in the input parameters.

The following VBA function calculates the price of an European option using Black-Scholes equation.

As evident by function name, lines 19 through 47 calculate implied volatility for given input parameters as defined in name range on worksheet. The method iterates till it finds a solution. Precision is defined by constant DELTA_VOL, which is the acceptable error of the function’s result. Since the function iterates to find the correct volatility, setting a higher value can speed execution in a worksheet containing 100 and above instances of this function. Lower values increases the precision. Example: If the exact implied volatility is 16%, setting “Precision” to .05 will cause the function to return a value between 15.95% and 16.05%.

Closing Notes:

- “European” call options have the same theoretical value as “American” calls. American puts are difficult to value than European puts, but experts agree the Black-Scholes formula provides a good approximation of volatility.
- Since the Black-Scholes formula cannot be deconstructed to solve for volatility mathematically, this function uses an iterative process to find the implied volatility. It is very similar to Excel’s “Goal-Seek” function, and other built-in Excel functions such as IRR.
- Dividend Yield – The annualized dividend yield of the underlying stock expressed in continuous compounding terms.

**Download the Implied Volatility Spreadsheet here**

More free spreadsheets at investsolver.com