The is the most-used reserving method in (GI). By the end of this lesson you will have run it three times: by hand, in Excel and in Python.
The worked example is a small of cumulative paid claims in £000s. Each row is an (all the claims from accidents that happened in that year, whenever they end up paid), and reading across it shows those claims growing as the year ages from 12 to 24 to 36 months. The volume-weighted , the multipliers that carry a year from one age to the next, are 1.50 (12–24 months) and 1.10 (24–36), and no development is assumed after 36 months.
| 12 months | 24 months | 36 months | |
|---|---|---|---|
| 2023 | 400 | 600 | 660 |
| 2024 | 450 | 675 | |
| 2025 | 480 |
By hand
in one sentence: take each 's latest figure and multiply it through every it has not yet been through. What comes out is an estimate of that year's : what its claims will have cost in total once they are all .
- AY2023 ( 2023) is at 36 months, so nothing is left. Ultimate = 660.
- AY2024 is at 24 months, so one remains: 675 × 1.10 = 742.5.
- AY2025 is at 12 months, so both remain: 480 × 1.50 × 1.10 = 792.
The is what still has to be paid: minus paid to date.
| Paid to date | |||
|---|---|---|---|
| 2023 | 660 | 660.0 | 0.0 |
| 2024 | 675 | 742.5 | 67.5 |
| 2025 | 480 | 792.0 | 312.0 |
| Total | 1,815 | 2,194.5 | 379.5 |
Total £379.5k, of which £312k (over 80%) sits in the youngest year (2025). That concentration is normal: the youngest year has the most development still ahead of it. It is also where the method is least reliable. (AY2023's of zero is an artefact of our no-tail assumption; a genuinely , one still paying claims many years after the accidents, would carry development well past 36 months.)
Because this is a paid triangle, the £379.5k covers everything still to be paid: (the claims department's own estimate for each claim it knows about) and (claims that have happened but not yet reached the insurer, plus expected growth on the ones it already has) together. The the company books is therefore £379.5k minus whatever the claims department already holds in . Run the same ladder on an incurred triangle (paid plus ) and its excess over current incurred is the directly. In practice teams project both and interrogate the gap.
In Excel
A reserving team keeps the triangle in a workbook, because a workbook is what the reviewing actuary, the auditors and the reserving committee can all open. Build it the way a team keeps it: the sheet derives the , the reference the ratio cells, and nothing below the triangle is a typed-in number.
Spreadsheet: build it, don’t type it
The ladder, as a reserving team keeps it
The triangle at the top is the lesson's: cumulative paid claims in £000s, locked. Everything else is yours, and every marked cell must be a formula. WORKINGS. B8 and B9: the volume-weighted link ratios (actuaries also call them development factors). Volume-weighted means you total the column you are moving to, total the column you came from over the same accident years, and divide. A year only takes part in a link ratio if it has both figures. RESULTS. B12 to B14: each accident year's ultimate: its latest cumulative figure multiplied through every link ratio it has not yet been through. Reference the ratio cells; never retype a ratio. Marked cells: B8, B9, B12, B13, B14.
Cells to fill: B8, B9, B12, B13, B14
- Given data, locked
- Yours to fill
| Row | A | B | C | D | E | F |
|---|---|---|---|---|---|---|
| 1 | INPUTS: cumulative paid claims (£000s) | |||||
| 2 | Accident year | 12 months | 24 months | 36 months | ||
| 3 | 2023 | 400 | 600 | 660 | ||
| 4 | 2024 | 450 | 675 | |||
| 5 | 2025 | 480 | ||||
| 6 | ||||||
| 7 | WORKINGS: volume-weighted link ratios | |||||
| 8 | 12 to 24 months | |||||
| 9 | 24 to 36 months | |||||
| 10 | ||||||
| 11 | RESULTS: ultimate (£000s) | |||||
| 12 | 2023 | |||||
| 13 | 2024 | |||||
| 14 | 2025 | |||||
| 15 | ||||||
| 16 | ||||||
| 17 |
How this grid works: typing, filling, references, layout
Moving and selecting
- Move a cell at a time
- : arrow keys
- Run to the end of a block
- : Ctrl+arrow
- Back to A1, or out to the last cell used
- : Ctrl+Home / Ctrl+End
- Select a range
- : Shift+arrow, or shift-click the far corner
- Select to the end of a block
- : Ctrl+Shift+arrow
- Take a whole row, or a whole column
- : Shift+Space / Ctrl+Space, or click its header
- Take several rows or columns
- : drag along the headers, or shift-click
- Take the lot
- : Ctrl+A
Entering and editing
- Start an entry
- : just type, or use the formula bar
- Commit it and move down, or up
- : Enter / Shift+Enter
- Commit it and move right
- : Tab
- Change your mind mid-entry
- : Escape
- Open what is already in the cell
- : F2, or double-click it
- Empty the selected cells
- : Delete
- Find a function, then its arguments
- : start typing the name; the open bracket lists the arguments in order, with the one you are writing picked out
Filling and copying
- Fill a formula down the column
- : Ctrl+D, or drag the small square at the corner of the selection
- Fill it right along the row
- : Ctrl+R
- Copy, or cut
- : Ctrl+C / Ctrl+X
- Paste it, references moving as they go
- : Ctrl+V
- Paste the numbers instead of the formulas
- : Ctrl+Shift+V
- Fill a whole block from one cell
- : copy it, select the block, paste
- The same four with a mouse
- : right-click a cell: the shortcuts are printed beside them
References
- Put a cell into a formula without typing its address
- : click it, or press an arrow after =, a bracket or an operator
- Grow that reference into a range
- : Shift+arrow, or drag across the cells
- Stop a reference shifting when the formula copies
- : F4, which adds the dollar signs
- Give the arrow keys back to the text
- : F2 swaps them between picking cells and moving the cursor
- See the range you picked before you commit it
- : each reference takes a colour and outlines the cells it points at; the same one twice keeps its colour
- See what a finished formula reads
- : select its cell: the cells it reads are outlined
Rows and columns
- Make a column wider, or a row taller
- : drag the line between two headers, or Alt+Shift+arrow
- Fit it back to what is in it
- : double-click that line, or Alt+Shift+0
- More room to work in
- : the blank rows and columns past the data, and the Add rows and Add columns buttons
Columns start as wide as what is in them, and nothing you do out in the blank space is marked.
The view
- Zoom in or out
- : Ctrl++ / Ctrl+-, the buttons above the grid, or Ctrl with the wheel
- Back to how it was drawn
- : Ctrl+0, or Reset view
- Find out what a shade means
- : the key above the grid, which lists only the shades this exercise uses
Zoom, widths and heights are how you are looking at the grid, not what is in it. None of it is marked.
Have a go and press Check. The worked solution opens up after your first real attempt.
In Python
Real triangles are bigger (ten or twenty development years, often at quarterly ages) and get rebuilt every quarter, which is why the Excel workbook's arithmetic ends up in code. The runner below does the same calculation. Press Run.
Now change AY2025's paid figure from 480 to 520 and run again. The do not move: AY2025 has no 24-month value yet, so it takes no part in either ratio. But its jumps from 792 to 858: £40k of extra paid claims became £66k of extra , because the multiplied the news by 1.50 × 1.10 = 1.65. The method reads every pound paid so far as evidence the year is bigger, never as the same-sized year paying out faster. On a one-year-old cohort that assumption carries serious leverage. Managing it is exactly what the method is for, and the next lesson teaches it.
What the professionals run
The industrial tools are recognisably doing the same thing as those twenty lines. Most UK GI reserving teams run a dedicated package. WTW's ResQ, which WTW says is used by more than 500 companies, is the market staple; Python-first teams increasingly use chainladder-python, an open-source library maintained under the Casualty Actuarial Society's GitHub organisation. Both add tail fitting, diagnostics and stochastic methods on top, but the engine underneath is the loop you just ran. Nobody expects you to have used ResQ before you start: it is licensed software, taught on the job.
Your turn: the ladder, with a tail
The code above assumes nothing develops after 36 months. A real keeps going: a liability class can still be settling claims at development year fifteen, long past the edge of the triangle above. The fix is a : one more multiplier, applied to every , standing in for all the development beyond the data.
Write the ladder yourself and make the tail a parameter. The tail is the assumption on a reserving exercise that gets argued about most, and an assumption welded inside a function is one nobody can sensitivity-test in a meeting.
Chain ladder with a tail factor
TRIANGLE is the same cumulative paid triangle as above, one list per accident year, ragged because the young years have fewer observations. Write two functions. link_ratios(triangle) returns the volume-weighted link ratios as a list: for this triangle, the 12-to-24 ratio then the 24-to-36 ratio. Volume-weighted means sum each pair of columns first, then divide. Only accident years that have both figures may take part in a link ratio. ultimates(triangle, tail=1.0) returns a list with one entry per accident year, in the triangle's order: the year's projected ultimate, meaning its latest cumulative figure multiplied through every link ratio it has not yet been through, then multiplied by the tail. The tail applies to every year, including the oldest one: 'fully developed to 36 months' is not the same as 'finished'. Derive the link ratios inside the function. One test runs the whole thing on a completely different triangle, and a function that knows this one's answers will fail it.