Using NORMSDIST() Function in VBA

[glade]

My entire current function for my Black Scholes Calling Price Model is



Function BlackScholesCallOption(Stock As Double, Exercise As Double, Time As Double, Interest As Double, Sigma As Double)

Dim a As Single
Dim b As Single
Dim c As Single
Dim d1 As Single
Dim d2 As Single

a = Log(Stock / Exercise)
b = (Interest + 0.5 * Sigma ^ 2) * Time
c = Sigma * (Time ^ 0.5)
d1 = (a + b) / c
d2 = d1 - Sigma * (Time ^ 0.5)
BlackScholesCallOption = Stock * NormSDist(d1) - (Exercise * Exp(-Interest * Time)) * NormSDist(d2)

End Function

The only problem is that my NormSDist functions that i use in the second last line is not defined. I need help defining it so that it pulls from the excel function normsdist().

Thanks in Advance.

[mdmackillop]

I know nothing about such formulae, but NormDist appears to take 4 arguments, you only have 1.
To use a worksheet function in VBA, you would use the qualification

Application.WorksheetFunction.NormDist(a,b,c,d)
or
Application.NormDist(a,b,c,d)

[Bob Phillips]

He is using NormSDist Malcolm, not NormDist.

Just use



Function BlackScholesCallOption(Stock As Double, Exercise As Double, Time As Double, Interest As Double, Sigma As Double)

Dim a As Single
Dim b As Single
Dim c As Single
Dim d1 As Single
Dim d2 As Single

a = Log(Stock / Exercise)
b = (Interest + 0.5 * Sigma ^ 2) * Time
c = Sigma * (Time ^ 0.5)
d1 = (a + b) / c
d2 = d1 - Sigma * (Time ^ 0.5)
BlackScholesCallOption = Stock * Application.NormSDist(d1) - (Exercise * Exp(-Interest * Time)) _
* Application.NormSDist(d2)

End Function

[mdmackillop]

I should wear my specs! Thanks Bob.

[Bob Phillips]

I'm wearing mine :)