NtRand (the “Software”) is a free software and no user’s fee or license fee is charged. So do not worry, you won’t be asked to pay anything nor get caught just by using it under the License Agreement.

Numerical Technologies Incorporated is an IT Software vendor which supports risk management system in major Japanese Financial Institutions and is located at Marunouchi in Tokyo, Japan.

Numerical Technologies Pte. Ltd. is its sister company located in Singapore.

Sometimes depends on your PC environment, you cannot open Help File (chm file). It is due to Windows security restrictions. Please try following methods.

• Method 1
  • 1. Double-click the .chm file
  • 2. In the Open File-Security Warning dialog box, click to clear the Always ask before opening this file check box
  • 3. Click Open
• Method 2
  • 1. Right-click the CHM file, and then click Properties
  • 2. Click Unblock
  • 3. Double-click the .chm file to open the file

Reference: Microsoft Support

The most serious headache of Monte Carlo users may be its complexity. The theoretical performance (computing error) of the crude Monte Carlo is represented by the expression below assuming the count of simulation is n:

Here are typical two of the approaches for improving the performance of crude Monte Carlo:

• Quasi-random number Monte Carlo – Use a regular numerical sequence instead of pseudo-random numbers, to approximate more preferable probability distribution.
• Moment matching – Operate pseudo-random numbers to be generated with statistical method, to approximate more preferable probability distribution.

Whichever approach you should choose is dependent on your purpose of using Monte Carlo, for it is not always concluded which approach is superior to the other. Pros and cons of each approach are explained below:

• Quasi-random number Monte Carlo
  Works best for specific purposes, such as evaluating the expected value or the variance of a single variant or of multiple variants under low dimensions. For instance, in the area of option pricing with a single variant, this approach greatly improves the accuracy of calculation with fewer times of repeating operations. The quasi-random number Monte Carlo made a boom among major players in financial communities in the late 1980s and early 1990s. Today the boom is almost gone because the moment matching with more times of operations has solved almost all problems thanks to the current improvement in computer performance. The disadvantage, on the other hand, is that this approach is not the solution for computing percentile numbers (e.g., VaR calculation) because of the significant degradation of performance in a higher dimension and the behavioral problem of quasi-random numbers on the tail of the distribution.
  Denoting the count of simulation by n, the theoretical performance (computing error) of the quasi-random number Monte Carlo relies on the dimensional number d ( as expressed in the following formula), which means the more the count of variant increases, the more the performance of the quasi-random number Monte Carlo deteriorates than that of the crude Monte Carlo.
  
• Moment matching
  Versatile for almost all purposes. There is no other powerful alternatives than this approach to maintain a stable performance in the higher dimension or on the tail of the distribution (e.g., percentile number calculation). Regardless of the great improvement in performance on the aspect, the moment matching approach also has the same theoretical performance limit as the crude Monte Carlo. Therefore its performance is poor in the single variant environment, even poorer than the quasi-random number Monte Carlo’s. In the financial community, the moment matching approach has become widely used as a standard in the higher order Monte Carlo simulation since the middle of the 1990s when the quantification of risk [i.e. value-at-risk (VaR)] made a presence as a significant subject. What made the moment matching so popular? Not only its user-friendliness and good predictability (perspective) but also the progress in computer performance as the infrastructure have greatly contributed to its popularity. Most people may think that “Quasi-random number Monte Carlo ended its role because now we have powerful computers capable of finishing 10,000-time or more simulation in less than one second.” This idea seems persuasive in a sense. But if you have to calculate a special option price for which large numbers of simulations are required as a very heavy burden of computing the quasi-random number Monte Carlo rather than the moment matching should be applied.

NtRand is capable of using two moment matching methods of antithetic variant method and quadratic resampling either separately or simultaneously. Especially the simultaneous operation brings a remarkably effective result, and thus this operation was a sort of company secret in the financial community (e.g., among the MBS/ABS derivative players) in the 1980s. Today, almost in the end of 1990s, this technology is out of the closet while many theses are released one after another regarding this subject.

Leave precise arguments to the papers by experts. Let us run four types of 100-count 3-variate Monte Carlo simulation (n=100) to assess the effects of the individual operation and simultaneous operations of antithetic variant method and quadratic resampling. The comparison tables indicate the first moment (mean), second moment (standard deviation or variance), third moment (skewness), fourth moment (kurtosis), and correlation coefficients of obtained random numbers.

