TransWikia.com

VBA Black Scholes Implied Volatility

Quantitative Finance Asked by Zakaria Ourad on December 19, 2021

I keep getting a Implied Vol. = to my initial guess, My code is as bellow

Option Explicit
Option Base 0
Const EPS  As Double = 10 ^ -5
Const DELTA_VOL As Double = 10 ^ -9

Function CalcEuropeanOption(opType, S, K, v, rate, T, div)

Dim d1 As Long
Dim d2 As Long
Dim normd1 As Double
Dim normd2 As Double

d1 = (Log(S / K) + (rate - div + 0.5 * v ^ 2) * T) / (v * (T ^ 0.5))
d2 = (Log(S / K) + (rate - div - 0.5 * v ^ 2) * T) / (v * (T ^ 0.5))

normd1 = WorksheetFunction.NormSDist(d1)
normd2 = WorksheetFunction.NormSDist(d2)

If opType = "call" Then
       CalcEuropeanOption = S * normd1 * Exp(-div * T) - K * Exp(-rate * T) * normd2
    Else
       CalcEuropeanOption = -S * (1 - normd1) * Exp(-div * T) + K * Exp(-rate * T) * (1 - normd2)
    End If
End Function

Function CalcImpliedVolatility(optionType)
    Dim vol1 As Double
    Dim vol2 As Double
    Dim optval1 As Double
    Dim dtm As Long
    Dim optval2 As Double
    Dim dsigma As Double
    Dim spot As Double
    Dim strike As Double
    Dim r As Double
    Dim div As Double
    Dim optval As Double
    Dim maxIter As Integer
    Dim i As Integer
    Dim dataSheet As Worksheet
    
    Set dataSheet = ActiveSheet
    
    spot = dataSheet.Range("S").Value
    strike = dataSheet.Range("K").Value
    r = dataSheet.Range("C6").Value
    dtm = dataSheet.Range("T").Value
    vol1 = dataSheet.Range("C8").Value
    div = dataSheet.Range("div").Value
    optval = dataSheet.Range("optval").Value
    
    maxIter = 100
   
    Do
       optval1 = CalcEuropeanOption(optionType, spot, strike, vol1, r, dtm, div)
       vol2 = vol1 - DELTA_VOL
       optval2 = CalcEuropeanOption(optionType, spot, strike, vol2, r, dtm, div)
       dsigma = (optval2 - optval1) / DELTA_VOL
       
       If Abs(dsigma) < EPS Or i = maxIter Then Exit Do
          vol1 = vol1 - (optval - optval1) / dsigma
          i = i + 1
    Loop
    CalcImpliedVolatility = vol1
End Function

Sub Calculate()
    Dim dataSheet As Worksheet
    Set dataSheet = ActiveSheet
    dataSheet.Range("call").Value = CalcImpliedVolatility("call")
    dataSheet.Range("put").Value = CalcImpliedVolatility("put")

End Sub

One Answer

I have only read your code but it seems to me that DELTA_VOL is way too low. This causes vol1 and vol2 to be very close which doesn’t ensure the value your looking for is included in the initial bracket.

Answered by Bob Jansen on December 19, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP