Hi all,

I'm a bit new to VBA and i've written a code for creating a function which calculates call and put prices using black-scholes model.

The problem is that after I input the variables (share price - S, exercise price - K, interest rate - r, dividend yield - q, option life - T, volatility - sigma, call- 1 / put - 0, I get #Value error instead of the call and put prices which is what this code is supposed to generate.

Could anyone tell me where I have made a mistake, please?

Thanks!

---------
Function BSMValue(S, K, r, q, T, sigma, i As Boolean)

Dim ert, eqt
Dim DOne, DTwo, NDone, NDtwo

ert = Exp(-q * T)
eqt = Exp(-r * T)

Select Case i

Case 1 ' call option
DOne = (Log(S / K) + (r - q + 0.5 * sigma ^ 2) * T) / (sigma * Sqr(T))
DTwo = (Log(S / K) + (r - q - 0.5 * sigma ^ 2) * T) / (sigma * Sqr(T))

NDone = Application.NormDist(DOne)
NDtwo = Application.NormDist(DTwo)

BSMValue = S * ert * NDone - K * eqt * NDtwo

Case 0 'put option
DOne = -(Log(S / K) + (r - q + 0.5 * sigma ^ 2) * T) / (sigma * Sqr(T))
DTwo = -(Log(S / K) + (r - q - 0.5 * sigma ^ 2) * T) / (sigma * Sqr(T))

NDone = Application.NormDist(DOne)
NDtwo = Application.NormDist(DTwo)

BSMValue = -S * ert * NDone + K * eqt * NDtwo
End Select

End Function