Monte Carlo Simulation in Excel

How do you run a Monte Carlo simulation in Excel?

Last updated September 2026

You can do it with nothing but built-in formulas, in five steps:

1. Build the model with a single value for each uncertain input.
2. Replace each of those inputs with a random draw from a probability distribution, using RAND() and an inverse function such as NORM.INV.
3. Recalculate the model thousands of times, one row per iteration or with a Data Table.
4. Collect the output of every iteration.
5. Summarise those outputs: mean, percentiles, the chance of exceeding a target, a histogram and an S-curve.

That covers simple models well. An add-in takes over when you need correlated inputs, a wide choice of distributions, sensitivity analysis or speed.

This guide works through one example both ways. Path A uses plain Excel, and every formula is shown. Path B builds the same model in the ModelRisk add-in and then adds the one thing plain Excel handles badly. Both are in a free workbook: download it and follow along. If you want the idea behind the method first, start with what Monte Carlo simulation is and how it works.

How do you generate random values from a distribution in Excel?

Use RAND() to produce a random number between 0 and 1, then pass it to the inverse of the distribution you want. The inverse turns “a random position between 0 and 1” into a value from that distribution. This is called inverse transform sampling, and it is how simulation software does it too.

Excel has inverse functions for some common distributions:

The example uses triangular distributions because they are the easiest three-point estimate to explain. With the random number in cell B2 and the minimum, most likely and maximum in C5, D5 and E5, one draw is:

=IF(B2<(D5-C5)/(E5-C5), C5+SQRT(B2*(E5-C5)*(D5-C5)), E5-SQRT((1-B2)*(E5-C5)*(E5-D5)))

Keep the random number in its own cell, as above. The formula uses it twice, and two separate RAND() calls would give two different numbers and a wrong result. One random number per input per iteration is the rule.

Triangles are simple, but they put a lot of weight near the extremes. For expert estimates, a PERT distribution is usually more realistic, and the BETA.INV row in the table gives it to you in plain Excel.

How do you read the results?

Summarise the column of 5,000 totals with ordinary functions: AVERAGE for the mean, PERCENTILE.INC(range,0.8) for the P80, and COUNTIF(range,">"&budget)/5000 for the chance of overspending. A P80 is the value with an 80% chance of not being exceeded. Count the totals into bins with COUNTIFS for a histogram, and the running share below each bin gives the S-curve.

The estimate everyone started from has a 96% chance of being exceeded. Nothing in the model is pessimistic: each item's most likely value is exactly what the estimator said. But every range has a longer tail on the high side, so each item's average sits above its most likely value, and the averages add up. The sum of the most likely values is not the most likely total. Our guide to calculating cost contingency works through what to do with that gap, including why you cannot add up line-item P80s: here they sum to €3,349k, well above the real P80.

How many iterations do you need? With 5,000 rows, the P80 moves by only a few thousand euros between runs (we re-ran it 200 times: 95% of runs landed between €3,190k and €3,203k). That is precise enough for a budget decision. Means settle quickly and extreme percentiles need more iterations; how many Monte Carlo iterations you need covers the rule of thumb and the reason behind it.

How do you run the same simulation with an add-in?

A Monte Carlo add-in keeps the model in Excel and supplies the distributions, the simulation engine and the results analysis. In ModelRisk the whole Path A model shrinks to a few cells. Each cost item becomes one formula:

=VoseInput("Groundworks")+VoseTriangle(350,400,560,U)

Here U is a correlated random number from a copula, one array formula across the six items: {=VoseCopulaMultiNormal(CorrelationMatrix)}. The total is marked as the output with =VoseOutput("Total cost")+SUM(...). Then you set the number of iterations (10,000 here) and click Start. ModelRisk runs them without adding a single row to the workbook. The Results Viewer shows the histogram, the S-curve and a tornado chart of which item drives the risk. VoseSimPercentile(Total,0.8) writes the P80 back into a cell for your report.

The workbook's Path B sheet is this model, with the correlation on the Inputs sheet. Run it at 0 and the results match Path A to within sampling noise. Set it to 0.6 and the P80 moves up as described above. From there, try swapping VoseTriangle for VoseModPERT (the PERT shape estimators usually find more realistic), or read the tornado chart to see which item deserves attention first.

ModelRisk is one of several Excel add-ins that do this, some of them free for basic work. Our comparison of the top risk analysis add-ins for Excel covers features and published prices side by side.