ramohse

09-04-2013, 01:15 PM

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.

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.