Cancel a policy mid-term and the unearned premium is refunded — pro-rata (full unused share) or short-rate (a penalty for early cancel). The refund depends on which method the policy uses.
The example
$1,200/yr, 120 days left.
| A | B | |
|---|---|---|
| 1 | Item | Value |
| 2 | 1200 × 120/365 | — |
| 3 | Pro-rata refund | → ~$394.52 |
The formula
The formula:
How it works
How it works:
- Earned premium = the days already covered; unearned = days remaining.
- Pro-rata refund = premium × days remaining ÷ 365 (insurer-initiated cancels).
- Short-rate keeps a penalty: refund × (1 − short_rate_penalty) — insured-initiated.
- The earned premium is the total minus the refund.
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
Annual premium, days remaining, short-rate penalty.
Variations
Earned premium
Days used:
Short-rate refund
With penalty:
Days remaining
From dates:
Pitfalls & errors
Pro-rata vs short-rate. Who cancels usually decides the method.
Day count. Use actual days remaining ÷ 365 (or 366 in leap years).
Not advice. Refund method is set by policy and regulation.
Practice workbook
Frequently asked questions
How do I calculate a premium refund in Excel?
What's the difference between pro-rata and short-rate?
How do I apply the short-rate 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