Implied Vol UDF

[K-MAN69]

Can anyone tell me what is wrong with my code?

I ran it in the immdeiate window and it jamed up Excel and I had to end process in the task manager.

It also said that a sub or function was not defined?

Function AA_ImpliedVol_FutOpt(Expiry As Date, DealDate As Date, Spot As Double, Strike As Double, OptPrice As Double, RFR As Double, OptType As String)

Dim TimeYears As Double
Dim d1 As Double
Dim d2 As Double
Dim ert As Double
Dim N_Dash_d1 As Double 'The normal distribution curve
Dim Vega As Double
Dim OptPriceTheory As Double
Dim X_Next As Double
Dim X_N As Double
Dim Error_Val As Double
Dim ctr As Double
Vol = 0.05
TimeYears = (Expiry - DealDate + 1) / 360
d1 = (Application.Ln(Spot / Strike) + Vol ^ 2 * TimeYears * 0.5) / (Vol * TimeYears ^ 0.5)
d2 = d1 - Vol * TimeYears ^ 0.5
ert = Exp(-RFR * TimeYears)
N_Dash_d1 = (1 / ((2 * 3.1415926535) ^ 0.5)) * Exp((-d1 ^ 2) * 0.5)
Vega = (Spot * ert * N_Dash_d1 * TimeYears ^ 0.5)
If OptType = "C" Then
OptPriceTheory = ert * (Spot * Application.NormSDist(d1) - Strike * Application.NormSDist(d2))
ElseIf OptType = "P" Then
OptPriceTheory = ert * (Strike * Application.NormSDist(-d2) - Spot * Application.NormSDist(-d1))
End If
X_N = OptPrice
ctr = 0
Do
X_Next = X_N - OptPriceTheory / Vega
Error_Val = X_Next - X_N
X_N = X_Next
ctr = ctr + 1
Loop Until (Abs(Error_Val) <= 0.0000001 Or crt = 1000)
AA_ImpliedVol_FutOpt = X_Next

End Function

 

[Mohammad Basem]

It seems the macro trapped in a very long loop.

In the Do...Loop you have the line

ctr = ctr + 1

while the loop is testing the condition

Loop Until (Abs(Error_Val) <= 0.0000001 Or crt = 1000)

Use Option Explicit to get rid of such typo errors

 

[K-MAN69]

I took that advise thank you it helped alot. I changed my code to that in pink.

I am actually adapting the code in Red

My code is at least producing a result but it is not correct. I admit that I am using code that I do not quite understand. but if anyone knows enough to understand what I am truing to do please help.

Function AA_ImpliedVol_FutOpt(ByVal Expiry As Date, ByVal DealDate As Date, ByVal Spot As Double, ByVal Strike As Double, ByVal OptPrice As Double, ByVal RFR As Double, ByVal OptType As String)

Dim TimeYears As Double
Dim d1 As Double
Dim d2 As Double
Dim ert As Double
Dim N_Dash_d1 As Double 'The normal distribution curve
Dim Vega As Double
Dim OptPriceTheory As Double
Dim X_Next As Double
Dim X_N As Double
Dim Error_Val As Double
Dim ctr As Double
Dim Vol As Double
Vol = 0.05
TimeYears = (Expiry - DealDate + 1) / 360
d1 = (Application.Ln(Spot / Strike) + Vol * Vol * TimeYears * 0.5) / (Vol * TimeYears ^ 0.5)
d2 = d1 - Vol * TimeYears ^ 0.5
ert = Exp(-RFR * TimeYears)
N_Dash_d1 = (1 / ((2 * 3.1415926535) ^ 0.5)) * Exp((-d1 * -d1) * 0.5)
Vega = (Spot * ert * N_Dash_d1 * TimeYears ^ 0.5)
If OptType = "C" Then
OptPriceTheory = ert * (Spot * Application.NormSDist(d1) - Strike * Application.NormSDist(d2))
ElseIf OptType = "P" Then
OptPriceTheory = ert * (Strike * Application.NormSDist(-d2) - Spot * Application.NormSDist(-d1))
End If
X_N = OptPrice
ctr = 0
Do
X_Next = X_N - OptPriceTheory / Vega
Error_Val = X_Next - X_N
X_N = X_Next
ctr = ctr + 1
Loop Until (Abs(Error_Val) <= 0.0000001 Or ctr = 1000)
AA_ImpliedVol_FutOpt = X_Next

