Treasury
Publication

# Appendix 1: Monte Carlo Simulation[18]

257.This section describes what a Monte Carlo simulation is and how it is carried out.

258.A Monte Carlo simulation is a computer-based technique that uses statistical sampling and probability distributions to simulate the effects of uncertain variables on model outcomes. It provides a systematic assessment of the combined effects of multiple sources of risk (represented by the confidence intervals) in each of the costs and benefits and can also allow for known correlations between these variables.

259.The first step is to estimate 90% confidence intervals and probability distribution for each cost and benefit. In many cases, it will be reasonable to assume a normal distribution.

260.A Monte Carlo simulation can be run by Excel and many other programs. The program randomly generates thousands of possible values for each variable, based on the estimated confidence interval and type of probability distribution. The computer then calculates a probability distribution for the outcome (eg, the net present value of NPV).

261.To make a Monte Carlo simulation of a normally distributed variable in Excel, we use this formula:

=norminv(rand(), mean, standard deviation)

262.So if the mean is 15 and the 90% confidence interval is +/-5, then the formula for the variable is:

=norminv(rand(), 15, (5/1.645)[19]

263.Suppose you enter this formula on cell A1 in Excel. To generate (say) 10,000 values for a particular cost or benefit, (1) copy the contents of cell A1, (2) enter “A1:A10000” in the cell range field to select cells A1 through A10000, and (3) paste the formula into all those cells.

264.Now follow this process in other columns for the other variables, including columns for the discount rate factors for each year. Then insert columns to calculate the net present value (NPV) and benefit-cost ratio (BCR) formulas for each row.

265.We can now work out the 90% confidence interval for the NPV (the same approach is used for the BCR). Use the STDEV formula to work out the standard deviation. The 90% confidence interval is equal to the mean of the numbers in the NPV column (use the AVERAGE formula) plus or minus the standard deviation times 1.645.

Example:

Suppose the Monte Carlo analysis results in a mean value for the NPV of \$173 million, with a 90% probability that it lies within the range of -\$327 million to \$673 million. We can make a histogram (see below) to show how many of the 10,000 scenarios landed in each \$50 million increment (of NPV). This is more informative, and tells us a great deal about the distribution of risk and benefits we might incur from investing in this project.

266.The simulation concept can (and in high-value cases should) be carried beyond this simple Monte Carlo simulation. One improvement would be to learn how to use a greater variety of distributions (aside from the normal distribution). Noting that the main weakness of the above approach is that it assumes that variables are uncorrelated, another improvement would be to generate correlated random numbers or to model what the variables have in common. Cheap software packages are available that allow complicated correlation structures and a wide range of distributions in addition to the normal function (e.g. rectangular, triangular, lognormal, etc.).

Page top