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

 

[Firefly2012]

One thing that immediately springs to mind is that you have used d1 and d2 befor thene you have calculated them

 

[K-MAN69]

Thanks for your post, I recoded it like below but it didn't help - is that what you meant?

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 TimeYears As Double
Dim d1 As Double
Dim d2 As Double
Dim ert As Double
TimeYears = (Expiry - DealDate + 1) / 360
d1 = (Worksheet.Function.Ln(Spot / Strike) + Vol ^ 2 * TimeYears * 0.2) / (Vol * Worksheet.Function.Sqrt(TimeYears))
d2 = d1 - Vol * Worksheet.Function.Sqrt(TimeYears)
ert = Worksheet.Function.Exp(-RFR * TimeYears)
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
End Function

 

[Firefly2012]

I'm on a mobile phone at the moment so not in a position to test but that was what I meant

What do you mean by doesn't work? Errors our returns wrong result?

If you search for black scholes under my user name you should find a function I wrote a month ago for calculating price of a call with no dividends -may give you some ideas

 

[K-MAN69]

Thanks a million

I checked out your code of BS and it was relevant except that I am looking to have the Time input as date variables and I think this is where my code is getting stuck....I am getting "Compile Error: ByRef argument type mismatch" when I put ?AA_Black76_FutOpt(27/06/2012,05/03/2012,100,107,0.25,0.01,P,C) in the immediate window. I think I am not doing the right thing with the dates somehow.
I have changed my code to:
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 * Application.Sqrt(TimeYears))
d2 = d1 - Vol * Application.Sqrt(TimeYears)
ert = Application.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
Also I am confused as to what to what to write when using a Excel Defined function. Is it Application.NormSDist(x)
or is it Worksheet.Function.NormSDist(x)
I noticed that you used the former but I have seen other people use the later what is the difference?

 

[Firefly2012]

Given your declaration, I believe you will want to write it like:
?AA_Black76_FutOpt(#06/27/2012#,#03/05/2012#,100,107,0.25,0.01,"P","C")

Also I am confused as to what to what to write when using a Excel Defined function. Is it Application.NormSDist(x)
or is it Worksheet.Function.NormSDist(x)
I noticed that you used the former but I have seen other people use the later what is the difference?

Practically speaking, there is next to no difference - you can use either perfectly happily.

 

[K-MAN69]

Thanks that helps I used
?AA_Black76_FutOpt(#27/06/2012,#05/03/2012,100,107,0.25,0.01,"P","C")
and got "Expected expression error"

 

[Firefly2012]

Did you try copying and pasting in to the Immediate Window exactly what I wrote in post #6?

If so, what was the result?

 

[K-MAN69]

sorry my bad - I ran it exactly this time and got dramas on the higlighted line of code.

It says run time error 438 - object doesn't support this property or method.

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 * Application.Sqrt(TimeYears))
d2 = d1 - Vol * Application.Sqrt(TimeYears)
ert = Application.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

 

[Firefly2012]

That's this bit:

Application.Sqrt(TimeYears)

Change this to:

TimeYears^0.5

and try re-running