Why Data Reconciliation Comes Before Measurement
A loss ratio, average loss, or other experience measure is only meaningful if its numerator and denominator describe the same business on the same basis. A dataset can appear complete and still overstate losses because a claim was loaded twice, understate losses because a recovery or payment was mishandled, or compare losses for one coverage with exposures for another. Those errors can distort a rate indication even when the arithmetic is flawless.
The earlier material on accident-year, policy-year, report-period, and calendar-year assignment establishes that dates serve different purposes. Diagnosis requires checking which date the requested measure uses, then confirming that claims, expenses, and exposures have been selected consistently. This tutorial focuses on those data checks rather than on subsequent rate-level calculations. The methods are aligned with the CAS Exam 5 (Basic Ratemaking and Estimated Claim Liabilities) syllabus.
A useful investigation moves from record detail to totals. Start by writing down the target measure: for example, paid losses for a specified accident year and coverage, or loss and loss adjustment expenses for a specified policy population. Then check identifiers, relevant dates, financial signs, classifications, and exposure matches. Do not “fix” a difference by forcing totals to agree; each adjustment should be supported by an identifiable record and a stated rule.
A Practical Diagnostic Sequence
Specify the measure, period basis, evaluation date, coverage, and whether amounts are gross or net of recoveries. State whether expenses are included.
Use claim and transaction identifiers to distinguish duplicate rows from separate payments on the same claim. Compare dates and amounts; a repeated claim number alone does not prove duplication.
Apply the date relevant to the target basis. For accident-year data, occurrence date determines the accident year; payment date determines a payment calendar period, not the accident year.
Separate gross claim payments, recoveries, allocated loss adjustment expenses, unallocated loss adjustment expenses, and underwriting expenses. Confirm the intended signs and categories.
Verify that each claim belongs to the stated insured, policy term, and coverage, and that its loss falls within the applicable coverage period. Confirm that exposure uses the same population and period.
Show each correction and its reason, then recompute the measure from corrected records. Retain unresolved items separately rather than silently including or excluding them.
This sequence is a control framework, not a substitute for understanding the requested measure. For example, a paid-loss total may include only payments made by an evaluation date, whereas a reported-loss measure may use a different financial definition. The target must be clear before a record can be classified as an error.
Worked Reconciliations
Worked Example: Duplicate Records and Conflicting Date Bases
A hypothetical insurer is preparing paid losses for accident year 2025, evaluated through January 31, 2026. A data extract selected payments made during calendar year 2025 and is labelled “AY 2025.” The extract contains these rows:
| Claim | Occurrence date | Payment date | Amount | Record note |
|---|---|---|---|---|
| C-41 | Dec. 28, 2025 | Dec. 30, 2025 | $12,000 | Transaction 771 |
| C-41 | Dec. 28, 2025 | Dec. 30, 2025 | $12,000 | Transaction 771 repeated |
| C-42 | Jan. 3, 2026 | Dec. 29, 2025 | $5,000 | Transaction 804 |
| C-43 | Nov. 14, 2025 | Jan. 10, 2026 | $8,000 | Transaction 826 |
Method: inspect transaction identity, then apply accident-year selection. The two C-41 rows have the same claim, transaction identifier, dates, and amount, so one is a duplicate. The payment is not two distinct transactions.
The uncorrected extract total is:
The extract excludes C-43 because its payment was made in 2026, despite the occurrence being in 2025. It includes C-42 because it was paid in 2025, despite the occurrence being in 2026. These are consequences of using payment calendar year as the selection rule, not accident year.
For accident-year 2025 paid losses through January 31, 2026, include valid payments by that date on claims arising from occurrences in 2025. C-41 contributes one payment of $12,000; C-42 is excluded because its occurrence is in 2026; C-43 contributes $8,000 because its occurrence is in 2025 and its payment is within the evaluation date. Therefore:
Check: The corrected total is $20,000, which equals the reported $29,000 less the duplicate $12,000, less C-42’s $5,000, plus the omitted C-43 payment of $8,000. The reconciliation is \(29{,}000-12{,}000-5{,}000+8{,}000=20{,}000\). The common mistake is to treat a payment-year extract as accident-year data simply because its label says “AY 2025.”
Worked Example: Recoveries and Expense Classification
A hypothetical insurer reviews a property portfolio and wants net losses plus loss adjustment expenses (LAE) for a defined experience period. Its paid records show three gross claim payments of $18,000, $12,000, and $10,000. Separate recovery records show $3,000 of salvage and $2,000 of subrogation. The expense ledger shows $6,000 of allocated LAE (ALAE), $4,000 of unallocated LAE (ULAE), and $7,000 of underwriting expense. A draft report shows “loss and LAE” of $62,000.
Method: reconcile the components by financial meaning before summing. First calculate gross paid losses:
Salvage and subrogation are recoveries of claim costs in this example. When the requested measure is net loss, subtract both from gross losses:
ALAE is tied to specific claims, and ULAE is a claim-handling expense not allocated to individual claims. Both are LAE, so the requested net loss and LAE total is:
Underwriting expense is not LAE. It may be relevant to a separate expense analysis or rate provision, but it does not belong in this loss-and-LAE total. The draft’s $62,000 can be reconstructed as \(40{,}000+3{,}000+2{,}000+6{,}000+4{,}000+7{,}000=62{,}000\): recoveries have been added rather than subtracted, and underwriting expense has been included as LAE.
Check: Correct net loss and LAE is $45,000. The difference from the draft is $17,000: removing the wrongly added $5,000 of recoveries changes the total by $10,000, and removing $7,000 of underwriting expense changes it by another $7,000. Thus \(62{,}000-10{,}000-7{,}000=45{,}000\). The common mistake is to aggregate all financial entries without checking whether each is a loss, recovery, claim expense, or underwriting expense.
Worked Example: Coverage Codes and Exposure Matching
A hypothetical insurer wants paid property losses per earned property policy-year for calendar year 2025. The policy and claim review identifies the following records. All listed claims occurred during the stated policy term and were paid by the evaluation date.
| Policy | Policy coverage and earned exposure | Claim record | Amount |
|---|---|---|---|
| P-101 | Property; 1.0 policy-year | Property claim, coded PROP | $9,000 |
| P-102 | Property; 1.0 policy-year | Property claim, mistakenly coded LIAB | $4,000 |
| P-103 | Liability; no property exposure | Liability claim, mistakenly coded PROP | $6,500 |
| P-104 | Property; 0.5 policy-year after midyear cancellation | Property claim, coded PROP-EXT | $2,000 |
A direct-code extract includes only claims coded PROP. It therefore includes P-101 and P-103, for a reported total of \(9{,}000+6{,}500=15{,}500\). The target population is property coverage, however, so the policy declarations and claim details must control the correction: P-102 is a property claim despite its erroneous liability code; P-103 is a liability claim on a liability policy and must be excluded; and P-104 is property business despite the unmapped PROP-EXT code.
The corrected property loss total is:
The corresponding earned property exposure is \(1.0+1.0+0.5=2.5\) policy-years. The corrected paid loss per earned policy-year is therefore:
Check: The numerator contains only the three property claims, and the denominator contains only their property exposure. The direct-code result would have been \(15{,}500/2.5=6{,}200\), but that ratio combines an incorrectly coded liability loss with omitted property losses. Its error is not fixed by the fact that the denominator is correct. The common mistake is to assume claim-system coverage codes are authoritative without reconciling them to policy coverage and valid code mappings.
Why the Checks Work—and Where They Can Fail
For a sum of transaction amounts, a duplicated row adds its amount twice instead of once. If the duplicated transaction has amount \(a\), the reported total exceeds the correct total by \(a\), unless another error happens to offset it. This explains why comparing transaction identifiers and details is more informative than checking only whether a claim number repeats: one claim can have multiple legitimate payments, while one transaction can be duplicated.
Likewise, when the target is net of recoveries, each recovery reduces the amount retained as loss. If gross losses are \(G\) and eligible recoveries are \(R\), net losses are \(G-R\). Adding \(R\) instead would make the result exceed the intended net amount by \(2R\): the incorrect calculation is \(G+R\), and \((G+R)-(G-R)=2R\). This sign check applies only when recoveries are defined as offsets in the target measure; confirm the accounting convention before using it.
A period mismatch is different from a duplicate or sign error. A transaction can be valid and correctly valued but belong outside the target population because the wrong date basis was applied. Payment date is appropriate for a payment calendar-period total; it does not by itself identify accident year. Similarly, claim and policy records must be matched by the relevant coverage and policy term. A correct claim amount assigned to the wrong coverage still corrupts a coverage-specific measure.
Expense classification also depends on the stated target. ALAE and ULAE are both LAE, but only ALAE is assigned directly to particular claims. Underwriting expenses are not LAE. If the objective is total expenses for a broader rate calculation, underwriting expenses may be included in a separate component; that does not make them claim losses or LAE. Keep the categories visible until the measure’s definition determines how they should be combined.
Common Data-Diagnosis Traps
- Using the extract label instead of the date rule. A file labelled “accident year” may have been selected by payment date. Test actual records against the target period definition.
- Deleting every repeated claim number. Several payments can belong to one claim. Identify duplicates at the transaction level, not solely at the claim level.
- Adding recoveries to net losses. Establish whether each item is a recovery and whether the requested loss measure is gross or net before summing.
- Combining expense categories prematurely. Keep LAE separate from underwriting expense unless the target measure explicitly calls for both.
- Trusting one coverage field. Compare claim coding with policy declarations, coverage terms, and valid code mappings; do not include a mismatched claim merely because its code matches an extract filter.
- Dividing by an unmatched exposure base. The policy, coverage, and period represented by the denominator must agree with those represented by the numerator.
Check Your Understanding
For each hypothetical situation, identify the correction and calculate the requested result.
- A calendar-year paid extract contains a $3,200 transaction twice under the same transaction ID. No other records are affected. What is the overstatement? (Answer: $3,200.)
- A paid-loss report for accident year 2024 includes a $7,500 payment on a claim arising in 2025 and omits a $4,000 payment made in 2025 on a claim arising in 2024. What is the net adjustment to the report? (Answer: decrease the reported total by $3,500.)
- A portfolio has $52,000 of gross paid losses and eligible recoveries of $4,500. What are net paid losses? (Answer: $47,500.)
- A report includes $28,000 of net losses, $5,000 of ALAE, $3,000 of ULAE, and $6,000 of underwriting expense. What is loss and LAE, excluding underwriting expense? (Answer: $36,000.)
- A property extract contains $11,000 of correctly matched property losses and a $2,400 liability loss mistakenly coded as property. It omits a $1,600 property loss coded as liability. The matched property exposure is 2.0 earned policy-years. What are corrected property losses and corrected losses per earned policy-year? (Answer: $10,200 and $5,100 per earned policy-year.)