How much contingency does a risk register need?

Worked example · Risk management · Updated October 2026

In this register of 10 risks (amounts in USD thousands), probability × most likely impact adds up to 291, but a Monte Carlo simulation of the whole register puts the average loss at 359 and the worst 5% of outcomes at 1,436 on average, so size the contingency from that tail. Even a reserve the size of the average loss is exceeded in 36.6% of outcomes.

One outcome in twenty loses more than 1,028 (P95), and the worst 5% average 4.0 times the mean. Check insurance limits against these tail figures too, not against the average.

The model

The workbook is a plain risk register: 10 risks, one per row, each with the probability that it occurs (column B) and a Min, Likely and Max impact if it does (columns C to E). A note below the register gives the unit: amounts are in USD thousands, as in the project cost example.

Next to the estimates are the two cells per risk that the simulation fills in each trial. Occurs (column F) is 1 with the risk’s probability and 0 otherwise, a Bernoulli distribution. Impact (column G) is drawn from a PERT distribution between Min and Max that peaks at Likely. Exposure (column H) multiplies the two with =F2*G2, and the total loss in H13, =SUM(H2:H11), is the output. Column I holds the usual calculation, probability × likely, with its total in I13. There are no add-in functions. As saved, Occurs is 0 and Impact equals Likely, so the total in H13 reads 0 in Excel until the simulation fills these cells.

The estimates stay in the register

The simulation setup that opens with the example does not copy these numbers. Each parameter points at a cell of the register: the probability of the first risk is =Register!B2, its Min, Likely and Max are =Register!C2, D2 and E2, and so on down the rows. xellstorm reads the cells once before each run, with the same results as if the numbers had been typed in. On the Distributions step, a linked field shows its reference, such as =B2, and the value it read.

The register stays the one place where estimates live. To carry your edits over, save a project file on the Results step (Save project), change probabilities or impacts in Excel, then open the edited workbook in xellstorm and apply the project file (Open project file, on the Workbook step). xellstorm warns that the workbook differs from the one the project was saved with, and the next run reads the new values.

Probability × likely impact understates the expected loss

The common way to total a register is its expected monetary value (EMV): each risk’s probability times its impact, added up. With the most likely impact, as in column I, this register comes to 290.5. The simulated average loss is 358.8, 23.5% more.

The gap comes from skew. The likely impact is the mode, the peak of the distribution, not its average: when Max lies further above Likely than Min lies below it, the average is higher. For a PERT distribution the mean is (Min + 4 × Likely + Max) / 6. The Cyber incident risk would most likely cost 800, but its mean impact is (300 + 4 × 800 + 2,500) / 6 = 1,000, so its expected loss is 50, not 40. With mean impacts the EMV comes to 358.7, which the simulation reproduces (358.8).

Done right, then, the EMV is the average loss. But an average is one number: it says nothing about how often the losses are far larger, and covering those outcomes is what a contingency reserve is for.

Results

In 5.6% of the 10,000 simulated outcomes no risk occurs at all; the exact chance, the product of (1 − probability) over the 10 risks, is 5.7%. That is why the app’s percentile table shows 0 for P1 and P5. Half the outcomes lose less than 267, below the mean of 359: most outcomes involve a few small or mid-size risks, and a few combine large ones and pull the average up.

Why contingency should come from the tail

Any reserve is exceeded in some outcomes. The question is how often, and by how much:

A reserve at the mean (the EMV with mean impacts) runs out in 36.6% of outcomes, and one at the column I total in 46.2%. Which percentile to hold is a policy decision about how often you accept running out; P50, P80 and P90 explains the levels. P95 is the loss exceeded in one outcome in twenty.

CVaR, the conditional value at risk, answers the next question: when losses go past P95, how large are they? Here the worst 5% of outcomes average 1,436, so beyond P95 they overshoot it by 407 on average. That is the figure to have in view when sizing a reserve for bad years or checking an insurance limit: a limit set near the average would pay for the ordinary outcomes and leave the large ones uncovered, and even a reserve at the CVaR is exceeded in 2.0% of outcomes.

Which risks fill the worst outcomes

Averages spread the loss across the register: each risk’s share of the average loss lies between 5.8% and 14.1%. The tail is different. Cyber incident, the least likely risk in the register (5%), occurs in 70.4% of the worst 5% of outcomes and makes up 57.7% of their loss, 829 of the 1,436, against 14.1% of the average loss. The next largest, Key supplier fails, contributes 155 (10.8%). The worst outcomes are also those where several risks coincide: 3.8 risks occur in them on average, against 2.35 over all outcomes.

This split is computed on this page from the trials: the results workbook’s Trials sheet has every trial’s inputs and total, so you can reproduce it by multiplying Occurs by Impact for each risk and averaging over the worst 5% of totals. In the app, the contribution-to-variance table under Results ranks inputs by how much of the whole spread they explain, and ranks the Occurs input of Key supplier fails (whether that risk happens) first, with 19.6%; see sensitivity analysis. Spread and tail are different questions. For a reserve or an insurance limit, look at the worst outcomes: the app’s worst-tail panel compares each input in the worst 5%, 10% or 25% of trials with all trials, and Trial by trial plots any input, such as a risk’s impact, against the total.

Try it yourself

1. Open the model in xellstorm. Each risk’s Occurs and Impact cells are inputs, the total in H13 is the output, and losing more than 1,000 is the target; there is nothing to install and no sign-up, and the workbook is calculated in your browser.
2. Run it: with the same seed and 10,000 trials you get the numbers on this page. Results show the mean, the chance of losing more than 1,000, the P90 and the worst 5% average (CVaR); the P80 and P95 are rows of the percentile table.
3. Type your own limit into the probability box to see how often losses exceed it, or hover over the S-curve to read the chance of staying under any amount.
4. On the Distributions step, add a scenario that lowers the probability of Cyber incident, run again, and compare the scenarios on the same random draws.
5. Then open your own register: make each risk’s occurrence a Bernoulli input and its impact a PERT, and type references such as =B2 so the parameters are read from the register’s cells.

Can I do this in plain Excel?

Yes: you can build the same simulation in plain Excel with RAND() and a data table; Monte Carlo simulation in Excel shows the formulas for risk events and PERT impacts.

Questions

Related

• Project cost and schedule

  How much contingency does a construction project need?

  Worked example: a building estimate’s most likely costs are exceeded in 94.2% of simulated outcomes. Size the construction cost contingency with Monte Carlo.
• Guide

  P50, P80 and P90: what they mean and which to budget at

  P80 is the cost 80% of simulated outcomes stay under. Six building cost items’ P80s add up to 2,843, but their sum’s P80 is 2,756 (USD thousands).
• Guide

  Tornado charts and sensitivity analysis explained

  A tornado chart moves each input alone, from P10 to P90 by default in xellstorm. In a building estimate, one risk moves the cost by 150 (USD thousands).

xellstorm is a browser-based Monte Carlo simulation tool for Excel models: no add-in, and the workbook never leaves your computer.