Free Excel Templates

Cyber Risk Monte Carlo Model (Excel)

An unlocked Excel model that turns one cyber loss scenario, described with calibrated ranges, into 1,000 simulated years: annual loss expectancy, percentiles, loss exceedance points and the effect of a cyber insurance retention and limit.

Describe a scenario the way a FAIR analysis does: how many threat events a year (minimum, most likely, maximum), the probability that one becomes a loss, the primary loss as a 90 percent range, and the probability and range of a secondary loss. In the prefilled case, a ransomware outage of the plants of Halden Marsh Industries, a fictional manufacturer, the model returns an ALE of $1,639,809, a loss in 9.3 percent of years and a 1-in-20 year of $14,546,698.

Get the Excel model

One unlocked Excel workbook, zero macros, zero circular references. Free: enter your email address and the download opens at once, along with the other free finance models.

Cyber_Risk_Monte_Carlo_Model.xlsx (231 KB)

You join the reader list: one email when a new free model or book comes out. Unsubscribe in one click. Already subscribed? Enter the same address. How your address is used.

The handbook behind this model

This model is a one-scenario extract of the 10-tab workbook in The Cyber Risk Quantification Handbook, which takes one company from its heat map to five quantified scenarios, a portfolio loss exceedance curve against board tolerance, the return on three controls, the cyber insurance renewal and an incident materiality call.

Paperback PDF edition, $19.99 Book page and full workbook

What is inside the workbook

Inputs
The scenario in blue cells: threat event frequency and vulnerability (minimum, most likely, maximum), primary loss and secondary loss as 90 percent ranges, the secondary loss probability, a retention per event and an aggregate limit; below them, the PERT and lognormal parameters and the analytic expected values, as formulas.
Uniforms
1,000 rows of twelve stored random numbers: one simulated year per row.
Simulation
For each year: the loss event frequency, the Poisson count of loss events, up to three event magnitudes with their secondary losses, the gross annual loss, the insurance recovery and the net loss.
Results
Annual loss expectancy, share of years with a loss, the 90th, 95th and 99th percentiles, two loss exceedance points, expected recovery, net ALE and net 95th percentile, and the analytic ALE for comparison.

What the prefilled example returns

MeasureValue
Annual loss expectancy (simulated mean)$1,639,809
Analytic ALE (mean LEF x mean loss per event)$1,404,218
Years with a loss9.3 percent
95th percentile (1 year in 20)$14,546,698
99th percentile (1 year in 100)$33,292,684
Probability of a loss above $10,000,0006.6 percent
Probability of a loss above $25,000,0001.6 percent
Expected insurance recovery ($2,000,000 retention, $20,000,000 limit)$1,164,843
Net ALE after insurance$474,966

Read the gap between the simulated and the analytic ALE before anything else. The analytic figure, $1,404,218, is the exact expectation of the inputs; the simulated $1,639,809 rests on the 93 years out of 1,000 in which a loss occurred. With a rare, severe scenario, 1,000 trials estimate the mean with visible sampling error, and the 1-in-100 year with even more. The ranges themselves remain the larger source of uncertainty: document the reasoning behind each one.

For the method step by step, read how to quantify cyber risk in dollars with FAIR; for reading the output, the cyber risk loss exceedance curve; for the insurance decision, choosing a cyber insurance retention and limit.

Method after the Open FAIR body of knowledge, described in principle. The company, its scenario and every figure are fictional teaching assumptions. Education only, not insurance or security advice.

Questions practitioners ask

Which method does the model use?

The FAIR decomposition: loss event frequency equals threat event frequency times vulnerability, each drawn from a PERT distribution (minimum, most likely, maximum); the number of loss events in a year is a Poisson draw; each event's primary loss, and its secondary loss when one occurs, come from lognormal distributions fitted to your 90 percent ranges. In the prefilled ransomware scenario the mean loss event frequency is 0.0817 a year and the analytic ALE $1,404,218.

Why are the random numbers stored rather than generated?

So that every result is reproducible and every what-if is compared on the same 1,000 simulated years (common random numbers). A change in the result then comes from the change you made to an input, not from a new draw.

Why is the 90th percentile zero?

Because the prefilled scenario produces a loss in only 9.3 percent of simulated years. When a loss occurs it is large: the 95th percentile is $14,546,698 and the 99th $33,292,684. Low-frequency, high-severity risks are exactly where a single average misleads.

Can I model several scenarios?

This free model handles one scenario. The companion workbook of the book simulates five scenarios together, with the portfolio loss exceedance curve, risk appetite, control returns and the insurance program across scenarios.