Calculate Implied Volatility with VBA

This article offers VBA code and an Excel spreadsheet to calculate the implied volatility of an option. This parameter is often compared to the historical volatility of the underlying asset to determine if the price of an option represents good value.

Implied volatility is the volatility estimated from the option price, asset price, strike price risk-free-rate, time to maturity and dividend yield. Calculating implied volatility needs iterative solution methods. Several technique are commonly used; one method uses Excel’s Goal Seek functionality, while other approaches use bisection or Newton-Raphson iteration.

The theory behind the Newton-Raphson method for finding the root of an equation is well documented. The approach gives the following equation to calculate the implied volatility of an option.

• V_mkt is the market price of the option
• V_BS is the option price given by the Black-Scholes equation
• σ is the volatility

Implied Volatility with Newton-Raphson Iteration

This VBA function calculates the price of a European option with the Black-Scholes equation.

Function EuropeanOption(CallOrPut, S, K, v, r, T, q)
Dim d1 As Double, d2 As Double, nd1 As Double, nd2 As Double
Dim nnd1 As Double, nnd2 As Double

d1 = (Log(S / K) + (r - q + 0.5 * v ^ 2) * T) / (v * Sqr(T))
d2 = (Log(S / K) + (r - q - 0.5 * v ^ 2) * T) / (v * Sqr(T))
nd1 = Application.NormSDist(d1)
nd2 = Application.NormSDist(d2)
nnd1 = Application.NormSDist(-d1)
nnd2 = Application.NormSDist(-d2)

If CallOrPut = "Call" Then
   EuropeanOption = S * Exp(-q * T) * nd1 - K * Exp(-r * T) * nd2
Else
   EuropeanOption = -S * Exp(-q * T) * nnd1 + K * Exp(-r * T) * nnd2
End If
End Function

This VBA function calculates the implied volatility of a European option with Newton-Raphson iteration.

Function ImpliedVolatility(CallOrPut, S, K, r, T, q, OptionValue, guess)
Dim epsilon As Double, dVol As Double, vol_1 As Double
Dim i As Integer, maxIter As Integer, Value_1 As Double, vol_2 As Double
Dim Value_2 As Double, dx As Double

dVol = 0.00001
epsilon = 0.00001
maxIter = 100
vol_1 = guess
i = 1
Do
   Value_1 = EuropeanOption(CallOrPut, S, K, vol_1, r, T, q)
   vol_2 = vol_1 - dVol
   Value_2 = EuropeanOption(CallOrPut, S, K, vol_2, r, T, q)
   dx = (Value_2 - Value_1) / dVol
   If Abs(dx) < epsilon Or i = maxIter Then Exit Do
   vol_1 = vol_1 - (OptionValue - Value_1) / dx
   i = i + 1
Loop
ImpliedVolatility = vol_1
End Function

• S is the asset price
• CallOrPut is either “Call” or “Put”
• K is the strike price
• r is the risk-free rate
• q is the dividend yield
• v is the volatility
• epsilon is a calculation tolerance
• maxIter is the maximum allowable number of iterations

This Excel spreadsheet implements the two VBA functions given above for a sample option.

As a check, the implied volatility is used to calculate the option price using the Black-Scholes equation. This should be equal to the option value specified in the Parameters section.

Download Excel Spreadsheet to Calculate Implied Volatility in VBA