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]

You will need something like the following.

[FONT="Consolas"][SIZE="2"][COLOR="Navy"]Dim blNewIncrement As Boolean
Dim dVolAssumption As Double

dVolAssumption = 0
Vol = 0.1
Accuracy = 0.00000000001
MaxIter = 100
i = 1
blNewIncrement = False

Do
    If blNewIncrement Then
        dVolAssumption = dVolAssumption + 0.5       [COLOR="Green"]'* Adjust as needed[/COLOR]
        Vol = dVolAssumption                        [COLOR="Green"]'* Adjust as needed[/COLOR]
        Accuracy = 0.00000000001                    [COLOR="Green"]'* In case further adjustment is needed[/COLOR]
        MaxIter = 100                               [COLOR="Green"]'* In case further adjustment is needed[/COLOR]
        i = 1
        blNewIncrement = False
    End If
[COLOR="Green"]    '*
    '* Code goes here
    '*[/COLOR]
    On Error Resume Next
[COLOR="Red"]    '*
    '* Statement that generates the #VALUE! error
    '*[/COLOR]
    If Err.Number <> 0 Then                         [COLOR="Green"]'* Adjust as per the type of error, if need be[/COLOR]
        blNewIncrement = True
    Else
[COLOR="Green"]        '*
        '* Remaining code goes here, if any
        '*[/COLOR]
    End If
    On Error GoTo 0
Loop Until Abs(NRF) <= Accuracy Or i = MaxIter[/COLOR][/SIZE][/FONT]

 

[K-MAN69]

Thanks for your reply

I went with the following code which worked but it the code is very labouring Excel and it crashed when I ran the UDF in 20 odd cells.

Is there anything obvious that needs tweeking?

I also wrote a few questions in the code and included a test for your immediate window in needed.

I have color coded the important parts:

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]
Dim blNewIncrement As Boolean
Dim dVolAssumption As Double
Vol = 0.1
Accuracy = 0.00000000001
MaxIter = 100
i = 1

Do

If blNewIncrement Then
dVolAssumption = dVolAssumption + 0.1 '* Adjust as needed
Vol = dVolAssumption '* Adjust as needed
Accuracy = 0.00000000001 'Does this reall need to be in the code twice?
MaxIter = 100 'Does this reall need to be in the code twice?
i = 1 'Does this reall need to be in the code twice?
blNewIncrement = False
End If

On Error Resume Next 'This line tells VBA to go to the next line if the code below returns an error

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

If Err.Number <> 0 Then 'This line makes sure the error in the above code is a #VALUE! error and not some other Run Time Error that will have a number
blNewIncrement = True
End If
On Error GoTo 0
Loop Until Abs(NRF) <= Accuracy Or i = MaxIter
AA_ImpliedVol_FutOpt = Vol
'?AA_ImpliedVol_FutOpt(#15/06/2012#, #18/04/2012#, 90.02, 60, 0.01, 0.01, "P")
End Function

 

[Mohammad Basem]

Please find hereunder are answers to your questions:

I)

Accuracy = 0.00000000001 'Does this reall need to be in the code twice?
MaxIter = 100 'Does this reall need to be in the code twice?

If you have no intention of fine tuning these two variables while changing the initial value dVolAssumption then you can delete both statements.

II)

i = 1 'Does this reall need to be in the code twice?

Yes, since one of the iteration conditions you are testing is the number of iterations MaxIter. Therefore you have to reset this variable each time you change dVolAssumption.

III)

On Error Resume Next 'This line tells VBA to go to the next line if the code below returns an error

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

If Err.Number <> 0 Then 'This line makes sure the error in the above code is a #VALUE! error and not some other Run Time Error that will have a number
blNewIncrement = True
End If
On Error GoTo 0

Here you have to investigate which statement generates the error. On Error Resume Next works exactly as mentioned in your comment and since each statement depends on the value returned from the previous statement(s), therefore, the IF Err.Number statement should be next to the statement that generates the error.

Here is an example, Assume that the statement NRF = (Value_1 - OptPrice) / Vega_1 generates the error as Vega_1 evaluates to zero.

in this case the above code shall be

[FONT="Consolas"][SIZE="2"][COLOR="Navy"]Value_1 = OptionPriceBlack76(Expiry, DealDate, Spot, Strike, RFR, Vol, OptType)
Vega_1 = Vega_Black76(Expiry, DealDate, Spot, Strike, Vol, RFR, OptType)
On Error Resume Next
NRF = (Value_1 - OptPrice) / Vega_1
If Err.Number <> 0 Then 
    blNewIncrement = True
Else
    Vol = Vol - NRF
    i = i + 1
End If
On Error GoTo 0[/COLOR][/SIZE][/FONT]

Hope this is clear.

 

[K-MAN69]

Yep clear - thanks so much for your ongoing help.

 

[Mohammad Basem]

You are most welcome, glad I could help.