End Function

Function BlackScholesNewton(ByVal Seed As Double, ByVal
Precision As Double, ByVal S As Double, ByVal K As Double, ByVal r
As Double, ByVal q As Double, ByVal t As Double, ByVal sigma As
Double) As Double
Dim x_next As Double
Dim x_n As Double
Dim error_val As Double
Dim ctr As Integer
x_n = Seed
ctr = 0
Do
x_next = x_n - Black_Scholes(S, K, r, q, t, sigma) / Vega(S, K, r, q, t,
sigma)
error_val = x_next - x_n
x_n = x_next
ctr = ctr + 1
Loop Until (Abs(error_val) <= Precision Or ctr = 1000)
BlackScholesNewton = x_next
End Function
Function Black_Scholes(ByVal S As Double, ByVal K As Double,
ByVal r As Double, ByVal q As Double, ByVal t As Double, ByVal
sigma As Double) As Double
Dim d1 As Double
Dim d2 As Double
Dim a As Double
Dim b_call As Double
Dim b_put As Double
Dim c As Double
Dim call_price As Double
Dim put_price As Double
a = Log(S / K)
b_call = (r - q + 0.5 * sigma ^ 2) * t
b_put = (r - q - 0.5 * sigma ^ 2) * t
c = sigma * Sqr(t)
d1 = (a + b_call) / c
d2 = (a + b_put) / c
call_price = S * WorksheetFunction.NormDist(d1) - K * Exp(-r * t) *
WorksheetFunction.NormDist(d2)
put_price = K * Exp(-r * t) * WorksheetFunction.NormDist(-d2) - S *
WorksheetFunction.NormDist(-d1)
Black_Scholes = Array(call_price, put_price)​

End Function

Function Vega(ByVal S As Double, ByVal K As Double, ByVal r As
Double, ByVal q As Double, ByVal t As Double, ByVal sigma As
Double) As Double
Dim d1 As Double
Dim d2 As Double
Dim a As Double
Dim b_call As Double
Dim b_put As Double
Dim c As Double
Dim call_price As Double
Dim put_price As Double
a = Log(S / K)
b_call = (r - q + 0.5 * sigma ^ 2) * t
b_put = (r - q - 0.5 * sigma ^ 2) * t
c = sigma * Sqr(t)
d1 = (a + b_call) / c
d2 = (a + b_put) / c
Vega = S * Sqr(t) * WorksheetFunction.NormInv(d1) * Exp ^ (-q * t)​

End Function

 

[Mohammad Basem]

If you have the formulas, tracing the code won't be that difficult

 

[K-MAN69]

I have changed the code to below but I am getting a runtime error '6' overflow.

I think this means one of the variables is not defined appropriatly.

I am not sure which one but the Debug says its the highlighted line

Can anyone please help?

Option Explicit
Function OptionPriceBlack76(Expiry As Date, DealDate As Date, Spot As Double, Strike As Double, RFR As Double, Vol As Double, OptType As String) As Double
Dim TimeYears As Double
Dim d1 As Double
Dim d2 As Double
Dim ert As Double
Dim N_Dash_d1 As Double 'The normal distribution curve
 
TimeYears = (Expiry - DealDate + 1) / 360
d1 = (Application.Ln(Spot / Strike) + Vol ^ 2 * TimeYears * 0.5) / (Vol * TimeYears ^ 0.5)
d2 = d1 - Vol * TimeYears ^ 0.5
ert = Exp(-RFR * TimeYears)
N_Dash_d1 = (1 / ((2 * Application.Pi) ^ 0.5)) * Exp((-d1 ^ 2) * 0.5)
 
