Actuarial work in spreadsheets

Why it matters

Actuaries use Python, R and SQL more every year, but spreadsheets are still everywhere: rate indications, reserve reviews, pension valuations and quick checks are often built, reviewed and handed over in Excel. A reviewer must be able to follow every number. This lesson uses the most famous actuarial spreadsheet of all, the loss development triangleA table of cumulative losses with accident years as rows and development ages as columns. and the chain ladderA reserving method that projects each accident year to ultimate using development factors from the triangle. method, to show how to build a clear, checkable model. It connects directly to the P&C reserves from module 2.

The loss development triangle

Claims take time to settle. A car accident in 2025 might be paid partly in 2025, more in 2026 and the last of it in 2028. Arrange cumulative paid losses by accident year (rows) and development age (columns, in months since the start of the accident year):

(Amounts in thousands.) Each diagonal is a calendar year. The bottom-right is empty because those ages have not happened yet. The reserving question is: what will each row grow to (its ultimateThe final amount an accident year's losses are expected to reach. loss)?

The chain ladder method

1. Age-to-age factorsRatios of cumulative losses at one development age to the previous age, here volume-weighted.. For each pair of ages, divide the sum of the later column by the sum of the earlier column, using only rows that have both (the volume-weighted average):

12-24: (1,500 + 1,700 + 1,750) / (1,000 + 1,100 + 1,200) = 4,950 / 3,300 = 1.5000
24-36: (1,650 + 1,870) / (1,500 + 1,700)                 = 3,520 / 3,200 = 1.1000
36-48: 1,700 / 1,650                                      =                 1.0303

2. Cumulative development factors (CDFs). Multiply the factors from each age to the end. We assume no development after 48 months (a tail factorA factor for development after the last age in the triangle; 1.0 means no further development. of 1.0; real reviews estimate one):

CDF 48 = 1.0000
CDF 36 = 1.0303
CDF 24 = 1.1000 * 1.0303 = 1.1333
CDF 12 = 1.5000 * 1.1333 = 1.7000

3. Ultimates and reserves. Multiply each row's latest value by its CDF. The unpaid (reserve) is ultimate minus paid to date:

The insurer needs about 1.2 million held for these claims. Most of it is for the most recent year, whose estimate is also the least certain: a small change in the 12-24 factor moves it a lot. Methods such as Bornhuetter-Ferguson (which blends the chain ladder with an expected loss ratio, a credibility idea) are used to stabilise recent years.

Spreadsheet formulas

With the triangle in B2:E5 (rows = years, columns = ages):

12-24 factor (B8):   =SUM(C2:C4)/SUM(B2:B4)
24-36 factor (C8):   =SUM(D2:D3)/SUM(C2:C3)
36-48 factor (D8):   =E2/D2
CDF at 12 (B9):      =PRODUCT(B8:D8)*tail

Put every assumption (tail factor, any selected factor that overrides the average) in its own clearly labelled input cellA clearly labelled cell that holds an assumption, referenced by formulas instead of typing numbers into them., never typed inside a formula.

Good spreadsheet practice

• Separate inputs, calculations and outputs, ideally on different tabs, with inputs shaded one colour.
• No hard-coded numbers in formulas. =C2*1.0303 hides an assumption; =C2*$D$9 points to it.
• Check totals. Add a reconciliation: total ultimate minus total paid must equal total unpaid. Show a check cell that says OK or ERROR.
• Document. A notes tab with the data source, date, method and who reviewed it. ASOP 41 (actuarial communications) expects that another actuary could follow your work.
• Version and lock. Save dated versions; protect formula cells so they are not overwritten.
• Know when to move to code. Large triangles, many segments or stochastic methods (bootstrapping, Mack's model) are easier to reproduce in Python or R, such as the open-source chainladder Python package.

Common mistakes

• Averaging the individual ratios with a simple mean when the volume-weighted average was intended (or the reverse), without saying which.
• Using rows that lack the later age in a factor's denominator.
• Forgetting the tail. Long-tailed lines (liability, workers' compensation) develop for many years.
• Mixing paid and incurred data in one triangle.

Quick recap

• Triangles arrange cumulative losses by accident year and development age.
• Chain ladder: age-to-age factors, then CDFs, then ultimate = latest x CDF, unpaid = ultimate - paid.
• Build spreadsheets with separate inputs, no hard-coded numbers, check cells and notes.

Try it

The Do tab walks you through building this triangle and reserve estimate in a free spreadsheet, with a check that the total unpaid is 1,200.