• Input data
  Suppose that we generate drifting multivariate normal random numbers having following statistics. It is assessed that the more proximate the result comes to these values, the more effective the simulation case is.

  1 2 3 4 5 6 7 8 9 10

  A B C D Data Data Data Description 1st.series 2nd.series 3rd.series Description 0.01 0.02 0.03 means 0.29833 0.24083 0.21213 stdevs 0 0 0 skewness 0 0 0 kurtosis Data Data Data Description 1 0.54843 0.50011 crrel.matrix 0.54843 1 0.68817 crrel.matrix 0.50011 0.68817 1 crrel.matrix

• Case 1: Crude Monte Carlo – with no use of moment matching
  In only a hundred times of trial computing, the obtained statistics from generated multivariate random numbers are substantially far from those of the input data.

  1 2 3 4 5 6 7 8 9 10

  A B C D Data Data Data Description 1st.series 2nd.series 3rd.series Description 0.05210 0.02710 0.03757 means 0.29159 0.22906 0.19863 stdevs -0.06096 0.39699 0.41137 skewness -0.30666 0.17643 0.55905 kurtosis Data Data Data Description 1 0.47414 0.45856 crrel.matrix 0.47414 1 0.71127 crrel.matrix 0.45856 0.71127 1 crrel.matrix

• Case 2: Only with antithetic variant method (odd-order moment matching)
  Mean and skewness which make the odd-order moment are adjusted so that they absolutely match the input data. Higher odd-order moments will be zeroed (not listed below).

  1 2 3 4 5 6 7 8 9 10

  A B C D Data Data Data Description 1st.series 2nd.series 3rd.series Description 0.01000 0.02000 0.03000 means 0.28964 0.23389 0.21311 stdevs 0 0 0 skewness -0.13148 -0.21078 -0.04597 kurtosis Data Data Data Description 1 0.58045 0.52405 crrel.matrix 0.58045 1 0.74021 crrel.matrix 0.52405 0.74021 1 crrel.matrix

• Case 3: Only with quadratic resampling (1st- and 2nd-moment matching)
  Mean, standard deviation and correlation coefficient are adjusted so that they absolutely match the input data. Note that a slight deviation may occur in case of very high dimension restricted by the error limit (generated in the process of inverse matrix computing and triangular decomposition) caused by representation accuracy of the computer.

  1 2 3 4 5 6 7 8 9 10

  A B C D Data Data Data Description 1st.series 2nd.series 3rd.series Description 0.01000 0.02000 0.03000 means 0.29833 0.24083 0.21213 stdevs 0.00470 0.40025 0.57564 skewness 0.43710 1.71197 0.72973 kurtosis Data Data Data Description 1 0.54843 0.50011 crrel.matrix 0.54843 1 0.68817 crrel.matrix 0.50011 0.68817 1 crrel.matrix

• Case 4: Simultaneous use of antithetic variant method and quadratic resampling (moment matching with odd-order moment plus 2nd-moment)
  All values except for kurtosis match the input data. Note that a slight deviation may occur in case of very high dimension restricted by the error limit (generated in the process of inverse matrix computing and triangular decomposition) caused by representation accuracy of the computer.

  1 2 3 4 5 6 7 8 9 10

  A B C D Data Data Data Description 1st.series 2nd.series 3rd.series Description 0.01000 0.02000 0.03000 means 0.29833 0.24083 0.21213 stdevs 0 0 0 skewness -0.73001 1.19494 0.15166 kurtosis Data Data Data Description 1 0.54843 0.50011 crrel.matrix 0.54843 1 0.68817 crrel.matrix 0.50011 0.68817 1 crrel.matrix

  Following table is another result of the case 4, where the simulation was run by 10000 times (n=10000). The kurtosis shows an improvement.

  1 2 3 4 5 6 7 8 9 10

  A B C D Data Data Data Description 1st.series 2nd.series 3rd.series Description 0.01000 0.02000 0.03000 means 0.29833 0.24083 0.21213 stdevs 0.00470 0.40025 0.57564 skewness -0.03774 0.11285 0.00159 kurtosis Data Data Data Description 1 0.54843 0.50011 crrel.matrix 0.54843 1 0.68817 crrel.matrix 0.50011 0.68817 1 crrel.matrix

As discussed through above cases, the simultaneous use of antithetic variant method and quadratic resampling is likely to serve for drastic improvement in convergence just with a hundred-time Monte Carlo simulation as if it would give a complete solution. This must be right to a certain extent, but a wrong conclusion. People are often lured by this sort of logical development named “Type I error” in statistics. To interpret the this Type I error in the case 4, a quick improvement in convergence by moment matching is quite different a question from the one that this method surely converges asymptotically to the true solution. Regardless of the purposes for calculating option or VaR, certain times of trial computing should be necessary.