'Price
If OptType = "C" Then
            OptionPriceBlack76 = ert * (Spot * Application.NormSDist(d1) - Strike * Application.NormSDist(d2))
ElseIf OptType = "P" Then
            OptionPriceBlack76 = ert * (Strike * Application.NormSDist(-d2) - Spot * Application.NormSDist(-d1))
End If
End Function

Function Vega_Black76(Expiry As Date, DealDate As Date, Spot As Double, Strike As Double, Vol As Double, RFR As Double, OptType As String) As Double
Dim TimeYears As Double
Dim d1 As Double
Dim d2 As Double
Dim ert As Double
Dim N_Dash_d1 As Double 'The normal distribution curve
 
TimeYears = (Expiry - DealDate + 1) / 360
d1 = (Application.Ln(Spot / Strike) + Vol ^ 2 * TimeYears * 0.5) / (Vol * TimeYears ^ 0.5)
d2 = d1 - Vol * TimeYears ^ 0.5
ert = Exp(-RFR * TimeYears)
N_Dash_d1 = (1 / ((2 * 3.14159265358979) ^ 0.5)) * Exp((-d1 ^ 2) * 0.5)
Vega_Black76 = Spot * ert * N_Dash_d1 * TimeYears ^ 0.5
End Function

Function AA_ImpliedVol_FutOpt(Expiry As Date, DealDate As Date, Spot As Double, Strike As Double, OptPrice As Double, RFR As Double, OptType As String)
Dim VolGuess As Double 'This is our first guess for Vol
Dim Accuracy As Double 'How accurate you want the calc to be
Dim dVol As Double
Dim vol_1 As Double
Dim i As Integer 'This is the iteration counter
Dim MaxIter As Integer 'This is the maximum allowable iterations the calc will do
Dim Value_1 As Double
Dim vol_2 As Double
Dim Value_2 As Double
Dim NRF As Double '(OptPrice [Using Guess] - Observed OptPrice)/Vega [Using Guess]
VolGuess = 0.1
dVol = 0.00001
Accuracy = 0.00000001
MaxIter = 100
vol_1 = VolGuess
i = 1
Do
   Value_1 = OptionPriceBlack76(Expiry, DealDate, Spot, Strike, RFR, vol_1, OptType)
   vol_2 = vol_1 - dVol
   Value_2 = OptionPriceBlack76(Expiry, DealDate, Spot, Strike, RFR, vol_2, OptType)
   NRF = (Value_2 - Value_1) / Vega_Black76(Expiry, DealDate, Spot, Strike, vol_2, RFR, OptType)
   If Abs(NRF) < Accuracy Or i = MaxIter Then Exit Do
   vol_1 = vol_1 - (OptPrice - Value_1) / NRF
   i = i + 1
Loop
AA_ImpliedVol_FutOpt = vol_1
 
End Function

 

[Mohammad Basem]

Vega_Black76(Expiry, DealDate, Spot, Strike, vol_2, RFR, OptType)

returns Zero because N_Dash_d1 is evaluated to Zero, as Exp((-d1 ^ 2) * 0.5) equals Zero.

Could you please post sample of valid data, or we can just enter anything?

 

[tusharm]

You may want to start by helping the volunteers who are willing to help you. Figure out how to share code that is formatted for readability. Check out how to use the # button just above the textbox you use for posting messages in this forum.

I have changed the code to below but I am getting a runtime error '6' overflow.

I think this means one of the variables is not defined appropriatly.

I am not sure which one but the Debug says its the highlighted line

Can anyone please help?

Option Explicit
Function OptionPriceBlack76(Expiry As Date, DealDate As Date, Spot As Double, Strike As Double, RFR As Double, Vol As Double, OptType As String) As Double
Dim TimeYears As Double
Dim d1 As Double
Dim d2 As Double
Dim ert As Double
Dim N_Dash_d1 As Double 'The normal distribution curve

