Merging claims listings into a loss development triangle

A loss development triangle is the schedule actuaries use to see how losses from a fixed accident period mature as later valuations arrive. Rows are origin periods. Columns are development ages. The cell C_i,k is cumulative paid loss for accident year i observed at age k. Cells already valued form the upper triangle. Reserve methods fill the lower triangle by carrying each origin forward with age-to-age factors. The chain-ladder method is the usual reading of that array.

The inputs are claims listings, one Excel workbook per evaluation year. Each row is a claim, with accident_year, paid-to-date in paid, and file_year for the year of that valuation. Listings are not required to share unused columns. A later file can carry extra fields, and the merge keeps only the three columns that define the triangle. Stacking those snapshots in R replaces a workbook of cross-file links that has to be rewired at every new evaluation.

Development age is the lag from the accident year to the valuation year. For annual valuations the lag in months is (file_year − accident_year) × 12, so a 2011 accident valued in 2011 sits at age 0 and the same accident valued in 2012 sits at age 12. Paid in each listing is paid-to-date at that file_year, not the incremental payment since the previous file. Summing paid over claims that share an accident year and a development age produces the cumulative cell C_i,k. The same origin then occupies one column per valuation instead of being overwritten.

Age-to-age factors compare cumulative paid at successive ages that appear in the triangle. The development ages are the month lags themselves, not a unit index, so the columns of this construction are 0, 12, 24, and so on when valuations are annual. Write the observed ages as d₁ < d₂ < … < dₘ. For each consecutive pair, let Sⱼ be the accident years for which both Cᵢ,dⱼ and Cᵢ,dⱼ₊₁ are present. The volume-weighted link ratio is fⱼ = (Σᵢ∈Sⱼ Cᵢ,dⱼ₊₁) / (Σᵢ∈Sⱼ Cᵢ,dⱼ). The cumulative development factor from age dⱼ to the last observed age is the product fⱼ fⱼ₊₁ … fₘ₋₁. An origin missing an intermediate valuation stays out of any factor that needs the missing cell.

The script selects file_year, accident_year, and paid from every workbook with readxl, stacks the frames with ldply, and attaches maturity_in_months. ChainLadder::as.triangle is called with origin = "accident_year", dev = "maturity_in_months", and value = "paid". Its default aggregation sums paid inside each origin-age cell, which is the map from a claim listing to C_i,k. Link ratios and ultimates are a later call to ata and cdf on that triangle. The workbooks and Final_Article.Rmd are in this repository; a longer walkthrough is on DataScience+.