xellstorm Learning center
EN
Open the app

How much does a reinsurance layer cut the bad years?

Worked example · Excess-of-loss reinsurance · Updated November 2026

Simulate the year claim by claim, apply the layer to each large claim, and compare structures on the same simulated years. In this illustrative property book, 14.6% of 10,000 years lose money without reinsurance, and the 1-in-100 year ends at −24,026. With a layer of 18,000 above a retention of 2,000 per claim, 8.0% lose money and the 1-in-100 year ends at −6,668. The layer costs 1,518 in the average year.

How a year of claims meets the layerEach year the book earns 50,000 of premium and pays 30% of it in expenses, about 300 small claims and about 4 large ones. The layer pays each large claim's part above 2,000, up to 20,000, for a premium of 5,150. In the simulated year drawn, the layer recovers 16,646 on 4 large claims of 15,655, 1,522, 1,583 and 4,991: the year loses 7,595 before reinsurance and earns 3,901 after. Over 10,000 years, 14.6% lose money without the layer and 8.0% with it.Each yearPremium50,000− Expenses30% of premium− Small claimsabout 300 a year, each under 1,000− Large claimsabout 4 a year, 1,000 to 40,000+ Recoverieseach claim above 2,000, to 20,000− Reinsurance premium5,150One simulated year · large claims15,6551,5221,5834,991Retention 2,000keptreinsurer pays14.6%of years lose money without the layer8.0%with it How a year of claims meets the layerEach year the book earns 50,000 of premium and pays 30% of it in expenses, about 300 small claims and about 4 large ones. The layer pays each large claim's part above 2,000, up to 20,000, for a premium of 5,150. In the simulated year drawn, the layer recovers 16,646 on 4 large claims of 15,655, 1,522, 1,583 and 4,991: the year loses 7,595 before reinsurance and earns 3,901 after. Over 10,000 years, 14.6% lose money without the layer and 8.0% with it.Each yearPremium50,000− Expenses30% of premium− Small claimsabout 300 a year, each under 1,000− Large claimsabout 4 a year, 1,000 to 40,000+ Recoverieseach claim above 2,000, to 20,000− Reinsurance premium5,150One simulated year · large claims15,6551,5221,5834,991Retention 2,000keptreinsurer pays14.6%of years lose money without the layer8.0%with it
The model at a glance (USD thousands): the year's claims, small ones as one total and large ones one by one. The layer takes the part of each large claim above the retention.

Many small claims add up to a steady total; a few large claims make the bad years. The model draws both, applies the layer to each large claim, then compares no layer and two other retentions on the same claims. Amounts are in USD thousands.

Open this model in xellstorm Download the free Excel template (.xlsx)

The model

The Assumptions sheet holds the book: premium of 50,000, expenses of 30% of premium, the claim assumptions and the layer (“18,000 xs 2,000” in the charts: 18,000 in excess of 2,000), with the reinsurer’s quote for each retention. The Claims sheet holds one year: the small claims’ total in B3, the number of large claims in B4, and one row for each possible large claim, rows 7 to 21. A row’s claim occurs when its number is within the year’s count: C7=IF(A7<=$B$4,1,0).

Column E is the reinsurer’s part of each claim, what lies above the retention up to the cover’s top: E7=IF(Assumptions!$B$14=1,MIN(MAX(D7-Assumptions!$B$15,0),Assumptions!$B$16-Assumptions!$B$15),0). On Result, premium less expenses and claims is the result before reinsurance (B7). Less the reinsurance premium and plus the recoveries, it is the result after reinsurance (B10), and the target asks whether that is below zero.

Small claims as one total, large claims one by one

Claims!B3 is a frequency × severity input (a compound distribution). Each trial draws a Poisson count with mean 300, then that many claim sizes from a lognormal distribution with mean 60 and standard deviation 90, truncated at the 1,000 threshold, and adds them up. Hundreds of small claims average out: the total’s P5–P95 is 14,972 to 20,535 around a mean of 17,657.

Large claims are few, and the layer applies to each one, so each has its own cell. Claims!B4 is a Poisson count with mean 4, limited to 15, the number of rows on the sheet; with that mean, a year with more than 15 large claims would be rarer than 1 in 10,000. Each size in B7:B21 is a Pareto distribution above 1,000 with shape 1.5, truncated at the largest insured value of 40,000, so no claim is larger. A lower shape means a heavier tail: most large claims stay near the threshold, and a few are far larger.