TimeYears = (Expiry - DealDate + 1) / 360
d1 = (Application.Ln(Spot / Strike) + Vol ^ 2 * TimeYears * 0.5) / (Vol * TimeYears ^ 0.5)
d2 = d1 - Vol * TimeYears ^ 0.5
ert = Exp(-RFR * TimeYears)
N_Dash_d1 = (1 / ((2 * Application.Pi) ^ 0.5)) * Exp((-d1 ^ 2) * 0.5)

'Price
If OptType = "C" Then
OptionPriceBlack76 = ert * (Spot * Application.NormSDist(d1) - Strike * Application.NormSDist(d2))
ElseIf OptType = "P" Then
OptionPriceBlack76 = ert * (Strike * Application.NormSDist(-d2) - Spot * Application.NormSDist(-d1))
End If
End Function

Function Vega_Black76(Expiry As Date, DealDate As Date, Spot As Double, Strike As Double, Vol As Double, RFR As Double, OptType As String) As Double
Dim TimeYears As Double
Dim d1 As Double
Dim d2 As Double
Dim ert As Double
Dim N_Dash_d1 As Double 'The normal distribution curve

TimeYears = (Expiry - DealDate + 1) / 360
d1 = (Application.Ln(Spot / Strike) + Vol ^ 2 * TimeYears * 0.5) / (Vol * TimeYears ^ 0.5)
d2 = d1 - Vol * TimeYears ^ 0.5
ert = Exp(-RFR * TimeYears)
N_Dash_d1 = (1 / ((2 * 3.14159265358979) ^ 0.5)) * Exp((-d1 ^ 2) * 0.5)
Vega_Black76 = Spot * ert * N_Dash_d1 * TimeYears ^ 0.5
End Function

Function AA_ImpliedVol_FutOpt(Expiry As Date, DealDate As Date, Spot As Double, Strike As Double, OptPrice As Double, RFR As Double, OptType As String)
Dim VolGuess As Double 'This is our first guess for Vol
Dim Accuracy As Double 'How accurate you want the calc to be
Dim dVol As Double
Dim vol_1 As Double
Dim i As Integer 'This is the iteration counter
Dim MaxIter As Integer 'This is the maximum allowable iterations the calc will do
Dim Value_1 As Double
Dim vol_2 As Double
Dim Value_2 As Double
Dim NRF As Double '(OptPrice [Using Guess] - Observed OptPrice)/Vega [Using Guess]
VolGuess = 0.1
dVol = 0.00001
Accuracy = 0.00000001
MaxIter = 100
vol_1 = VolGuess
i = 1
Do
Value_1 = OptionPriceBlack76(Expiry, DealDate, Spot, Strike, RFR, vol_1, OptType)
vol_2 = vol_1 - dVol
Value_2 = OptionPriceBlack76(Expiry, DealDate, Spot, Strike, RFR, vol_2, OptType)
NRF = (Value_2 - Value_1) / Vega_Black76(Expiry, DealDate, Spot, Strike, vol_2, RFR, OptType)
If Abs(NRF) < Accuracy Or i = MaxIter Then Exit Do
vol_1 = vol_1 - (OptPrice - Value_1) / NRF
i = i + 1
Loop
AA_ImpliedVol_FutOpt = vol_1

End Function

 

[K-MAN69]

Thanks a million!

I have now solved the problems and here is my code complete and working!!!!

[COLOR=magenta]Option Explicit[/COLOR]

[COLOR=magenta]Function OptionPriceBlack76(Expiry As Date, DealDate As Date, Spot As Double, Strike As Double, RFR As Double, Vol As Double, OptType As String)[/COLOR]
[COLOR=magenta]Dim TimeYears As Double
Dim d1 As Double
Dim d2 As Double
Dim ert As Double
Dim N_Dash_d1 As Double 'The normal distribution curve
 
