Financial
Insurance Loss Ratio, Expense Ratio & Combined Ratio formulas
Calculate loss, expense and combined ratios with an explicit earned/written premium convention, then subtract investment income relative to earned premium.
Keep premium bases and reporting conventions explicit
This example divides incurred losses including loss adjustment expenses by earned premium, and underwriting expenses by written premium. Other reporting conventions use earned premium for both. Use a consistent period and gross/net reinsurance basis, and state whether additional items such as policyholder dividends are included.
Written premium is a measure of business written, not simply cash collected upfront. Earned premium relates to coverage already provided; recognition is not necessarily uniform for every risk. This simplified example omits policyholder dividends and other reporting adjustments.
B6 is investment income divided by earned premium, not an asset yield. A 4.5% return on an investment portfolio cannot be subtracted directly unless converted to this premium basis.
The combined ratio is a performance indicator. With different denominators, 1 minus this ratio is not an exact earned-premium profit margin. The additional earned-basis illustration uses (losses + expenses) / earned premium for the same sample amounts; it is not a complete accounting result. Positive premium denominators are required. Signed loss or expense adjustments and signed investment income ratios are allowed.
| P&C Underwriting Metric | Example convention | Normal Year ($6.4M Loss / $10M EP) | Catastrophe Storm Year ($7.8M Loss) |
|---|---|---|---|
| Loss & LAE Ratio | B4 / B2 (Earned Premium) | 64.00% (0.6400) | 78.00% (0.7800) |
| Trade Expense Ratio | B5 / B3 (Written Premium) | 28.00% ($2.94M / $10.5M) | 28.00% (0.2800) |
| Trade Combined Ratio | (B4 / B2) + (B5 / B3) | 92.00% on stated mixed basis | 106.00% on stated mixed basis |
| Operating ratio (4.5% of earned premium) | CombinedRatio - B6 | 87.50% (0.8750) | 101.50% (1.0150) |
Combined ratio with earned/written premium bases
Add the loss ratio and written-premium expense ratio. Do not infer a currency profit by multiplying its complement by earned premium.
=IF(COUNT(B2:B6)<>5,"Invalid inputs",IF(OR(B2<=0,B3<=0),"Positive premiums required",ROUND((B4 / B2) + (B5 / B3), 4)))Insurance Operating Ratio = CombinedRatio - InvestmentIncomeRatio
Subtract the investment-income-to-earned-premium ratio supplied in B6. This does not measure the yield on invested assets.
=IF(COUNT(B2:B6)<>5,"Invalid inputs",IF(OR(B2<=0,B3<=0),"Positive premiums required",ROUND((B4 / B2) + (B5 / B3) - B6, 4)))Loss and loss-adjustment expense ratio
Use the displayed denominator convention consistently when comparing periods or reports.
=IF(COUNT(B2:B6)<>5,"Invalid inputs",IF(OR(B2<=0,B3<=0),"Positive premiums required",ROUND(B4 / B2, 4)))Written-premium expense ratio
Use the displayed denominator convention consistently when comparing periods or reports.
=IF(COUNT(B2:B6)<>5,"Invalid inputs",IF(OR(B2<=0,B3<=0),"Positive premiums required",ROUND(B5/B3,4)))Same amounts on an earned-premium basis
Use the displayed denominator convention consistently when comparing periods or reports.
=IF(COUNT(B2:B6)<>5,"Invalid inputs",IF(OR(B2<=0,B3<=0),"Positive premiums required",ROUND((B4+B5)/B2,4)))Try the example
Use the original values or change them to test your understanding.