This notebook contains a simple chain-ladder calculator written in Julia. The chain-ladder method is used by insurers to forecast the amount of reserves required to cover future (incurred by not reported) claims.

Install/import packages

using Pkg
Pkg.add(["DataFrames", "Statistics", "XLSX"]);
using DataFrames, Statistics, XLSX

   Resolving package versions...
     Project No packages added to or removed from `C:\Users\lblevins\.julia\environments\v1.12\Project.toml`
    Manifest No packages added to or removed from `C:\Users\lblevins\.julia\environments\v1.12\Manifest.toml`

reported_claims = DataFrame(XLSX.readtable("Reported Claims.xlsx", "Data"))

5×5 DataFrame

Row	Year 0	Year 1	Year 2	Year 3	Year 4
	Any	Any	Any	Any	Any
1	8050.11	1341.67	268.33	57.09	11.65
2	5224.46	725.55	172.75	44.29	missing
3	7942.76	1118.59	219.33	missing	missing
4	8022.77	1215.45	missing	missing	missing
5	7762.01	missing	missing	missing	missing

Calculate cumulative claims

cumulative_claims  = DataFrame(cumsum(Matrix(reported_claims), dims=2), names(reported_claims))

5×5 DataFrame

Row	Year 0	Year 1	Year 2	Year 3	Year 4
	Any	Any	Any	Any	Any
1	8050.11	9391.78	9660.11	9717.2	9728.85
2	5224.46	5950.01	6122.76	6167.05	missing
3	7942.76	9061.35	9280.68	missing	missing
4	8022.77	9238.22	missing	missing	missing
5	7762.01	missing	missing	missing	missing

Calculate age-to-age factors

Create empty dataframe to hold age-to-age factors, using headings from above dataframe

column_titles_list = names(cumulative_claims)

new_columns = [
    Symbol("$(a) to $(b)")
    for (a, b) in zip(column_titles_list[1:end-1], column_titles_list[2:end])
]

age_to_age = DataFrame([col => Float64[] for col in new_columns]...)

0×4 DataFrame

Row	Year 0 to Year 1	Year 1 to Year 2	Year 2 to Year 3	Year 3 to Year 4
	Float64	Float64	Float64	Float64

Add data into age-to-age dataframe

ratio_list = Float64[]

df_width = size(cumulative_claims, 2) - 1 

for i in 1:df_width
    col1 = cumulative_claims[!, i]
    col2 = cumulative_claims[!, i+1]

    mask = .!(ismissing.(col1) .| ismissing.(col2))
    col1_clean = col1[mask]
    col2_clean = col2[mask]

    col1_total = sum(col1_clean)
    col2_total = sum(col2_clean)

    push!(ratio_list, col2_total / col1_total)
end

push!(age_to_age, ratio_list)

1×4 DataFrame

Row	Year 0 to Year 1	Year 1 to Year 2	Year 2 to Year 3	Year 3 to Year 4
	Float64	Float64	Float64	Float64
1	1.15052	1.02706	1.00642	1.0012

Calculate run-off triangle

run_off = deepcopy(cumulative_claims)   

for i in 1:(length(column_titles_list) - 1)
    current_column = column_titles_list[i]
    next_column    = column_titles_list[i + 1]

    factor = age_to_age[1, i]   

    run_off[!, next_column] = coalesce.(run_off[!, next_column],
                                        run_off[!, current_column] .* factor)
end

run_off = round.(run_off, digits=2)

5×5 DataFrame

Row	Year 0	Year 1	Year 2	Year 3	Year 4
	Float64	Float64	Float64	Float64	Float64
1	8050.11	9391.78	9660.11	9717.2	9728.85
2	5224.46	5950.01	6122.76	6167.05	6174.44
3	7942.76	9061.35	9280.68	9340.29	9351.49
4	8022.77	9238.22	9488.23	9549.18	9560.62
5	7762.01	8930.36	9172.04	9230.95	9242.02

Calculate incurred but not reported claims

# reverse to make indexing slightly simplier
run_off_reverse = run_off[:, reverse(1:end)]

num_rows = nrow(run_off_reverse)

ibnr   = Float64[]
period = copy(run_off_reverse[!, 1])

for i in 1:num_rows
    period[i] = run_off_reverse[i, 1]                     
    push!(ibnr, run_off_reverse[i, 1] - run_off_reverse[i, i])
end

ibnr_df = DataFrame(Period = period, IBNR = ibnr)

5×2 DataFrame

Row	Period	IBNR
	Float64	Float64
1	9728.85	0.0
2	6174.44	7.39
3	9351.49	70.81
4	9560.62	322.4
5	9242.02	1480.01

println("Total IBNR is: ", round(sum(ibnr_df.IBNR), digits=2))

Total IBNR is: 1880.61