TimeYears = (Expiry - DealDate + 1) / 360
d1 = (Application.Ln(Spot / Strike) + Vol ^ 2 * TimeYears * 0.5) / (Vol * TimeYears ^ 0.5)
d2 = d1 - Vol * TimeYears ^ 0.5
ert = Exp(-RFR * TimeYears)
N_Dash_d1 = (1 / ((2 * 3.14159265358979) ^ 0.5)) * Exp((-d1 ^ 2) * 0.5)
 
'Price
If OptType = "C" Then
            OptionPriceBlack76 = ert * (Spot * Application.NormSDist(d1) - Strike * Application.NormSDist(d2))
ElseIf OptType = "P" Then
            OptionPriceBlack76 = ert * (Strike * Application.NormSDist(-d2) - Spot * Application.NormSDist(-d1))
End If[/COLOR]
[COLOR=magenta]End Function[/COLOR]

[COLOR=magenta]Function Vega_Black76(Expiry As Date, DealDate As Date, Spot As Double, Strike As Double, Vol As Double, RFR As Double, OptType As String) As Double[/COLOR]
[COLOR=magenta]Dim TimeYears As Double
Dim d1 As Double
Dim d2 As Double
Dim ert As Double
Dim N_Dash_d1 As Double 'The normal distribution curve
 
TimeYears = (Expiry - DealDate + 1) / 360
d1 = (Application.Ln(Spot / Strike) + Vol ^ 2 * TimeYears * 0.5) / (Vol * TimeYears ^ 0.5)
d2 = d1 - Vol * TimeYears ^ 0.5
ert = Exp(-RFR * TimeYears)
N_Dash_d1 = (1 / ((2 * 3.14159265358979) ^ 0.5)) * Exp((-d1 ^ 2) * 0.5)[/COLOR]
[COLOR=magenta]Vega_Black76 = Spot * ert * N_Dash_d1 * TimeYears ^ 0.5[/COLOR]
[COLOR=magenta]End Function[/COLOR]

[COLOR=magenta]Function AA_ImpliedVol_FutOpt(Expiry As Date, DealDate As Date, Spot As Double, Strike As Double, OptPrice As Double, RFR As Double, OptType As String)[/COLOR]
[COLOR=magenta]Dim Vol As Double 'This is our first guess for Vol
Dim Accuracy As Double 'How accurate you want the calc to be
Dim i As Integer 'This is the iteration counter
Dim MaxIter As Integer 'This is the maximum allowable iterations the calc will do
Dim Value_1 As Double
Dim Vega_1 As Double
Dim NRF As Double '(OptPrice [Using Guess] - Observed OptPrice)/Vega [Using Guess][/COLOR]
[COLOR=magenta]Vol = 0.1
Accuracy = 0.00000000001
MaxIter = 100
i = 1[/COLOR]
[COLOR=magenta]Do
   Value_1 = OptionPriceBlack76(Expiry, DealDate, Spot, Strike, RFR, Vol, OptType)
   Vega_1 = Vega_Black76(Expiry, DealDate, Spot, Strike, Vol, RFR, OptType)
   NRF = (Value_1 - OptPrice) / Vega_1
   Vol = Vol - NRF
   i = i + 1
   
Debug.Print "Value_1: " & Value_1
Debug.Print "Vega_1: " & Vega_1
Debug.Print "NRF: " & NRF
Debug.Print "Vol: " & Vol
Debug.Print "Iteration: " & i
    
Loop Until Abs(NRF) <= Accuracy Or i = MaxIter[/COLOR]
[COLOR=magenta]AA_ImpliedVol_FutOpt = Vol[/COLOR]
[COLOR=magenta] 
End Function[/COLOR]

 

[Mohammad Basem]

Al-hamdullah. Glad to hear that and thank you for the feedback!

 

[K-MAN69]

