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.
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.
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.
Inputs are in blue; everything else is a formula; the checks count the failures. Unused rows and columns stay empty and change nothing.
Cumulative reported losses entered on the Inputs tab (the paid triangle sits beside it):
| Accident year | 12 months | 24 months | 36 months | 48 months | 60 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.
| Method (five years together) | Ultimate | IBNR |
|---|---|---|
| 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.
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.
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.
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.
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.
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.
No. Every factor comes from the triangles you enter, and the tail is yours to type and support.
Yes. You enter an email address, the download opens at once, and you can leave the reader list in one click.
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.