VBA Black Scholes w/Greeks and Conditional Formula

[ramohse]

Hello all,

Firstly, thank you all very much for your time and assistance. I am new to VBA, and am currently working through a book on applied VBA for financial modeling. I am trying to take the code from the book and adapt it to my needs/wants, often to no avail (it is also an old book). Currently I am working on making individual functions to calculate the Black Scholes model and greeks, to ultimately integrate those functions into one "BSGreek" conditional function (1 = delta, 2 = gamma, etc.) My "BSValue," "NDOne," "NDTwo," "BSDOne," and "BSDTwo" functions all work, but the two at the bottom, "BSNdashDOne" and "BSDelta" both yield #VALUE! errors. The code:


Option Explicit

Function BSValue(iopt, S, K, sigma, r, tyr, Optional q As Integer = 0)
'returns Black Scholes option value
'iopt = 1 for call, -1 for put, q = dividend yield

Dim ert, eqt, NDOne, NDTwo
'This declares the variables for the function

ert = Exp(-r * tyr)
'This finds the continuous compounding risk-free rate

eqt = Exp(-q * tyr)
'See above, applied to dividend yield

NDOne = Application.NormSDist(iopt * BSDOne(S, K, sigma, r, tyr))
NDTwo = Application.NormSDist(iopt * BSDTwo(S, K, sigma, r, tyr))
'These calculate the d1 and d2 values

BSValue = iopt * (S * eqt * NDOne - K * ert * NDTwo)
'This tells the program what exactly to do for the function

End Function


Function BSDOne(S, K, sigma, r, tyr, Optional q As Integer = 0)
'Returns the Black Scholes d1 value

BSDOne = (Log(S / K) + (r - q + 0.5 * sigma ^ 2) * tyr) / (sigma * Sqr(tyr))
End Function


Function BSDTwo(S, K, sigma, r, tyr, Optional q As Integer = 0)
'Returns the Black Scholes d2 value

BSDTwo = (Log(S / K) + (r - q - 0.5 * sigma ^ 2) * tyr) / (sigma * Sqr(tyr))
End Function


Function BSNdashDOne(iopt, S, K, sigma, r, tyr, Optional q As Integer = 0)
'Returns the N'(d1) value

BSNdashDOne = Application.NormDist(Application.NormSDist(iopt * (Log(S / K) + (r - q + 0.5 * sigma ^ 2) * tyr) / (sigma * Sqr(tyr)), 0, 1, False))
End Function


Function BSDelta(iopt, S, K, sigma, r, tyr, Optional q As Integer = 0)

BSDelta = Application.NormSDist(BSDOne(S, K, sigma, r, tyr, [q as integer = 0]))

End Function



It is likely something incredibly simple and obvious, and for that I apologize. My working theory is that it has something to do with the NormDist/NormSDist functions. But again I thank you for any assistance you can provide.

[SamT]

I have no idea what the effect of NormDist(NormSDist) is.


This [q As Integer = 0] should read Evaluate(q = 0) or [q=0] and will return "True" if q = 0, else returns "False"

[ZVI]

This [q As Integer = 0] should read Evaluate(q = 0) or [q=0] and will return "True" if q = 0, else returns "False"
It's the correct initiating of the Optional argument of the function ;)
You can find the same usage in VBA Help section Function Statement Example.
Actually, the integer variable is already initiated by zero, thus Optional q As Integer = 0 can be shortened to Optional q As Integer , but it is the matter of personal styles & preferences.

[SamT]

Sorry, I wasn't clear. In the declaration, that construction will work. although you don't need the square brackets. In the help file, square brackets used like that indicate that the particular parameter is optional. In VBA Code, the square brackets are shorthand for the Evaluate Function.

I was indicating a problem in this line of code
BSDelta = Application.NormSDist(BSDOne(S, K, sigma, r, tyr, [q As Integer = 0]))

The BSDOne function needs q to be an Integer. In the line above,I have no idea what the value and type of the sixth parameter is going to evaluate as. In effect, when BSDOne is called by BSDelta, the formula in BSDOne is
BSDOne = (Log(S / K) + (r - [q As Integer = 0] + 0.5 * sigma ^ 2) * tyr) / (sigma * Sqr(tyr))




BTW, Since BSDelta is merely calling BSDOne 's formula you can save the CPU and Memory cost of a Call by placing the actual formula in BSDelta
Function BSDelta(iopt, S, K, sigma, r, tyr, Optional q As Integer = 0)

BSDelta = Application.NormSDist((Log(S / K) + (r - q + 0.5 * sigma ^ 2) * tyr) / (sigma * Sqr(tyr)))

End Function

[ZVI]

Agh! Thank you SamT, I see now what you meant – have sense!

[ramohse]

That fixed it. Thank you all very much for your help!