Personal Auto Claims and Reserves

• Loss reserving

  build paid and incurred triangles from the transaction log, estimate IBNR with the chain ladder or Bornhuetter-Ferguson, and score the estimate against ultimate_truth, accident year by accident year.

• Ratemaking

  earned premium and car-years, frequency and severity by coverage, severity trend, on-level premium from the rate changes, and loss ratios by rating variable to find where the plan undercharges.

• Catastrophe analysis

  a hail storm in May 2024, its claims by territory and what it does to the accident year.

• Claims analytics

  report lag, settlement time, litigation, reserve adequacy over a claim's life, reopenings, salvage and subrogation.

• Teaching and demos

  a CAS or actuarial science course, an insurance analytics portfolio, a reserving tool or a claims dashboard demo, with no policyholder data anywhere.

• ultimate_truth.ibnr_trueWhat each accident year and coverage will still cost beyond the incurred booked at the cut: the reserve's answer.-528,776 to 3.53M, mean 226,695
• ultimate_truth.ultimate_on_claims_not_reportedThe part of it on claims not yet reported at the cut, IBNR proper.0 to 1.82M, mean 124,365
• ultimate_truth.development_on_reportedThe rest: how claims already reported will develop, up for injuries, down for damage as recoveries arrive.-631,408 to 1.71M, mean 102,330
• ultimate_truth.ultimate_lossWhat the accident year and coverage finally cost, net of salvage and subrogation.945,488 to 11.4M, mean 6.71M
• policies.true_frequency_indexHow often the policy really claims against the book's average, beside the premium the plan charged it.0.034 to 17.17, mean 1
• claims.catastrophe_codeThe hail storm's code on each claim it caused.1 values: CAT-2405, 96.3% empty

• About 38,000 cars insured at any time; an annual term costs $929 on average. 79% of terms carry physical damage cover, and 8% are cancelled before they expire.
• Rate changes filed: +4.0% from 1 January 2023, +7.5% from 1 July 2024. Severity rises faster than the rates.
• The May 2024 hail storm brought 905 claims in the hail-belt territories, $5.4 million incurred and 17% of the accident year's.
• About 1,100 claims were open at the cut, 59% of them injuries. Accident year 2025 was 89% reported in dollars at the cut.
• Injury reserves develop up: the first reserve is set before treatment is known. Damage reserves develop down: salvage and subrogation, 12% of physical damage paid, arrive after a claim closes.

• every key resolves and every primary key is unique;
• every premium recomputed from the filed rating plan: territory, driver, vehicle, injury limit, deductible, tenure and the rate level in force on the term's effective date; every term a year long, cancellations inside their term with the unearned premium returned pro rata;
• every claim inside its policy's term and on a coverage the policy carries; hail claims only in the hail belt, on the storm's day; injury payments inside the limit and damage inside the vehicle's value;
• the transaction log adds up: the case reserve after each transaction is the running sum, never negative, a payment takes exactly its amount off the reserve, a closed claim holds none, recoveries come only after close and only where they can (salvage on total losses, subrogation on collisions the other driver caused), and each claim's paid, recoveries, reserve and status at the cut equal its transactions;
• nothing is dated after the cut; the ultimate truth's reported column equals the files, accident year by coverage, and its unreported claims are mostly 2025 accidents.

• policiesEvery annual term written from 2020: driver (age, insurance score tier, prior claims, mileage, use), vehicle (type, age, value), cover (package, injury limit, collision deductible), tenure, the premium the plan charged, cancellation with its date, reason and return premium240,000 rows · 26 cols
• claim_transactionsThe claim system's log: reserve opened and revised, payments, closings and reopenings, salvage and subrogation received, each with the case reserve after it93,298 rows · 7 cols
• claimsEvery claim reported by the cut: coverage, cause of loss, catastrophe code, accident and report dates, fault, litigation, total loss, deductible, status, paid, recoveries, case reserve and incurred at the cut24,106 rows · 20 cols
• ultimate_truthThe hindsight table: each accident year and coverage as booked at the cut, and what it finally costs once every late claim is reported, every open claim settles and every recovery comes in20 rows · 11 cols
• territoriesThe twelve rating territories: area type, whether a territory lies in the hail belt, its share of the book and the factor the filed plan rates it at12 rows · 7 cols
• rate_changesThe rate changes the insurer filed, with their effective dates2 rows · 3 cols

Want it bigger, in another setting, or with your own columns? Have us build it.