UDF Black 76 Option Pricing model

[K-MAN69]

Can anyone tell me what I have done wrong with my code below - cant seem to get it to work?

Function AA_Black76_FutOpt(Calc As String, OptType As String, Spot As Double, Strike As Double, Expiry As Double, DealDate As Double, RFR As Double, Vol As Double)
Dim T As Double
Dim d1 As Double
Dim d2 As Double
Dim ert As Double
If Worksheet.Function.And(Calc = "P", OptType = "C") Then
AA_Black76_FutOpt = ert * (Spot * Worksheet.Function.NormSDist(d1) - Strike * Worksheet.Function.NormSDist(d2))
Else
AA_Black76_FutOpt = ert * (Strike * Worksheet.Function.NormSDist(-d2) - Spot * Worksheet.Function.NormSDist(-d1))
End If

T = (Expiry - DealDate + 1) / 360
d1 = (Worksheet.Function.Ln(Spot / Strike) + Vol ^ 2 * T * 0.2) / (Vol * Worksheet.Function.Sqrt(T))
d2 = d1 - Vol * Worksheet.Function.Sqrt(T)
ert = Worksheet.Function.Exp(-RFR * T)

End Function

 

[K-MAN69]

Thank U! Breakthrough!

I also had Application.Exp wrong too. Now my formula reads:

Function AA_Black76_FutOpt(Expiry As Date, DealDate As Date, Spot As Double, Strike As Double, Vol As Double, RFR As Double, Calc As String, OptType As String)
Dim TimeYears As Double
Dim d1 As Double
Dim d2 As Double
Dim ert As Double
TimeYears = (Expiry - DealDate + 1) / 360
d1 = (Application.Ln(Spot / Strike) + Vol ^ 2 * TimeYears * 0.2) / (Vol * TimeYears ^ 0.5)
d2 = d1 - Vol * TimeYears ^ 0.5
ert = Exp(-RFR * TimeYears)
If Application.And(Calc = "P", OptType = "C") Then
AA_Black76_FutOpt = ert * (Spot * Application.NormSDist(d1) - Strike * Application.NormSDist(d2))
Else
AA_Black76_FutOpt = ert * (Strike * Application.NormSDist(-d2) - Spot * Application.NormSDist(-d1))
End If
End Function

I will add in the other Greeks to improve it and post it up once I have it finished

Thanks again.

 

[K-MAN69]

ok Done! I have now added the other greeks and it works well.

Function AA_Black76_FutOpt(Expiry As Date, DealDate As Date, Spot As Double, Strike As Double, Vol As Double, RFR As Double, Calc As String, 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 * Application.Pi) ^ 0.5)) * Exp((-d1 ^ 2) * 0.5)
'Price
If Application.And(Calc = "P", OptType = "C") Then
AA_Black76_FutOpt = ert * (Spot * Application.NormSDist(d1) - Strike * Application.NormSDist(d2))
ElseIf Application.And(Calc = "P", OptType = "P") Then
AA_Black76_FutOpt = ert * (Strike * Application.NormSDist(-d2) - Spot * Application.NormSDist(-d1))
'Delta
ElseIf Application.And(Calc = "D", OptType = "C") Then
AA_Black76_FutOpt = ert * Application.NormSDist(d1)
ElseIf Application.And(Calc = "D", OptType = "P") Then
AA_Black76_FutOpt = ert * (Application.NormSDist(d1) - 1)

'Gamma
ElseIf Calc = "G" Then
AA_Black76_FutOpt = (ert * N_Dash_d1) / (Spot * Vol * TimeYears ^ 0.5)

'Vega
ElseIf Calc = "V" Then
AA_Black76_FutOpt = (Spot * ert * N_Dash_d1 * TimeYears ^ 0.5) / 100
'Theta
ElseIf Application.And(Calc = "T", OptType = "C") Then
AA_Black76_FutOpt = (-RFR * Spot * ert * Application.NormSDist(d1) + RFR * Strike * ert * Application.NormSDist(d2) + Spot * ert * Vol * N_Dash_d1 / (2 * (TimeYears ^ 0.5))) / -365
ElseIf Application.And(Calc = "T", OptType = "P") Then
AA_Black76_FutOpt = (RFR * Spot * ert * Application.NormSDist(-d1) - RFR * Strike * ert * Application.NormSDist(-d2) + Spot * ert * Vol * N_Dash_d1 / (2 * (TimeYears ^ 0.5))) / -365
'Rho
ElseIf Application.And(Calc = "R", OptType = "C") Then
AA_Black76_FutOpt = (Strike * TimeYears * ert * Application.NormSDist(d2)) / 100
ElseIf Application.And(Calc = "R", OptType = "P") Then
AA_Black76_FutOpt = (-Strike * TimeYears * ert * Application.NormSDist(-d2)) / 100

End If
End Function

 

[shg]

You could make a little more use of native VBA functions and flow control:

Function AA_Black76_FutOpt(Expiry As Date, DealDate As Date, Spot As Double, _
                           Strike As Double, Vol As Double, RFR As Double, Calc As String, OptType As String)
    Const pi As Double = 3.14159265358979
    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 = (Log(Spot / Strike) + Vol ^ 2 * Sqr(TimeYears)) / (Vol * Sqr(TimeYears))
    d2 = d1 - Vol * Sqr(TimeYears)
    ert = Exp(-RFR * TimeYears)
    N_Dash_d1 = Exp((-d1 ^ 2) / 2) / (Sqr(2 * pi))
 
    If Calc = "G" Then
        AA_Black76_FutOpt = (ert * N_Dash_d1) / (Spot * Vol * Sqr(TimeYears))
    
    ElseIf Calc = "V" Then
        AA_Black76_FutOpt = (Spot * ert * N_Dash_d1 * Sqr(TimeYears)) / 100
    
    Else
        Select Case Calc & OptType
            Case "PC"
                AA_Black76_FutOpt = ert * (Spot * Application.NormSDist(d1) - Strike * Application.NormSDist(d2))
            Case "PP"
                AA_Black76_FutOpt = ert * (Strike * Application.NormSDist(-d2) - Spot * Application.NormSDist(-d1))
                'Delta
            Case "DC"
                AA_Black76_FutOpt = ert * Application.NormSDist(d1)
            Case "DP"
                AA_Black76_FutOpt = ert * (Application.NormSDist(d1) - 1)
            Case "TC"
                AA_Black76_FutOpt = (-RFR * Spot * ert * Application.NormSDist(d1) + RFR * Strike * ert * Application.NormSDist(d2) + Spot * ert * Vol * N_Dash_d1 / (2 * (Sqr(TimeYears)))) / -365
            Case "TP"
                AA_Black76_FutOpt = (RFR * Spot * ert * Application.NormSDist(-d1) - RFR * Strike * ert * Application.NormSDist(-d2) + Spot * ert * Vol * N_Dash_d1 / (2 * (Sqr(TimeYears)))) / -365
            Case "RC"
                AA_Black76_FutOpt = (Strike * TimeYears * ert * Application.NormSDist(d2)) / 100
            Case "RP"
                AA_Black76_FutOpt = (-Strike * TimeYears * ert * Application.NormSDist(-d2)) / 100
        End Select
    End If
End Function