This notebook contains a simple chain-ladder calculator written in Python. The chain-ladder method is used by insurers to forecast the amount of reserves required to cover future (incurred by not reported) claims.¶
In [1]:
import pandas as pd
import numpy as np

Import reported claims data¶

In [2]:
reported_claims = pd.read_excel("Reported Claims.xlsx", index_col=0)
reported_claims
Out[2]:
Year 0 Year 1 Year 2 Year 3 Year 4
2019 8050.11 1341.67 268.33 57.09 11.65
2020 5224.46 725.55 172.75 44.29 NaN
2021 7942.76 1118.59 219.33 NaN NaN
2022 8022.77 1215.45 NaN NaN NaN
2023 7762.01 NaN NaN NaN NaN

Calculate cumulative claims¶

In [3]:
cumulative_claims = reported_claims.cumsum(axis=1)
In [4]:
cumulative_claims
Out[4]:
Year 0 Year 1 Year 2 Year 3 Year 4
2019 8050.11 9391.78 9660.11 9717.20 9728.85
2020 5224.46 5950.01 6122.76 6167.05 NaN
2021 7942.76 9061.35 9280.68 NaN NaN
2022 8022.77 9238.22 NaN NaN NaN
2023 7762.01 NaN NaN NaN NaN

Calculate age-to-age factors¶

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

In [5]:
column_titles_list = cumulative_claims.columns.tolist()
new_columns = [f"{column_titles_list[i]} to {column_titles_list[i+1]}" for i in range(len(column_titles_list)-1)]
age_to_age = pd.DataFrame(columns=new_columns)
In [6]:
age_to_age
Out[6]:
Year 0 to Year 1 Year 1 to Year 2 Year 2 to Year 3 Year 3 to Year 4

Add data into age-to-age dataframe¶

In [7]:
ratio_list = []

df_width = cumulative_claims.shape[1]-1

# loop through columns, comparing neighboring two
for i in range(0, df_width):
    col_1 = cumulative_claims.iloc[:, i]
    col_2 = cumulative_claims.iloc[:, i+1]
    
    # filter out rows where col_1 or col_2 are null
    both_na = col_1.isna() | col_2.isna()
    col_1 = col_1[~both_na]
    col_2 = col_2[~both_na]
    
    # calculate totals and divide them
    col_1_total = col_1.sum()
    col_2_total = col_2.sum()
    ratio = col_2_total / col_1_total
    ratio_list.append(ratio)

age_to_age.loc[len(age_to_age)] = ratio_list
In [8]:
age_to_age
Out[8]:
Year 0 to Year 1 Year 1 to Year 2 Year 2 to Year 3 Year 3 to Year 4
0 1.150521 1.027063 1.006423 1.001199

Calculate run-off triangle¶

In [9]:
run_off = cumulative_claims.copy()
In [10]:
for i in range(len(column_titles_list) - 1):
    next_column = column_titles_list[i + 1]
    current_column = column_titles_list[i]
    run_off[next_column] = run_off[next_column].fillna(run_off[current_column] * age_to_age.iloc[0, i])
    
run_off = run_off.round(2)
In [11]:
run_off
Out[11]:
Year 0 Year 1 Year 2 Year 3 Year 4
2019 8050.11 9391.78 9660.11 9717.20 9728.85
2020 5224.46 5950.01 6122.76 6167.05 6174.44
2021 7942.76 9061.35 9280.68 9340.29 9351.49
2022 8022.77 9238.22 9488.23 9549.18 9560.62
2023 7762.01 8930.36 9172.04 9230.95 9242.02

Calculate incurred but not reported claims¶

In [12]:
# reverse to make indexing slightly simplier
run_off_reverse = run_off.iloc[:, ::-1]

num_rows = run_off_reverse.shape[0]

ibnr = []
period = []

for i in range (0, num_rows):
    period.append(run_off_reverse.index[i])
    ibnr.append(run_off_reverse.iloc[i,0] - run_off_reverse.iloc[i,i])

ibnr = pd.DataFrame({'Period': period, 'IBNR': ibnr})
In [13]:
ibnr
Out[13]:
Period IBNR
0 2019 0.00
1 2020 7.39
2 2021 70.81
3 2022 322.40
4 2023 1480.01
In [14]:
print("Total IBNR is: " + str(round(ibnr['IBNR'].sum(),2)))

Total IBNR is: 1880.61