ModelRisk example · Insurance

Reinsurance Programme: Excess of Loss and Quota Share

What does reinsurance cost, and what does it buy? Four programmes compared on the same claims.

Complexity: ★★★★★
Free Excel workbook (IN-03)
Needs ModelRisk: free trial

Download the Excel model Get the free ModelRisk trial

From the model’s saved run

Which programme?

The underwriting result (US$m) under each programme, on the same simulated claims.

Programme	Mean US$m	Std dev US$m	1-in-20 5th pct	1-in-200 0.5th pct	Loss chance
No reinsurance	3.59	10.50	−13.18	−43.19	26.8%
Excess of loss only	1.90	6.65	−6.99	−22.13	32.7%
Quota share only	3.14	7.35	−8.59	−29.60	24.0%
Excess of loss and quota share	1.47	4.65	−4.75	−15.35	31.4%

Underwriting result — cumulative probability

S-curve (cumulative probability) of No reinsurance, Excess of loss, Quota share, Both from the saved simulation.

The business question

A commercial property insurer writes US$42 million of premium a year. It expects about 60 claims, averaging US$500,000, but property claims are very uneven: most are small, while a fire at a large factory can cost tens of millions. Its board wants the 1-in-200 loss — which sets the capital the insurer must hold — brought down.

The broker offers two per-risk excess-of-loss layers, which pay the part of each claim between US$2 million and US$10 million and between US$10 million and US$25 million, and a 30% quota share, which takes 30% of the premium and of the claims the insurer keeps, and pays back a commission.

Reinsurance costs money on average — the reinsurers' margin — so the question is whether the capital it frees is worth the price. The model splits every simulated claim between the insurer and the layers, and compares the four programmes on the same claims.

Who it is for: Reinsurance buyers, underwriters, capital and pricing actuaries, finance teams of insurers.
Main outputs: The underwriting result with no reinsurance, the layers only, the quota share only and both: mean, spread, 1-in-20 and 1-in-200 results, chance of a loss · each programme's expected cost, the capital it frees and the price of that capital · the expected loss to each layer, calculated exactly.

How the model works

Three sheets after this one, read from left to right. Each one feeds the next.

Inputs sheet
The expected number of claims, the average claim and how heavy the tail of claim sizes is. The retention and the limits of the two layers and their prices as multiples of their expected loss; the quota share and its commission. The premium, the expense ratio and the cost of capital.
Programme sheet
Each layer's expected loss is calculated exactly with VoseDeductObject and sets its price. Every iteration draws the year's claims once: VoseAggregateTranche adds up, over every claim, the part below the retention, the part in each layer and the part above the programme. The recoveries, the premiums and the result follow for each programme.
Results sheet
Mean, spread, 1-in-20 and 1-in-200 result and the chance of a loss for each programme. Its expected cost, the capital it frees, the price of that capital and the net gain at the cost of capital; each layer's losses, exact and simulated.

ModelRisk techniques

Claim objects (Poisson, Pareto)
Layers VoseAggregateTranche
Exact layer pricing VoseDeductObject
Quota share
Four programmes, same claims

Find similar models

The results in detail

What it costs, and what it buys

Against no reinsurance: the expected result given up, and the 1-in-200 loss removed.

Programme	Cost US$m a year	Capital freed US$m	Break-even cost of capital	Net gain US$m a year	Worth it? at the CoC
Excess of loss only	1.69	21.06	8.0%	0.42	✔ yes
Quota share only	0.45	13.59	3.3%	0.91	✔ yes
Excess of loss and quota share	2.12	27.84	7.6%	0.67	✔ yes

Cost: the fall in the mean result. Capital freed: the rise in the 1-in-200 result. Break-even: cost ÷ capital freed, the cost of capital at which the programme pays for itself. Worth it when the cost of capital on the Inputs sheet is above it. Net gain: cost of capital × capital freed − cost.

Capital freed comes from two 1-in-200 results, 50 of 10,000 samples each: it moves by about US$1.5m from run to run, so a break-even close to the cost of capital can flip.

Where the claims go

Each part of the year's claims (US$m): its expected loss, exact, and the simulation.

Gross claims in the year — distribution

Histogram of Gross claims from the saved simulation.

Part of each claim	Expected exact	Mean simulated	95th pct 1-in-20	99.5th pct 1-in-200	Share of expected
Insurer's retention	24.84	24.87	33.55	39.22	82.8%
Layer 1	4.21	4.20	12.40	20.25	14.0%
Layer 2	0.62	0.62	4.41	15.00	2.1%
Above the programme	0.33	0.32	–	22.11	1.1%
Gross claims	30.00	30.01	46.77	76.27	100.0%

How to read these results

