NORMSDIST() Visual Basic Function
The option pricing model we will use
in this project is the famous "Black-Scholes" model.
Before we get to Black-Scholes however,
we need to make a function that calculates something called the "standard
normal CUMULATIVE distribution".
This function is not available ready-made
in Visual Basic. It is available in spreadsheets such as Excel, where
it is named "NORMSDIST", so we will use that name for our custom function
The actual function is quite complicated
and involves calculus, which would be difficult to program. You can
read about it on wikipedia if you are interested. However, Lawrence
G. McMillan, in his book "Options as a Strategic Investment", gives
an approximation method of obtaining NORMSDIST(). We have translated
that method into Visual Basic.
In testing the approximation method
against the NORMSDIST found in Excel, the results are usually within
.00001 of each other. Since option prices are quoted in cents, or to
an accuracy of .01, this is not a problem. If you are using this NORMSDIST()
function for some reason besides calculating option prices, you will
need to decide for yourself if the level of accuracy is good enough.
To add NORMSDIST() to your project,
open your project. Right-click on the name of your project, which should
be "Option Calculator", in the Solutions Explorer on the right of your
screen, and choose "Add".
(If you cannot see a window called "Solution Explorer" on the right
side of your screen, open the "View" menu, select "Other Windows", and
then select "Solution Explorer".)
From the "Add" menu, select "Module",
give the Module the name "OptionCalcs.vb", and press the "Add"
A module provides a place to store and
organize code, so your main form "code behind" is not cluttered up with
bits of code that will rarely need to be changed.
You should now see the module, with
the words "Module OptionCalcs" and "End Module" already on it. Copy
the code below and paste it to a blank line between "Module OptionCalcs"
and "End Module".
Function NORMSDIST(ByVal x As Single) As Single
'returns the "standard normal CUMULATIVE distribution"
Dim result As Single
Dim y As Single = 1 / (1 + (0.2316419 * Math.Abs(x)))
Dim z As Single = 0.3989423 * (Math.Exp((-x ^ 2) / 2))
result = 1 - z * ((1.33027 * (y ^ 5)) - (1.821256 * (y ^ 4)) +
(1.781478 * (y ^ 3)) - (0.356538 * (y ^ 2)) + (0.3193815 * y))
If x > 0 Then
Return 1 - result
You now have your first function, ready
to go to work. The Black-Scholes functions will now know what NORMSDIST()
means when that name is referenced in code.
NORMSDIST() is not used directly from
your form, but if you wish to test it and/or compare results with the
same formula in a spreadsheet, you can do this:
In the Immediate Window, below the OptionCalcs
module, type ?NORMSDIST(.2)
(If you cannot see a window called "Immediate Window" on the bottom
of your screen, press "CTRL-G" to open it.)
Typing ?NORMSDIST(.2) and pressing Enter
is like asking "what is the NORMSDIST of .2?"
You should get the answer 0.5792518
Checking this answer against the results
from NORMSDIST in a spreadsheet, we found that they are .0000079 apart.
Good enough for an option price!
(If the Immediate Window gives an error message instead of a value, see
the first question in the FAQs.)
Save your Project.