Free Excel templates

A free loss reserving calculator in Excel

Paid and reported triangles turned into age-to-age and age-to-ultimate factors, then into the ultimates and IBNR of the chain ladder, the expected loss ratio method, Bornhuetter-Ferguson and Cape Cod, year by year, with the checks at zero.

Five accident years of a small book, evaluated at December 31, 2025, an expected loss ratio of 0.800 and two declared tails. The reported chain ladder says $7,303,557, the paid chain ladder $7,034,894, the expected loss ratio method $7,168,000; Bornhuetter-Ferguson and Cape Cod land in between, and the Cape Cod ratio computed from the data, 81.37 percent, sits just above the ratio the book was priced at. The calculator lays the five methods out side by side, ready for your own triangles.

Get the calculator

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

Loss_Reserving_Calculator.xlsx (20 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 calculator

This calculator is a simplified version of the companion workbook of The Loss Reserving Handbook, which follows one regional insurer through a full year-end review: a claims registry turned into triangles, diagnostics before methods, declared exclusions and tails, seven method totals, an estimate selected by accident year with its range, ALAE and ULAE, the next evaluation compared with expected, and a captive.

Paperback PDF edition, $19.99

What is in the file

Inputs are in blue; everything else is a formula; the checks count the failures. Unused rows and columns stay empty and change nothing.

1. Start here
What the calculator does, the conventions it applies, what it leaves out, and a note that every example figure is a teaching assumption, not a market or industry figure.
2. Inputs
Cumulative paid and reported triangles of up to ten accident years and ten ages, earned premium by accident year, the expected loss ratio, the choice of average (simple, weighted, or the latest years) and the paid and reported tails.
3. Calculator
Link ratios, the selected factors, the age-to-ultimate factors, then the ultimate, the IBNR and the unpaid losses of each method and each year, with the Cape Cod ratio and used-up premium.
4. Checks
The controls of the calculator, each showing a gap that must read 0, and a count of the failures.

The worked example

Cumulative reported losses entered on the Inputs tab (the paid triangle sits beside it):

Accident year12 months24 months36 months48 months60 months
2021$812,000$1,104,000$1,236,000$1,281,000$1,296,000
2022$845,000$1,162,000$1,301,000$1,349,000
2023$903,000$1,228,000$1,377,000
2024$951,000$1,297,000
2025$998,000

Earned premium: 2021 $1,640,000; 2022 $1,705,000; 2023 $1,790,000; 2024 $1,870,000; 2025 $1,955,000. Expected loss ratio 0.800, weighted averages of all years, reported tail 1.010, paid tail 1.040.

What the worked example returns

Method (five years together)UltimateIBNR
Paid chain ladder$7,034,894$717,894
Reported chain ladder$7,303,557$986,557
Expected loss ratio method$7,168,000$851,000
Reported Bornhuetter-Ferguson$7,274,657$957,657
Reported Cape Cod$7,291,057$974,057

The Cape Cod ratio is 81.37 percent and every check of the control block reads 0. The example book is fictional and every input is a teaching assumption. The calculator is separate from the book's case: read its figures against each other, not against the book's insurer.

How to use it on your own data

  1. Enter cumulative values, not increments, with one row per accident year and the oldest year on top.
  2. Keep paid and reported on the same accident years and the same evaluation date, and leave every cell beyond that date empty.
  3. Enter earned premium for each year and an expected loss ratio you can explain, from pricing or from mature years brought to the current level.
  4. Choose the average and type the tails; write down why.
  5. Read the Checks tab before any figure: a count above zero means a cell to fix.

Methods as described in a Casualty Actuarial Society study text on basic techniques for estimating unpaid claims (Version 3, 2010); ASOP No. 43 (Actuarial Standards Board) as read in November 2026. Education only, not actuarial advice and not a statement of actuarial opinion: the data of your own book govern, and a qualified actuary decides.

Questions people ask

What does the loss reserving calculator do?

From cumulative paid and reported triangles of up to ten accident years, earned premium, an expected loss ratio, a choice of average and a tail, it returns the age-to-age and age-to-ultimate factors, the ultimates of the paid and reported chain ladder, the expected loss ratio method, Bornhuetter-Ferguson and Cape Cod, with the Cape Cod ratio, and the IBNR and unpaid losses of each method, year by year.

Which methods and conventions does it use?

The basic methods as described in a Casualty Actuarial Society study text on basic techniques: factors rounded to three decimals, age-to-ultimate factors as products of the rounded factors and the tail, dollars rounded half up. The average is chosen on the Inputs tab: simple, weighted or the latest years.

What does it leave out?

ALAE and ULAE, case reserve and settlement adjustments, claim counts, large claim treatment, reinsurance, discounting and any range. The book's full workbook covers most of them on one case.

Can I rely on it for a booked reserve or an actuarial opinion?

No. It is built for a first pass and for learning the mechanics. As read in November 2026, ASOP No. 43 asks the actuary to consider methods, generally more than one; the estimate and any opinion belong to a qualified actuary.

Does it use industry factors?

No. Every factor comes from the triangles you enter, and the tail is yours to type and support.

Is this calculator 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 workbook of The Loss Reserving Handbook goes further: a claims registry summed into triangles, diagnostics, declared exclusions for a large claim, six averages per age, a supported tail, paid and incurred versions of every method, a selection by accident year with its range, ALAE and ULAE, case strengthening and faster settlement scenarios, actual versus expected and a captive.

See the book and its free files →

Related reading