Policy Limit and Sublimit Caps

All versionsMIN

A claim is capped by the policy limit, and specific perils (jewelry, electronics, water) often have a lower sublimit. The payable amount is the loss capped by whichever limit applies.

Quick formula: covered amount under a sublimit:

=MIN(loss, sublimit)

The payable loss is capped at the sublimit for that category, then the whole claim is capped at the policy limit.

Functions used (tap for the full reference guide):

MIN

The example

$8,000 jewelry loss, $1,500 sublimit.

	A	B
1	Item	Value
2	MIN(8000, 1500)	—
3	Covered	→ $1,500

The formula

The formula:

=MIN(loss, sublimit) // loss capped at the sublimit

How it works

How it works:

Each category (jewelry, cash, electronics) may have its own sublimit.
Cap that category’s loss at its sublimit with MIN(loss, sublimit).
Sum the capped categories, then cap the total at the policy limit.
Amounts above a sublimit are uncovered unless scheduled separately.

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

Live demo

Loss, sublimit, policy limit.

Loss

Sublimit

Policy limit

Covered · Uncovered

Variations

Capped at policy limit

Overall cap:

=MIN(total_covered, policy_limit)

Uncovered amount

Above the sublimit:

=loss - MIN(loss, sublimit)

Sum of capped categories

Multiple sublimits:

=SUM(MIN-capped category losses)

Pitfalls & errors

Sublimit first. Cap the category before applying the overall limit.

Scheduled items. High-value items may need separate scheduling above the sublimit.

Not advice. Sublimits and exclusions vary — read the policy.

Practice workbook

📊

Download the free Policy Limit and Sublimit Caps practice workbook

A sublimit sheet with the policy-cap, uncovered, and multi-category variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I apply a sublimit in Excel?

Cap the category loss with MIN: =MIN(loss, sublimit). An $8,000 jewelry loss under a $1,500 sublimit covers $1,500.

How does the policy limit interact?

Cap each category at its sublimit, sum them, then cap the total at the policy limit with another MIN.

What about amounts above the sublimit?

They're uncovered unless the item is separately scheduled: loss - MIN(loss, sublimit).

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

Function references: MIN