Counting IBNR and Reconciling Reinsurance Cessions

Goal

You learn how to count accidents that are not in the data, and become able to do by hand what pieces the reserve is made of and in what order you narrow down when a reinsurance cession is off.

Why it matters

The accident data at closing time is always not yet fully developed. If you count June accidents on June 30, fewer come out, and that is not because there were few accidents but because there has not yet been time for them to be reported. If you report "this month's loss ratio improved" without knowing this difference, it returns to its original place next month, and then a meeting is held asking why it suddenly got worse.

The amount of cases that have not yet come is a calculation, not a guess. If you extract the reporting lag distribution from fully developed accident months, you can tell what percent of the way a not-yet-developed month has come. The data in this lab has a closing date of 2026-06-30 and a maximum reporting lag of 5 months, so the accident months that have filled all 5 months go up to 2026-01.

The reserve has two pieces.

미지급 발생손해   발생손해액 − 기지급액. 이미 접수된 건의 남은 몫
IBNR 추정액       미접수 추정 건수 × 건당 평균 발생손해액

When taking the average for the second piece, you must leave out large cases over 100 million won. Setting large cases apart and estimating them individually is the usual treatment, and if you mix them into the average, the reserve is inflated by hundreds of millions.

Finally, reinsurance. In this data, QS30 proportional reinsurance covers every loss, so 30% of the incurred loss amount is ceded. But the cession details are in two places — the accounting ledger (booked per month) and the reinsurance management system (registered per case). The ledger is a value computed from the primary data, while the reinsurance system is registered by people. That is why they diverge.

Steps

Save the generator in the example on the right as /root/ins/make_reserve.py and run it with python3 make_reserve.py. It creates 1,296 losses, 1,293 cessions, and 12 months of ledger.
Write the table of accident month × reporting lag in months to /root/ins/triangle.csv as accident_ym,lag0,lag1,lag2,lag3,lag4,lag5,reported. All 12 accident-month lines must be included.
Gather only the accident months that have filled all 5 months (2025-07 to 2026-01) and write six lines to /root/ins/lagrate.csv as lag,claims,cum_claims,cum_permille. cum_permille is the integer per-mille obtained by dividing the cumulative by the total.
Estimate the final counts for the accident months that are not yet fully developed and write them to /root/ins/ibnr.csv as accident_ym,reported,max_lag,cum_claims,ultimate,ibnr.
Write eight lines to /root/ins/reserve.txt: outstanding=, ibnr_claims=, avg_incurred_excl_large=, ibnr_amount=, reserve_total=, avg_incurred_incl_large=, reserve_if_incl_large=, overstated_by=.
Reconcile the ledger and the reinsurance system by accident month, write the months whose difference is not 0 to /root/ins/cession_diff.csv as accident_ym,ledger,system,diff, and narrow down to the per-case level and write to /root/ins/cession_claims.csv as claim_no,accident_ym,incurred,expected,recorded,diff.
Write seven lines to /root/ins/netloss.txt: gross_incurred=, ceded=, net_retained=, big_claim=, big_net=, net_excl_big=, big_share_pct=. Use the ledger value for the ceded amount.
Write a report in /root/ins/reserve_report.md with five sections: ## 확인한 것, ## 준비금, ## 출재 대사, ## 대형 사고, ## 권고 (the five Korean section titles mean: what was checked, reserve, cession reconciliation, large accident, and recommendations). The recommendations must have three or more items.

Notes

For the month difference between two year-months, convert to 연 × 12 + 월 and subtract (the placeholders are the year and the month). You cannot get it by string comparison.
SQLite's SUM(lag=0) counts the rows where the condition is true. You do not need to write a long CASE WHEN.
Common mistake 1: extracting the ratios from all accident months in step 3. If months still developing are mixed in, the same-month reporting ratio comes out higher than it really is.
Common mistake 2: dividing first in step 4. In integer arithmetic the quotient becomes 1, and the estimate comes out as it is.
Common mistake 3: including the large case in the average in step 5. The reserve is inflated by nearly 100 million won.
Common mistake 4: stopping step 6 at the month level. With "September does not match," nobody can touch anything.

Generate the closing data

Save the generator in the example on the right as /root/ins/make_reserve.py and run it with python3 make_reserve.py. It creates 1,296 losses, 1,293 cessions, and 12 months of ledger.

Save the generator in the lab instructions as /root/ins/make_reserve.py and run it with python3. Accidents that would be reported after the closing date are not in the data to begin with.

Build the development triangle

Write the table of accident month × reporting lag in months to /root/ins/triangle.csv as accident_ym,lag0,lag1,lag2,lag3,lag4,lag5,reported. All 12 accident-month lines must be included.

The reporting lag in months comes out if you convert report_ym and accident_ym each to year×12+month and subtract. The lower right being empty is normal.

Extract the reporting rates from the fully developed months

Gather only the accident months that have filled all 5 months (2025-07 to 2026-01) and write six lines to /root/ins/lagrate.csv as lag,claims,cum_claims,cum_permille. cum_permille is the integer per-mille obtained by dividing the cumulative by the total.

The closing date is 2026-06 and the maximum lag is 5 months, so the accident months that have filled all 5 months go up to 2026-01. If you mix in months that are still developing, the ratios skew toward the front.

Estimate the cases not yet reported

Estimate the final counts for the accident months that are not yet fully developed and write them to /root/ins/ibnr.csv as accident_ym,reported,max_lag,cum_claims,ultimate,ibnr.

For a month still developing, the maximum observable lag is less than 5. Divide by the cumulative count up to that lag and multiply by the total count to get the final estimate. Do the multiplication first and the division only once, at the end.

Compute the reserve in two pieces

Write eight lines to /root/ins/reserve.txt: outstanding=, ibnr_claims=, avg_incurred_excl_large=, ibnr_amount=, reserve_total=, avg_incurred_incl_large=, reserve_if_incl_large=, overstated_by=.

The IBNR estimate is the number of unreported cases times the average incurred loss per case. When taking the average, compute the version that puts in the large cases over 100 million won and the version that leaves them out, and place them side by side.

Reconcile the ledger and the reinsurance system

Reconcile the ledger and the reinsurance system by accident month, write the months whose difference is not 0 to /root/ins/cession_diff.csv as accident_ym,ledger,system,diff, and narrow down to the per-case level and write to /root/ins/cession_claims.csv as claim_no,accident_ym,incurred,expected,recorded,diff.

First narrow down to the accident-month level and keep only the months whose difference is not 0, then open them at the per-case level to pin down. You have found them all when the monthly total difference and the per-case total difference are the same.

Measure the financial impact of one large accident

Write seven lines to /root/ins/netloss.txt: gross_incurred=, ceded=, net_retained=, big_claim=, big_net=, net_excl_big=, big_share_pct=. Use the ledger value for the ceded amount.

For the ceded amount, use the accounting ledger value, not the reinsurance system value that has not been reconciled. Net retained is the total incurred loss minus the ceded amount.

Write the closing report

Write a report in /root/ins/reserve_report.md with five sections: ## 확인한 것, ## 준비금, ## 출재 대사, ## 대형 사고, ## 권고 (the five Korean section titles mean: what was checked, reserve, cession reconciliation, large accident, and recommendations). The recommendations must have three or more items.

It needs five sections and the recommendations need three or more items. The reserve and the cession are looked at by different teams, so the document moves only if it is clear whose job each item is.