0
$\begingroup$

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
$\endgroup$
  • $\begingroup$ Hi there, and welcome to QSE. Could you please add a bit more detail to your question? Also, please format your question a bit more readable. Thanks! $\endgroup$ – Kermittfrog Jun 23 at 17:32
  • $\begingroup$ Are you calling the Calculate subroutine in a different worksheet than where the data is? $\endgroup$ – amdopt Jun 24 at 12:17
0
$\begingroup$

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.

| improve this answer | |
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.