Just a bit of an after thought - in the above code, the line that I have highlighted in pink "Vol = 0.1" is a guess to begin a Newton Raphson iteration.

If my guess is not close enough, a result will not be possible and the cell will return "#VALUE!". if this happens I want to begin the proces all over again with a new guess, say Vol = 0.5

.... and if that returns #VALUE! then a new guess say Vol = 1

How can I achieve this?

Option Explicit

Function OptionPriceBlack76(Expiry As Date, DealDate As Date, Spot As Double, Strike As Double, RFR As Double, Vol As Double, OptType As String)
Dim TimeYears As Double
Dim d1 As Double
Dim d2 As Double
Dim ert As Double
Dim N_Dash_d1 As Double 'The normal distribution curve
 
TimeYears = (Expiry - DealDate + 1) / 360
d1 = (Application.Ln(Spot / Strike) + Vol ^ 2 * TimeYears * 0.5) / (Vol * TimeYears ^ 0.5)
d2 = d1 - Vol * TimeYears ^ 0.5
ert = Exp(-RFR * TimeYears)
N_Dash_d1 = (1 / ((2 * 3.14159265358979) ^ 0.5)) * Exp((-d1 ^ 2) * 0.5)
 
'Price
If OptType = "C" Then
            OptionPriceBlack76 = ert * (Spot * Application.NormSDist(d1) - Strike * Application.NormSDist(d2))
ElseIf OptType = "P" Then
            OptionPriceBlack76 = ert * (Strike * Application.NormSDist(-d2) - Spot * Application.NormSDist(-d1))
End If
End Function

Function Vega_Black76(Expiry As Date, DealDate As Date, Spot As Double, Strike As Double, Vol As Double, RFR As Double, OptType As String) As Double
Dim TimeYears As Double
Dim d1 As Double
Dim d2 As Double
Dim ert As Double
Dim N_Dash_d1 As Double 'The normal distribution curve
 
TimeYears = (Expiry - DealDate + 1) / 360
d1 = (Application.Ln(Spot / Strike) + Vol ^ 2 * TimeYears * 0.5) / (Vol * TimeYears ^ 0.5)
d2 = d1 - Vol * TimeYears ^ 0.5
ert = Exp(-RFR * TimeYears)
N_Dash_d1 = (1 / ((2 * 3.14159265358979) ^ 0.5)) * Exp((-d1 ^ 2) * 0.5)
Vega_Black76 = Spot * ert * N_Dash_d1 * TimeYears ^ 0.5
End Function

Function AA_ImpliedVol_FutOpt(Expiry As Date, DealDate As Date, Spot As Double, Strike As Double, OptPrice As Double, RFR As Double, OptType As String)
Dim Vol As Double 'This is our first guess for Vol
Dim Accuracy As Double 'How accurate you want the calc to be
Dim i As Integer 'This is the iteration counter
Dim MaxIter As Integer 'This is the maximum allowable iterations the calc will do
Dim Value_1 As Double
Dim Vega_1 As Double
Dim NRF As Double '(OptPrice [Using Guess] - Observed OptPrice)/Vega [Using Guess]
Vol = 0.1
Accuracy = 0.00000000001
MaxIter = 100
i = 1
Do
   Value_1 = OptionPriceBlack76(Expiry, DealDate, Spot, Strike, RFR, Vol, OptType)
   Vega_1 = Vega_Black76(Expiry, DealDate, Spot, Strike, Vol, RFR, OptType)
   NRF = (Value_1 - OptPrice) / Vega_1
   Vol = Vol - NRF
   i = i + 1
   
Debug.Print "Value_1: " & Value_1
Debug.Print "Vega_1: " & Vega_1
Debug.Print "NRF: " & NRF
Debug.Print "Vol: " & Vol
Debug.Print "Iteration: " & i
    
Loop Until Abs(NRF) <= Accuracy Or i = MaxIter
AA_ImpliedVol_FutOpt = Vol
 
End Function