Every programme lowers the expected result: the reinsurers charge more than the claims they expect to pay. Here the two layers cost about US$1.7m a year and the quota share about US$0.5m; together about US$2.1m — more than half the expected profit.
In exchange, the bad years become far less bad. With no reinsurance, one year in 200 loses about US$43m; with both covers about US$15m. The capital the insurer must hold against its underwriting falls by about US$28m.
The layers act only on large claims: they take about a sixth of the expected claims, but most of what makes a year catastrophic. The quota share scales everything by the same share — the profit too. With a commission that covers the insurer's costs it is the cheapest capital here, but it frees less of it.
At a 10% cost of capital each programme is worth more than it costs. Lower the cost of capital or raise the layer prices on the Inputs sheet and the answer changes: reinsurance is a source of capital, to be bought when it is cheaper than shareholders' money.
Claims above the top of the programme come back to the insurer. They are rare — reached about once in 64 years — but in the 1-in-200 year the excess above US$25m is large. Check the top of the programme against the largest sums insured.

See it move. Open the workbook, change an input on the Inputs sheet and run the simulation: every table and chart on this page updates in Excel.

Download the Excel model Get the free ModelRisk trial

Inputs and assumptions

Every assumption in the model, with its uncertainty and where it comes from. The figures are illustrative.

Claims 4 inputs

How many claims a year, and how large.

Assumption	Unit	Value
Expected number of claimsPolicies in force × claim rate, from the claims history.	claims a year	60
Average claimClaims history, inflated to next year's prices.	US$ thousand	500
Tail of the claim sizes (Pareto shape)Fitted to the large claims (as in IN-02). Must be above 1.	lower = heavier	2.2
Example claim, to show how a claim is splitUsed only for the illustration on the Programme sheet.	US$ thousand	12,000

Excess-of-loss layers 5 inputs

Per-risk layers: each pays the part of every claim that falls inside it.

Assumption	Unit	Value
RetentionThe insurer keeps every claim up to this amount.	US$ thousand	2,000
Layer 1 limitLayer 1 pays the part of each claim from the retention up to the retention + this limit.	US$ thousand	8,000
Layer 2 limitLayer 2 starts where layer 1 ends. Set the top from the largest sums insured.	US$ thousand	15,000
Price of layer 1Hit every year: priced close to its expected loss.	× expected loss	1.25
Price of layer 2Rarely hit: the reinsurer charges more for the uncertainty.	× expected loss	2.00

Quota share 2 inputs

A fixed share of the premium and of the claims the insurer keeps after the layers.

Assumption	Unit	Value
Share of premium and claims cededAs proposed by the broker.		30%
Ceding commissionPaid back by the reinsurer for the insurer's cost of writing the business.	of ceded premium	25%

Premium, expenses and capital 3 inputs

Assumption	Unit	Value
Gross premiumWritten next year.	US$m	42.0
Expense ratioBrokers' commission and administration.	of gross premium	20%
Cost of capitalThe return shareholders require on the capital the insurer holds.	a year	10%

Units and conventions

Money: US$ million (US$m); claim sizes, retention and limits in US$ thousand.
Excess of loss: A layer '8,000 xs 2,000' pays the part of each claim above US$2m, up to US$8m: the slice of every claim between US$2m and US$10m.
Quota share: The reinsurer takes a fixed share of the premium and of the claims, and pays back a ceding commission on its share of the premium.
Result: Underwriting result = premium − expenses − reinsurance premiums + commission − claims kept; before investment income and tax.
1-in-200: The 0.5th percentile of the result: the loss exceeded in one year in 200, on which solvency capital is based.

Key assumptions and limitations

Claims: Claim numbers are Poisson; claim sizes are independent and Pareto (type 2) — heavy-tailed.
Layers: Each layer pays every claim that reaches it (unlimited free reinstatements); the part of a claim above the top layer stays with the insurer.
Quota share: It shares what the insurer keeps after the layers' recoveries, and pays for its share of the layers' price.
Capital: The capital needed is driven by the 1-in-200 underwriting result alone: no other risks, no diversification.
Data: The book and all figures are invented for this example.

How to use it

Enter your claims assumptions, the layers your broker quotes and their prices.
Press F9 on the Programme sheet to see how other years' claims are shared.
Run a simulation from the ModelRisk ribbon (10,000 samples with a fixed seed are set).
Compare each programme's cost with the capital it frees, at your own cost of capital.

ModelRisk functions used

VosePoissonObject, VosePareto2Object: The claim number and claim size distributions as objects.
VoseDeductObject, VoseMean: The amount a layer pays on one claim, and its exact mean — used to price the layer.
VoseProb: The chance that a claim reaches a layer.
VoseAggregateTranche: Adds up, over every claim of the year, the part that falls in each layer (an array over four cells).
VoseSimMean, VoseSimStdev, VoseSimPercentile, VoseSimProbability: Statistics of the simulation on the Results sheet.

Related example models

All example models

An illustrative example: the figures are invented for teaching and do not describe a real company, project or asset. Last updated 9 October 2026.