Skip to content
Find your tool
Guides · 8 min read

How to calculate contingency with Monte Carlo, step by step

From estimate ranges and risk events to a P80 contingency, in plain Excel: the formulas, a worked example and the governance rules that make the number useful.

By Mike Bouman, Quantil · November 4, 2026

"Just add 10%." It's still the most common way to set contingency, and it's quick. It's also a guess. The good news: doing it properly isn't nearly as hard as it sounds, and you can start in Excel today.

To calculate contingency with Monte Carlo, you simulate the total project cost thousands of times using ranges and risk events, read off the cost at your chosen confidence level (say P80), and subtract the base estimate. What is left is your contingency. Below are the steps, with Excel formulas and a worked example. For the reasoning behind each step, read how to set a P80 contingency.

What you need before you start

Step 1: Put a range on every estimate line

Each cost line gets three values. The triangular distribution is the simplest option and easy to explain to anyone. PERT puts less weight on the extremes and suits items you know well. Whichever you pick, be honest about how wide the ranges are. Ranges that are too narrow are the weakest spot in many models.

Step 2: Draw a random value for each line

In Excel, put a random number in its own cell, for example =RAND() in a cell named U. Then turn it into a triangular value with minimum A, most likely M and maximum B:

=IF(U<(M-A)/(B-A), A+SQRT(U*(B-A)*(M-A)), B-SQRT((1-U)*(B-A)*(B-M)))

In words: below the turning point (M−A)/(B−A) the value rises from A towards M; above it, it runs from M towards B.

Step 3: Add the risk events

A risk either happens or it does not. For a risk with probability P and a triangular impact:

=IF(RAND()<P, [triangular formula for the impact], 0)

Use one random number for whether it happens and another for how much it costs. And before you add a risk, check that it isn't already hiding inside one of your estimate ranges. Counting it twice quietly inflates the contingency.

Step 4: Add correlation

Cost lines don't move independently. When one goes up, others often follow. A simple way to build that into a spreadsheet is a shared random factor. With one common standard normal draw Zc, one draw Zi per line and a correlation ρ:

=NORM.S.DIST(SQRT(ρ)*Zc + SQRT(1-ρ)*Zi, TRUE)

Draw each Z with =NORM.S.INV(RAND()). Use the result as U in Step 2, and every pair of lines will be correlated at roughly ρ. On large engineering programs, values between 0.3 and 0.6 are common, and 0.4 is a frequent default for cost when you have nothing better. Skip this step at your peril: Book (1999) showed that with 30 lines correlated at only 0.2, assuming independence understates the spread of the total by about 60%. More in our guide on correlation.

Step 5: Run it thousands of times

Add all lines and risks up in one total cost cell. Then let Excel recalculate it over and over:

  1. In an empty column, number the rows 1 to 10,000.
  2. Next to the first number, link to your total cost cell.
  3. Select both columns and go to Data > What-If Analysis > Data Table. Use any empty cell as the column input cell.
  4. Excel now recalculates the model once per row and stores every total.

Ten thousand iterations is usually enough for stable P80 and P90 values. A quick check: recalculate twice. If the P80 moves by more than a small fraction, add iterations.

Step 6: Read the confidence levels

Use =PERCENTILE.INC(results, 0.5) for the P50 and =PERCENTILE.INC(results, 0.8) for the P80. Sort the results and plot them against their rank, and you have your S-curve.

Step 7: Calculate the contingency

Contingency at P80 = P80 − base estimate.

Worked example: the Millbrook Connector

Illustrative case. The project and figures are fictional.

The fictional Millbrook Connector has a base estimate of $120 million across seven cost lines, six quantified risks with a combined expected value of $7.1 million, and a correlation of 0.4 between the cost lines. The full input is in the practitioner's guide. Running 100,000 iterations gives:

ResultValue ($M)
Base estimate (sum of most likely values)120.0
Mean of estimate lines alone129.2
Mean including risk events136.3
P50135.8
P80143.4
Contingency at P8023.4 (20% of the estimate)

Two things surprise most people here. The risk register only adds $7.1 million on average; most of the contingency actually comes from the skew in the estimate ranges. And without correlation, the P80 would have come out at about $141.9 million, so you would have been some $1.5 million short without knowing it.

A practical tip. Run range workshops per cost area with the people who actually know that work, and ask for the maximum first: “what would make this cost a lot more?” Start with the most likely value and everyone anchors on the estimate. Write every risk as cause, event and effect, so nobody ends up putting a number on a vague worry. On linear projects such as new transmission lines, with many work locations and long supply chains, two risks are easily forgotten: delays caused by third parties (grid operators, permitting bodies, suppliers) and the time-related costs (site overheads, crews, equipment standby) that come with every delay. Walking through each interface in the chain before work starts, who delivers what, when and at what quality, catches many of them early.

Step 8: Decide who holds the contingency

A number on its own doesn't protect anything. How you govern it does. Three rules that hold up in research and in practice:

Contingency versus management reserve

Contingency covers known risks and estimate uncertainty within the agreed scope. A management reserve covers what the model can't see, like scope changes or events nobody thought of. Keep them apart in your reporting, so each one can be justified on its own terms.

Frequently asked questions

Can I do this without special software?

Yes. The steps above work in plain Excel. Dedicated tools just make correlation, auditing and reporting faster and less error-prone; see Excel tools, add-ins or dedicated risk software.

Triangular or PERT?

Triangular is simpler and a bit more conservative in the tails. PERT suits items where you trust the most likely value. Honestly, the choice matters less than having honest ranges.

How often should I recalculate?

At every stage gate, after major contract awards, and whenever a top driver changes.

Quantil's QRCIM does all of this in a single Excel workbook, including correlation, drivers and a board report. Try the free demo.

References