Free Excel templates

A free reinsurance model in Excel, from quota share to cat XL

A reinsurer's underwriting year in sixteen sheets: quota share with a sliding scale commission, surplus and stop loss, a facultative certificate, a four-layer catastrophe programme, an event with reinstatements, burning cost, cat model pricing, a sidecar and solvency.

A 30% quota share on a EUR 400 million book cedes EUR 120 million of premium. The sliding scale commission moves from 31.50% to 30.25% as the loss ratio estimate rises, and the cedant owes EUR 2,100,000 back. The treaty runs at a 94.75% combined ratio.

Get the template

One Excel file, no macros, nothing locked. Free: enter your email address and the download opens at once, along with the other 129 free models.

Reinsurance_Model_Quota_Share_to_Cat_XL.xlsx (54 KB)

You join the reader list: one email when a new free model or a new book comes out, and a few notes on using the models. Leave in one click. Already on the list? Enter the same address. How your address is used.

The book behind this model

This model goes with The Reinsurance Professional, which explains the method behind it, chapter by chapter.

Get the book on Apple Books → Or on Google Play

Want every model at once?

The Financial Model Library: all 130 models from this site in one download, organised by theme, plus two modelling books in DRM-free PDF (Financial Modeling from First Principles and Real Estate Financial Modeling).

Get the Library, $79 →

This free file stays free. Secure checkout by Stripe.

Not on Amazon: 24 paperbacks by Julian R. Sterling on insurance and retirement, printed on demand and shipped worldwide. Browse the bookshop →

What is in the file

16 sheets. Inputs can be changed; everything else is a formula, and the last sheet checks the figures against the book.

Inputs
Every hard-coded figure: Corbelle's terms and loss ratio estimates, the surplus and stop loss illustrations, the facultative certificate, Veldhaven's four layers for 2025 and 2026, reinstatement terms, brokerage, the event loss, the exceedance curve and the as-if history, the pricing loads and weights, the segment totals, the sidecar, the retro cover and the capital figures. Change it here only, one cell at a time, and watch the Checks sheet.
Corbelle Quota Share
Ceded premium, paid and reserved losses, brokerage, commission, result and combined ratio.
Sliding Scale Commission
The commission at each loss ratio estimate and on a scale of loss ratios, held between its minimum and maximum, the provisional amount and the adjustment, with who pays whom.
Surplus and Stop Loss
Line, number of lines, capacity, retained, ceded and uncovered amounts for three risks; the stop loss attachment, exhaustion, recovery and retained loss.
Facultative Certificate
Layer premium as rate on line times limit, Lowmere's premium and its line.
Veldhaven Program 2025
Each layer's top, premium at 100 percent, payback, Lowmere's premium and line, and its total line against the authority.
Event and Reinstatements
Loss to each layer, reinstatement premiums pro rata as to amount, Lowmere's shares, the netted payment, Veldhaven's cost and Lowmere's account on the program.
Burning Cost
The as-if history cut into each layer year by year, summed, divided by ten and expressed against the limit.
Cat Model and Layer Loss
The exceedance curve turned into annual event rates, log-linear between its points, with Poisson event counts; each layer's expected loss, standard deviation and probabilities of attachment and exhaustion; the event's return period.
Technical Price 2026
Blended expected loss, risk load, gross-up for brokerage and internal expenses, technical rate on line beside the firm order, and Lowmere's 2026 premium.
Brisant Sidecar
Ceded premium, the sidecar's share of brokerage, override, ceded losses, expenses, profit before and after the profit commission, the investors' return and the Veldhaven recovery.
Sidecar Trust
Collateral plus net cash premium, less expenses and paid losses; the amount kept under the collateral rule; the release; the counterfactual payment at commutation.
Retro Excess of Loss
Premium, the 1-in-200 loss after the sidecar and after the retro cover, the net figure against own funds and the risk appetite, and whether the Veldhaven event reaches the attachment.
Underwriting Year Results
Segments, gross and net totals and ratios, the cost of the retrocession, result before tax and return on allocated capital.
Solvency and Capital
The solvency ratio with and without the sidecar, the SCR relief, the surplus and the rescaled risk margin. The SCR itself is an input from the capital manager, not a calculation.
Checks
The figures printed in the book beside the formulas that produce them, and structural reconciliations. With the original inputs every line reads OK.

What the worked case returns

MeasureValue
Quota share: ceded premiumEUR 120,000,000
Quota share: ceded lossesEUR 76,200,000
Provisional commissionEUR 38,400,000
Commission on the scaleEUR 36,300,000
Combined ratio of the treaty94.75%
Surplus treaty capacityEUR 18,000,000
Stop loss recoveryEUR 13,000,000
Facultative premium for the layerEUR 4,200,000
Cat layer 1: premiumEUR 7,200,000
Cat layer 1: payback (years)5.6
Cat layer 1: loss to the layerEUR 40,000,000
Cat layer 1: reinstatement premiumEUR 7,200,000
Cat programme premiumEUR 21,100,000

Payback, the limit divided by the premium, rises from 5.6 years on the first cat layer to 36.4 on the fourth: the higher the layer, the rarer the loss and the cheaper the cover per unit of limit.

How to use it on your own case

  1. Enter treaty terms on Inputs: shares, premiums, commissions, lines, layers and rates on line.
  2. Read each structure on its own sheet: quota share, sliding scale, surplus, stop loss, facultative.
  3. Run the catastrophe programme through an event, with reinstatement premiums, and price it from burning cost and the cat model.
  4. Follow the year to results, the sidecar and the solvency ratio.

Questions people ask about it

What is the difference between quota share and excess of loss?

A quota share cedes a fixed share of every policy's premium and losses. Excess of loss pays only the part of a loss above an attachment point, up to a limit.

How does a sliding scale commission work?

The reinsurer pays a provisional commission, then adjusts it as the loss ratio develops: lower losses earn the cedant more commission, between a minimum and a maximum.

What is a reinstatement premium?

After a loss uses up part of a cat layer's limit, the cedant pays an extra premium, often pro rata to the amount used, to restore the cover.

Is this template really free?

Yes. You enter an email address, the download opens at once, and you can leave the reader list in one click.

Go further

The free workbooks of The Reinsurance Professional go further: the full companion set, blank models and the worked cases of the book.

See the book and its free files →

Other free templates

This template goes with The Reinsurance Professional. See the book.