Loss ratio — claims paid over premiums earned — is the core underwriting health metric. Below 100% the book is profitable on losses alone; combined with expenses it drives the bottom line.
The example
$640,000 claims, $1,000,000 premium.
| A | B | |
|---|---|---|
| 1 | Item | Value |
| 2 | 640k / 1M | — |
| 3 | Loss ratio | → 64% |
The formula
The formula:
How it works
How it works:
- Divide incurred claims by earned premiums for the loss ratio.
- Lower is better — it’s the share of premium consumed by claims.
- Add the expense ratio for the combined ratio (the full picture).
- Track by line of business with SUMIF to find unprofitable segments.
Illustrative math only — not insurance, financial, or legal advice. Policy language, state regulation, and carrier rules govern actual claims, premiums, and coverage. Always read the policy and consult a licensed professional.
Try it: interactive demo
Claims incurred and premiums earned.
Variations
Combined ratio
Add expenses:
By line of business
Segment health:
Underwriting margin
What’s left:
Pitfalls & errors
Earned, not written. Use earned premium for the period to match incurred claims.
Incurred claims. Include reserves for reported-but-unpaid, not just paid.
Zero premium. No earned premium gives #DIV/0!.
Practice workbook
Frequently asked questions
How do I calculate loss ratio in Excel?
What's a good loss ratio?
Earned or written premium?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class