xlf QandA series
Function arguments and random number series
QUESTION
WHY DOES MY MONTE CARLO BASED OPTION PRICING FUNCTION SHOW TWO DIFFERENT VALUES IN THE FUNCTION ARGUMENTS DIALOG BOX? (Figure 1)

Answer
The Monte Carlo option pricing procedure uses a random number generator (RNG) to simulate random drawing from the standard normal distribution. This is the epsilon $\varepsilon$ value in the geometric Brownian motion equation for the change in the stock price $\Delta S=\mu S \Delta t+\sigma S \varepsilon \sqrt{\Delta t}$ with rate of return $\mu$ and volatility $\sigma$.
In VBA, $\varepsilon$ can be generated by the expression Application.NormSInv(Rnd()).. Written as Application.Norm_S_Inv(Rnd()) in Excel 2016. In a new session of Excel, ie when Excel has just been opened, it is well known that the VBA RNG always returns the same sequence of random numbers. This feature is useful in answering this question. The code 1 procedure generates the first 10 values of $\varepsilon$.
Code 1: Sub GenEpsilon returns a column vector of 10 $\varepsilon$ variates from the $N(0,1)$ distribution
Sub GenEpsilon() Dim i As Long With ActiveCell .Value = "Iteration" .Offset(0, 1) = "Random No" For i = 1 To 10 .Offset(i, 0) = i .Offset(i, 1) = Application.NormSInv(Rnd()) Next i End With End Sub
With output to Excel as shown in figure 2

Now consider the function procedure in code 2. This procedure is designed to pass the random number sequence in figure 2 through the function arguments dialog box.
Code 2: A function to generate $\varepsilon$ variates
Function TestEpsilon(Num As Double) TestEpsilon = Application.NormSInv(Rnd()) * Num End Function
When the TestEpsilon function in code 2 is called in a new session of Excel, it displays the arguments shown in figure 3.

Thus, the two numbers in figure 3 are the return values from two consecutive iterations of the RNG. There is an additional iteration between the function return value number and the "Formula result" value. When the OK button is clicked, however, the third iteration, 0.20066214 is returned to cell A1 (figure 4).

Return now to the original Monte Carlo option pricer procedure in figure 1. Suppose that the calling cell has a currency format with 4 decimal places.

Figure 5 shows that the top value is unformatted 6.71606072, whilst the "Formula result" value inherits the formatting and value from the calling cell $6.8104, and in the case of a Monte Carlo based option pricer, the result value updates again when the OK button is clicked.
- This example was developed in Excel 2013 Pro 64 bit.
- Published: 5th June 2014
- Revised: Saturday 23rd of June 2018 - 02:10 AM, Pacific Time (PT)