Commercial property policies require insuring to a coinsurance percentage of value. Underinsure, and a claim is reduced by the ratio of carried to required coverage — the dreaded coinsurance penalty.
The example
$100k value, 80% req, $60k carried, $40k loss.
| A | B | |
|---|---|---|
| 1 | Item | Value |
| 2 | 60k / 80k | 0.75 |
| 3 | 40k × 0.75 | → $30,000 |
The formula
The formula:
How it works
How it works:
- Required coverage = value × coinsurance % (e.g. 80% of replacement value).
- The coinsurance factor = carried ÷ required, capped at 1 (no bonus for over-insuring).
- Multiply the loss by the factor, then subtract the deductible.
- Insure to the requirement and the factor is 1 — no penalty.
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
Value, coinsurance %, carried, loss.
Variations
Required coverage
The threshold:
Coinsurance factor
Capped at 1:
Penalty amount
What underinsurance costs:
Pitfalls & errors
Cap at 1. Over-insuring earns no bonus — MIN(…, 1).
Insure to value. Penalty applies even on partial losses.
Not advice. Coinsurance clauses vary — read the policy.
Practice workbook
Frequently asked questions
How do I calculate a coinsurance penalty in Excel?
What's the required coverage?
How do I avoid the penalty?
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