#Value error msg in VBA code

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!

---------

Code:

---

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


---

BS is not my field, but some suggestions for code

1. Explictly Dim each variable -- otherwise it's a Variant

2. I guessing that either you wanted NormSDist which takes one parm, or there were 3 missing if you use NormDist. Help has a good writeup on both

3. I tend to be on the wordy side (I tell myself it's self documenting code) so I always find meaningful variable names easier to figure out 5 or 6 months later

'Function BSMValue(SharePrice As Double, ExercisePrice As Double, InterestRate As Double, _
' DividentYield As Double, OptionLife As Double, Volatility As Double, GeneratePut As Boolean) As Double

Code:

---

Option Explicit
Function BSMValue(S As Double, K As Double, r As Double, q As Double, T As Double, sigma As Double, i As Boolean) As Double
 
Dim ert  As Double, eqt  As Double
Dim DOne  As Double, DTwo  As Double, NDone  As Double, NDtwo  As Double
 
ert = Exp(-q * T)
eqt = Exp(-r * T)
 
If i Then
    ' 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.NormSDist(DOne)
    NDtwo = Application.NormSDist(DTwo)
   
    BSMValue = S * ert * NDone - K * eqt * NDtwo
 
Else
    '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.NormSDist(DOne)
    NDtwo = Application.NormSDist(DTwo)
   
    BSMValue = -S * ert * NDone + K * eqt * NDtwo
End If
 
End Function

---

Hope this helps

You don't need any variable at all, let alone any declaration:

Code:

---

Function BSMValue(S, K, r, q, T, sigma, i As Boolean)
    BSMValue = -S * Exp(-q * T) * Application.NormDist(i * (Log(S / K) + (r - q - 0.5 * sigma ^ 2) * T) / (sigma * Sqr(T))) + K * Exp(-r * T) * Application.NormDist(i * (Log(S / K) + (r - q - 0.5 * sigma ^ 2) * T) / (sigma * Sqr(T)))
End Function

---

NB a boolean is true or false; it doesn't convert automatically to -1 or 0

crosspost

http://www.ozgrid.com/forum/showthre...761#post718761

this is also posted as
http://www.vbforums.com/showthread.p...e-coding-error

Please read this post on cross-posting: http://www.excelguru.ca/content.php?...-cross-posters

We appreciate your compliance with forum netiquette and not cross-posting anymore without providing the appropriate links.