Sample sheets can be downloaded from Download page. After installing NtRand excel add-in, open the sample sheet to understand how to use NtRand functions.
Each sample sheet includes following functions.

• NtRand1.zip –
  (NTRAND, NTRANDBETA, NTBETAMOM, NTBETAPARAM, NTRANDGUMBEL, NTGUMBELMOM, NTGUMBELPARAM, NTRANDJOHNSONSU, NTJOHNSONSUMOM, NTJOHNSONSUPARAM, NTRANDLOGISTIC, NTLOGISTICMOM, NTLOGISTICPARAM, NTRANDLOGNORM, NTLOGNORMMOM, NTLOGNORMPARAM, NTRANDNORM, NTRANDPOISSON, NTPOISSONMOM, NTRANDTRIANGULAR, NTTRIANGULARMOM, NTTRIANGULARPARAM, NTRANDTRUNCNORM, NTTRUNCNORMMOM, NTTRUNCNORMPARAM, NTRANDWEIBULL, NTWEIBULLMOM, NTWEIBULLPARAM, NTRANDMULTINORM)
• NtRand2.zip –
  (NTBETANORMDIST, NTBETAPARAM, NTGUMBELDIST, NTGUMBELINV, NTGUMBELPARAM, NTJOHNSONSUDIST, NTJOHNSONSUINV, NTJOHNSONSUPARAM, NTLOGISTICDIST, NTLOGISTICINV, NTLOGISTICPARAM, NTLOGNORMDIST, NTLOGNORMINV, NTLOGNORMPARAM, NTNORMDIST, NTPOISSONDIST, NTTRIANGULARDIST, NTTRIANGULARINV, NTTRIANGULARPARAM, NTTRUNCNORMDIST, NTTRUNCNORMINV, NTTRUNCNORMPARAM, NTWEIBULLDIST, NTWEIBULLINV, NTWEIBULLPARAM)
• NtRand3.zip –
  (NTCOR2COV, NTCOV2COR, NTMULTICORREL, NTMULTICOVARP)

NtRand addin functions give stable number. This is by design. But you may want to update random number every time when you recalculate the worksheet. There is very simple way to achieve this. See following worksheet formula.

=NtRand(10000,0,RAND()*2147483647,RAND()*2147483647)

This is what you want. NtRand() function is called just once even if it returns multiple values, because it uses array formula. For this reason, RAND() function is never called 10000 or 20000 times by this solution.

You may want to call NtRand addin functions from VBA (Visual Basic for Application). See following example. This code fragment implements same NTRANDMULTINORM worksheet example which is the same as written above.

Public Sub Test1()
  Dim Result As Variant
  Result = Application.Run("NTRANDMULTINORM", 8,
Range(Cells(14, 3), Cells(16, 5)), Range(Cells(12, 3), Cells(12, 5)),
    0, 12345, 67890, True, True, True)
  '... now 'Result' stores 8 x 3 array of multi-dimensional normal
  '    random number sequence ...
  ' You can extract an individual value from Result by e.g. Result(5, 2)
  '... do what you want ...
End Sub

Remind that VBA is different from Visual Basic. The “Application.Run…” technique works with VBA only. VBA can convert Excel internal data type XLOPER inside. However, Visual Basic doesn’t have this feature. It is not simple matter to call internal Excel addin functions from Visual Basic.

NtRand 32bit supports Excel 97 – 2016 running on the Windows 95 or later operating system in any language version. NtRand 32bit is also designed to be operated in Excel 5.0 on 95/98, but the correct operation is not verified in this environment.
And sample sheets are not for Excel 5.0.

With regard to the Excel function, the maximum number of data passable at one time is limited to 32,767 records (both in row and column directions) for an array formula, or to 32,767 records (in row) and 256 records (in column) as a general expression.
In Windows 95/98, the 16-bit limitation in addition to the above restrictions restrains the maximum passable data size at one time to 64 KB. So, the Excel’s standard “Numeric” type (double-precision floating-point number) data cannot be transferred more than 8,192 records.
Thus, the 32-bit Windows NT/2000/XP operating system is recommended for a higher dimension multivariate Monte Carlo with NtRand 32bit.
Basically these limitations are caused from the Excel and Windows, and not by NtRand 32bit.

Excel 2007 or later can treat more than 32,767 records.

The most frequently asked question for NtRand is this type of question. This is caused by Excel, not NtRand. People who don’t know how to use Excel well, such as,

key combination, must read Excel help and/or how-to books carefully. In addition, we recommend to read either “array formula “ or “Microsoft Excel-specific Limitations”.

Log normal distribution has 2 parameters such as and , and its cumulative distribution function is :

