The 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 as well.

     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" button.

      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 result Else Return 1 - result End If End Function

     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.