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.