Motor insurance loss ratio and claim settlement ratio from claim files

Why the underwriting head asks for this

An underwriting head uses segment loss ratios to decide where to tighten pricing, add inspection, or grow. A claims head uses the settlement ratio to see whether decisions are keeping pace with intimations. The two numbers come from the same two files, which is exactly why they get muddled.

Tables from the policy and claims systems

`policies` — policy_id, segment, start_date, end_date, premium. In production add od_premium and tp_premium separately, because own-damage and third-party cover behave very differently, plus channel, rto_state and vehicle_age.

`claims` — claim_id, policy_id, loss_date, reported_date, status (Settled, Repudiated, Open), paid, reserve (outstanding estimate at the valuation date). Real files add claim_type (OD, TP, theft), surveyor_id and settled_date.

The reserve column is often a snapshot that the claims team overwrites. Ask for it as at the valuation date, not today's value, or a loss ratio re-run next month will not match the one you circulated.

The worked example (synthetic data)

600 annual policies starting between October 2024 and March 2026, with claims on a quarter of them. Nothing here reflects a real insurer.

CREATE TABLE policies (policy_id INTEGER PRIMARY KEY, segment TEXT,
  start_date TEXT, end_date TEXT, premium REAL);
CREATE TABLE claims (claim_id INTEGER PRIMARY KEY, policy_id INTEGER, loss_date TEXT,
  reported_date TEXT, status TEXT, paid REAL, reserve REAL);

-- 600 synthetic annual policies starting between Oct 2024 and Mar 2026
WITH RECURSIVE k(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM k WHERE n < 600)
INSERT INTO policies
SELECT n,
       CASE n % 3 WHEN 0 THEN 'Two-wheeler' WHEN 1 THEN 'Private car' ELSE 'Commercial vehicle' END,
       date('2024-10-01', '+' || ((n * 7) % 540) || ' days'),
       date('2024-10-01', '+' || ((n * 7) % 540) || ' days', '+1 year', '-1 day'),
       CASE n % 3 WHEN 0 THEN 1800 WHEN 1 THEN 14000 ELSE 32000 END
FROM k;

-- synthetic claims on roughly one policy in six
INSERT INTO claims (policy_id, loss_date, reported_date, status, paid, reserve)
SELECT policy_id,
       date(start_date, '+' || (policy_id % 300) || ' days'),
       date(start_date, '+' || (policy_id % 300 + 3 + policy_id % 20) || ' days'),
       CASE policy_id % 7 WHEN 0 THEN 'Repudiated' WHEN 1 THEN 'Open' ELSE 'Settled' END,
       CASE WHEN policy_id % 7 IN (0,1) THEN 0
            ELSE premium * (2 + policy_id % 5) END,
       CASE WHEN policy_id % 7 = 1 THEN premium * 3 ELSE 0 END
FROM policies WHERE policy_id % 6 IN (0, 1, 2) AND policy_id % 12 < 3;

Now the two metrics for financial year 2025-26 (1 April 2025 to 31 March 2026):

-- Earned premium for FY 2025-26: premium x (days of cover inside the year / days of cover)
CREATE VIEW earned AS
SELECT segment,
       SUM(premium) FILTER (WHERE start_date BETWEEN '2025-04-01' AND '2026-03-31') AS written,
       SUM(premium *
           MAX(0, julianday(MIN(end_date, '2026-03-31')) - julianday(MAX(start_date, '2025-04-01')) + 1)
           / (julianday(end_date) - julianday(start_date) + 1)) AS earned
FROM policies GROUP BY segment;

-- Incurred on an accident-year basis: losses that happened in FY 2025-26
CREATE VIEW incurred AS
SELECT p.segment, COUNT(*) AS claims, SUM(c.paid + c.reserve) AS incurred
FROM claims c JOIN policies p USING (policy_id)
WHERE c.loss_date BETWEEN '2025-04-01' AND '2026-03-31'
GROUP BY p.segment;

SELECT e.segment, ROUND(e.written) AS written, ROUND(e.earned) AS earned,
       i.claims, ROUND(i.incurred) AS incurred,
       ROUND(100 * i.incurred / e.earned, 1) AS loss_ratio_pct,
       ROUND(100 * i.incurred / e.written, 1) AS wrong_written_basis_pct
FROM earned e JOIN incurred i USING (segment) ORDER BY loss_ratio_pct DESC;

-- Claim settlement ratio by count, for claims reported in FY 2025-26
SELECT COUNT(*) AS reported,
       SUM(status = 'Settled') AS settled,
       SUM(status = 'Repudiated') AS repudiated,
       SUM(status = 'Open') AS open_at_year_end,
       ROUND(100.0 * SUM(status = 'Settled') / COUNT(*), 1) AS csr_all_reported_pct,
       ROUND(100.0 * SUM(status = 'Settled') / SUM(status <> 'Open'), 1) AS csr_decided_only_pct
FROM claims WHERE reported_date BETWEEN '2025-04-01' AND '2026-03-31';

And the settlement ratio: 92 claims reported in the year, 64 settled, 15 repudiated, 13 still open. Settled over all reported is 69.6%; settled over decided claims only is 81.0%.

Four definitions that move the answer

Earned, not written. The book in this example is growing, so written premium exceeds earned. Dividing by written premium makes every segment look five to eight points better. In a shrinking book the error runs the other way.

Accident year versus reporting year. The loss-ratio query counts losses that happened in FY 2025-26. A reporting-year view counts claims intimated in the year, including late-reported losses from earlier cover. Third-party motor claims can be reported and litigated long after the loss, so the two views diverge most for commercial vehicles.

Reserves are estimates. Incurred includes the reserves held on open claims. When reserves are strengthened next quarter, this year's loss ratio rises even though nothing new happened. Show paid and reserve separately next to incurred, and say that the recent year is not yet developed. Actuaries add an IBNR (incurred but not reported) provision on top; if you have it, add it as its own column rather than burying it.

Which claims count as "settled". The 69.6% versus 81.0% gap is entirely the treatment of open claims. IRDAI's annual reports publish insurer-wise claims data; if you benchmark against them, read that report's own definitions note and replicate its denominator before comparing, rather than assuming it matches yours.

Decision table for the underwriting review

Motor-claims data that misleads

• Multi-year two-wheeler policies. Long-term policies need earning spread across all their years. A one-year earning formula over-earns them in year one.
• OD and TP in one number. Third-party premium is tariff-driven and its claims are long-tailed. A combined loss ratio can hide an OD pricing problem.
• Salvage and recoveries. Recoveries from salvage sale or from a third party reduce incurred. Missing them overstates loss ratios on total-loss-heavy segments.
• Cancelled policies. A policy cancelled mid-term stops earning at cancellation; use the actual end date, not the original one.

What the underwriting head should see first

Give the underwriting head one table by segment with earned premium, claim count, frequency, average severity, paid, reserve and loss ratio, and label the period, the basis (accident year) and the valuation date in the title. Put the settlement ratio on the claims operations page with both denominators and an open-claims ageing chart. Separate pages keep the two conversations from blending.

Earning formulas with MIN/MAX date overlaps, conditional counts and two-grain reporting come up constantly in insurance analytics, and the Data Analyst course builds them step by step. For the health-insurance side of claims operations, see cashless claim TAT analysis.