I have written the following functions and when I try to evalute it I get #Value in excel. For a test you can use S = 50, K = 60, T = 0.25, R = 0.05, V = 0.3, B = 0.01
I started having trouble when I tried to use the excel function NORM.S.DIST()
Function BSPriceC(S, K, T, R, V, B)
Dim d1 As Double
Dim d2 As Double
d1 = d_1(S, K, T, R, V, B)
d2 = d1 - V * Sqr(T)
BSPriceC = Application.NORM.S.DIST(d1, True) * S - Application.NORM.S.DIST(d2, True) * K * Exp(-R * T)
End Function
Function d_1(S, K, T, R, V, B)
d_1 = (1 / (V * Sqr(T)) * (Log(S / K) + (B + (V ^ 2) / 2) * T))
End Function
Function d_2(S, K, T, R, V, B)
d_2 = d_1(S, K, T, R, V, B) - V * Sqr(T)
End Function
Bookmarks