The years that lose money before reinsurance are years of large claims. Their large claims add up to 25,210 on average, against 7,609 in the other years, while the small claims’ total barely changes.

Read the result

Result without reinsuranceHistogram of 10,000 simulated years’ underwriting result, without reinsurance: 14.6% below zero, mean 7,166, 1-in-100 year −24,026 (USD thousands).−60,000−40,000−20,000020,0001 in 100Loss years 14.6%
Result without reinsurance, USD thousands. Red bars are years that lose money; the dotted line is the 1-in-100 year.
Result with the 18,000 xs 2,000 layerHistogram of 10,000 simulated years’ underwriting result, with the 18,000 xs 2,000 layer: 8.0% below zero, mean 5,647, 1-in-100 year −6,668 (USD thousands).−60,000−40,000−20,000020,0001 in 100Loss years 8.0%
Result with the 18,000 xs 2,000 layer, USD thousands. Red bars are years that lose money; the dotted line is the 1-in-100 year.

Without reinsurance the average year earns 7,166, and 61.1% of years do better than that; the others do worse, some far worse: 14.6% of years lose money, and the 1-in-100 year ends at −24,026. With the layer, 8.0% lose money, the 1-in-100 year ends at −6,668 and the 1-in-200 year at −10,137. The 1-in-100 year is the result that only 1 simulated year in 100 falls below (the 1st percentile); the 1-in-200 year is the 0.5th percentile.

What the layer costs

The reinsurer quotes 5,150 for the layer above 2,000, and the layer recovers 3,632 in the average year. The quotes in the workbook are about 1.4 times the expected recoveries: a reinsurer’s premium also covers its costs, the capital it holds and its margin. The difference, 1,518, is what the layer costs in the average year, so the mean result falls from 7,166 to 5,647. In exchange, the 1-in-100 year improves by 17,358.

Which retention?

Four structures on the same 10,000 simulated years (USD thousands)
StructureReinsurance premiumExpected recoveriesMean resultLoss years1-in-100 year1-in-200 year
No reinsurance007,16614.6%−24,026−30,152
Retention 5,0002,2501,5716,48612.0%−10,610−14,037
Retention 2,000 (base)5,1503,6325,6478.0%−6,668−10,137
Retention 1,0008,4005,9744,7405.2%−5,537−9,254

A lower retention costs more and cuts more, with diminishing returns. From no layer to a retention of 2,000, 1,518 a year improves the 1-in-100 year by 17,358. Going on to a retention of 1,000 costs another 908 a year for 1,131 more.

Most of the worst 1% of years under the base layer, 89% of them, hold a claim above the cover’s top of 20,000. That part of the claim comes back to the insurer, and a lower retention does not reach it; a higher top would, at a price not quoted here. Only 2.8% of all years have such a claim, which is why a top can look high enough until the tail is simulated.

Each structure meets the same simulated claims, so the differences come from the layer, not from luckier draws.

What a per-claim layer does not cover

The layer pays per claim. A storm that damages hundreds of properties at once makes hundreds of claims, each below the retention, and a per-claim layer pays none of them. That needs a catastrophe (per-event) cover. This model has no catastrophe events, and no reinstatements, claims inflation or investment income; its claim assumptions are illustrative, not a fit to a book’s history.

Try it yourself

  1. Open the example and run its 10,000 trials with seed 1. The result after reinsurance and its target, a year below zero, are already set.
  2. Read the result’s histogram and percentiles, then compare it with the result before reinsurance, Result!B7.
  3. Run the three saved scenarios and compare their loss years and low percentiles with the base layer.
  4. For your own book, fit the claim counts and sizes to its claims history, and add catastrophe events if one storm can hit many risks at once.

Questions

Why not use the average loss ratio?

The average year says how much the book earns, not how often or how badly it loses. Large claims are rare and uneven, so most years do better than the average and a few do much worse. Simulating many years shows those few, and what a layer does to them.

Why does the reinsurer charge more than it is expected to pay?

Its premium also covers its expenses, the capital it holds against bad years and a margin. A layer is worth its cost when the bad years it removes matter more to the insurer than the average result it gives up; the simulation puts both in the same units.

Is the 1-in-200 year a capital requirement?

No. A solvency capital model also covers reserves, catastrophes, market, credit and operational risk, and when claims are paid. This one-year underwriting result is one piece of that picture.

Related

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