Page 364 - Excel for Scientists and Engineers: Numerical Methods

Chapter 15

Random Numbers and

the Monte Carlo Method

The Monte Carlo method differs from the techniques we have considered in
preceding chapters: instead of applying quantitative mathematical expressions to
arrive at an answer, we approximate or simulate the process, repeat the
calculation a large number of times using randomly selected inputs chosen within
a suitable range, and then average the result or draw other statistical conclusions.
The method can be lengthy and provide only an approximate answer, but it may
be the only available way to arrive at an answer.
Monte Carlo methods have been used in economics, in nuclear physics and to
model traffic patterns. We will look at two main types of application: Monte
Carlo simulation and Monte Carlo integration.

Random Numbers in Excel

Since the Monte Carlo method involves the use of random numbers, we will
begin by examining how random numbers are produced and used within Excel.

How Excel Generates Random Numbers

In Excel 2003, an improved random number generator was implemented.
Earlier versions of Excel used a pseudo-random-number-generation algorithm
whose performance on standard tests of randomness was not sufficient to satisfy
the demand of power users who might require the generation of a million or more
random numbers. For the majority of users, the older pseudo-random-number
generator was satisfactory.
The earlier algorithm used the following iterative method to calculate
pseudo-random numbers:
The first random number:
r = fractional part of (9821 x s + 0.21 1327)
where s = 0.5, and successive random numbers:

34 1