PDA

View Full Version : #Value error msg in VBA code



abnf354
07-05-2014, 06:58 PM
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

Paul_Hossler
07-05-2014, 08:08 PM
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







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

snb
07-06-2014, 03:00 AM
You don't need any variable at all, let alone any declaration:


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

snb
07-06-2014, 05:19 AM
crosspost

http://www.ozgrid.com/forum/showthread.php?t=189474&p=718761#post718761

westconn1
07-06-2014, 05:37 AM
this is also posted as
http://www.vbforums.com/showthread.php?770005-VBA-Value-coding-error

Zack Barresse
07-06-2014, 10:36 AM
Please read this post on cross-posting: http://www.excelguru.ca/content.php?184-A-message-to-forum-cross-posters

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