Where is the cumulative distribution function of the standard normal distribution.
Parameter and are not the mean and not the standard deviation of the distribution respectively.

As you can see in r.h.s, the variable’s logarithm is normally distributed. Therefore, and are the mean and the standard deviation of the variable’s natural logarithm.

However, how about the mean and standard deviation of itself？
If the mean of is then the mean of is ? (Then the standard deviation of is ? ) Unfortunately, that is a sweet fantasy.

When you calculate the mean and the standard deviation carefully according to definitional identity…

Therefore, it’s not possible to describe the mean of only with nor describe the standard deviation only with . Both the mean and the standard deviation are combinations of and .

Of course NtRand is here to do all those tough calculations for you.

What about the inverse of this calculation, in another word, to calculate and from and ? Don’t worry. Let NtRand do.

By the way, the result is


You can also check details at Sample Sheet.

If you have cumulative distribution function and its inverse function of a ** distribution, you can generate random number sequence following “Truncated” ** distribution from uniform random numbers.

There are three generating process depends on the way to truncate the tail.

• Truncate both tail (Restrict the range to )
  • 
  • 
• Truncate lower tail (Restrict the range to )
  • 
  • 
• Truncate upper tail (Restrict the range to )
  • 
  • 

By using constants defined above,

is random number following “Truncated” ** distribution.
You can also check details at sample (Truncated Weibull distribution)

NtRand’s 64bit/32bit versions are corresponding to Excel versions. Typically, we hear people trying to install NtRand 64bit version just because their OS is 64bit version. But that’s not always the case. Especially OS 64bit usually has Excel 32bit version installed by default. You’d better check your Excel version before installing!

The computing result depends on the computer’s CPU, operating system, and compiler. Thus the same program would produce various results on the different machines. For instance, in triangular decomposition of a larger matrix, the results are not identical between on a Windows machine and on a UNIX workstation. The key reasons for such difference are (1) the operation mode of the floating-point processing unit (FPU) on the CPU chip and (2) the microprogram physically coded on the ROM on CPU chip. This is the different question from the binary underflow or round-off error, or the binary coded decimal (BCD) problems often described in the reference books.
Assume that you run a double data type program (most accurate numeric type among C/C++) on a Windows machine. Its binary expression must be 64-bit in most compilers (e.g., Sun Pro CC, egcs, HP CC, Visual C++, Intel C/C++). However, the binary expression accuracy of an Intel x86 FPU is settable to 24/53/64-bit “using the program,” of which setting is usually specified in the operating system or compiler. In addition, Pentium4 / Xeon has SSE II instruction set, enables to process two double precision value at once by vectored operation. This feature also has sub-effect to accuracy, of course. Different CPU ought to employ different microprogram algorithm, therefore it is natural for the computers to generate various results in calculation of functions without analytic solution (e.g., trigonometric function, exponential function). That’s why you always get different results on each operating system such as Windows, Linux, and various version of UNIX.
Watch out, if you are using a personal computer or workstation for evaluation of positioning derivatives. A computer has a daemon inside. To be frightened, to the knowledge of experts who are engaged in numerical calculation, there are number of problems restrained from disclosure or active opening due to the business operation reasons of computer manufacturers. Numerical calculation is an abyss even to the system engineers. Please study this paper (David Goldberg, “What Every Computer Scientist Should Know about Floating-Point Arithmetic”) for more information.

When you change random seeds, different random number series are generated. Then what kind of random seeds should be given? This question is very sensitive and deep, so deep that you can’t see the bottom.

To conclude, there’s no other way except giving “randomly picked” numbers as random seeds to generate different random number sequences. However, if you don’t have to worry about random seeds nor if you embrace that random seed is a God-given value like a random number generator function RAND() of Excel, we tell you an alternate method, i.e. a random seed itself is a random number!

Those methods above are widely used from well-known professors to ordinary people.
However, it should be used after careful tests of its independency among random number series (Naturally).
In reality, there’s only quite few people who does such test, and those random number series are recognized as independent without being tested.
(Those random numbers are independent to each other more or less in fact.)
This is why on current NtRand we designed to give random seeds manually. We do also have a plan to provide this Excel-imitated random number generator function noted above in the next version of NtRand.

If you are interested in those mysteries of random numbers, this link might help.
Dynamic Creator Home Page(Mersenne Twister web site)

The original random number generating algorithm is extended to prepare the seeds of random numbers for 64 bits. This expansion is necessary to secure a large initial-value space for the simulation with repeatability (i.e., back testing, WHAT-IF analysis) in the business practices. Generally, such simulation is conducted by having the key fields of the database associated with the type of random numbers.