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

Import libraries

library(readxl)
library(dplyr)

Import data

reported_claims <- read_excel("Reported Claims.xlsx")
names(reported_claims)[1] <- "Year"
year_col <- reported_claims$Year

head(reported_claims, 5)

## # A tibble: 5 × 6
##    Year `Year 0` `Year 1` `Year 2` `Year 3` `Year 4`
##   <dbl>    <dbl>    <dbl>    <dbl>    <dbl>    <dbl>
## 1  2019    8050.    1342.     268.     57.1     11.6
## 2  2020    5224.     726.     173.     44.3     NA  
## 3  2021    7943.    1119.     219.     NA       NA  
## 4  2022    8023.    1215.      NA      NA       NA  
## 5  2023    7762.      NA       NA      NA       NA

Calculate cumulative sum

reported_claims <- reported_claims[ , !(names(reported_claims) == "Year")] # temporarily drop

cumulative_claims <- as.data.frame(
  t(apply(reported_claims, 1, cumsum))
)

head(cbind(Year = year_col, cumulative_claims), 5)

##   Year  Year 0  Year 1  Year 2  Year 3  Year 4
## 1 2019 8050.11 9391.78 9660.11 9717.20 9728.85
## 2 2020 5224.46 5950.01 6122.76 6167.05      NA
## 3 2021 7942.76 9061.35 9280.68      NA      NA
## 4 2022 8022.77 9238.22      NA      NA      NA
## 5 2023 7762.01      NA      NA      NA      NA

Calculate age-to-age factors

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

column_titles_list <- setdiff(names(cumulative_claims), "Year")

new_columns <- paste(
  head(column_titles_list, -1),
  "to",
  tail(column_titles_list, -1)
)

age_to_age <- data.frame(matrix(nrow = 0, ncol = length(new_columns)))
names(age_to_age) <- new_columns

tibble::add_row(age_to_age) # show empty df

##   Year 0 to Year 1 Year 1 to Year 2 Year 2 to Year 3 Year 3 to Year 4
## 1               NA               NA               NA               NA

Add data into age-to-age dataframe

ratio_list <- c()

df_width <- ncol(cumulative_claims) - 1

for (i in 1:df_width) {
  
  col_1 <- cumulative_claims[[i]]
  col_2 <- cumulative_claims[[i + 1]]
  
  # remove rows where either is NA
  mask <- !(is.na(col_1) | is.na(col_2))
  col_1_clean <- col_1[mask]
  col_2_clean <- col_2[mask]
  
  # compute totals and ratio
  col_1_total <- sum(col_1_clean)
  col_2_total <- sum(col_2_clean)
  ratio <- col_2_total / col_1_total
  
  ratio_list <- c(ratio_list, ratio)
}

age_to_age[nrow(age_to_age) + 1, ] <- ratio_list

head(age_to_age, 5)

##   Year 0 to Year 1 Year 1 to Year 2 Year 2 to Year 3 Year 3 to Year 4
## 1         1.150521         1.027063         1.006423         1.001199

Calculate run-off triangle

run_off <- cumulative_claims

for (i in seq_len(length(column_titles_list) - 1)) {
  current_column <- column_titles_list[i]
  next_column    <- column_titles_list[i + 1]
  factor_value   <- age_to_age[1, i]
  
  # fill NA in next_column using current_column * factor
  missing_rows <- is.na(run_off[[next_column]])
  
  run_off[missing_rows, next_column] <-
    run_off[missing_rows, current_column] * factor_value
}

run_off <- round(run_off, 2)

run_off <- cbind(Year = year_col, run_off)

head(run_off, 5)

##   Year  Year 0  Year 1  Year 2  Year 3  Year 4
## 1 2019 8050.11 9391.78 9660.11 9717.20 9728.85
## 2 2020 5224.46 5950.01 6122.76 6167.05 6174.44
## 3 2021 7942.76 9061.35 9280.68 9340.29 9351.49
## 4 2022 8022.77 9238.22 9488.23 9549.18 9560.62
## 5 2023 7762.01 8930.36 9172.04 9230.95 9242.02

Calculate incurred but not reported claims

run_off_reverse <- run_off[, c(1, ncol(run_off):2)]

ibnr_values <- c()
year <- c()

for (i in 1:nrow(run_off_reverse)) {
  year <- c(year, run_off_reverse[i, 1])
  ibnr_values <- c(ibnr_values, run_off_reverse[i, 2] - run_off_reverse[i, i + 1])
}

ibnr <- data.frame(year = year, IBNR = ibnr_values)

head(ibnr, 5)

##   year    IBNR
## 1 2019    0.00
## 2 2020    7.39
## 3 2021   70.81
## 4 2022  322.40
## 5 2023 1480.01

total_ibnr <- sum(ibnr$IBNR, na.rm = TRUE)
cat("Total IBNR is:", round(total_ibnr, 2), "\n")

## Total IBNR is: 1880.61