A dental plan pays a coverage percentage of the allowed fee, after the deductible. The estimate: apply the deductible first, then the coverage percent — bounded by the annual maximum.
The example
$200 fee, $50 deductible, 80%.
| A | B | |
|---|---|---|
| 1 | Item | Value |
| 2 | (200−50)×80% | — |
| 3 | Plan pays | → $120 |
The formula
The formula:
How it works
How it works:
- Apply the deductible to the allowed fee first (once per benefit period).
- Multiply the remainder by the coverage percentage for the procedure category.
- Cap the payment at the remaining annual maximum with MIN.
- Preventive is often 100%, basic 80%, major 50% — use the right category.
Illustrative estimate only — not a guarantee of benefits or clinical/financial advice. Actual coverage depends on the patient’s plan, frequency limits, downgrades, and the carrier’s determination. Always verify benefits and read the plan.
Try it: interactive demo
Allowed fee, deductible, coverage %.
Variations
Capped at annual max
Remaining benefit:
Deductible not yet met
Apply only what is left:
By category
Right percent:
Pitfalls & errors
Deductible first. Subtract the deductible before applying coverage %.
Allowed fee. Coverage is on the plan-allowed amount, not your full fee.
Not a guarantee. Verify benefits — estimates can differ from payment.
Practice workbook
Frequently asked questions
How do I estimate a dental insurance payment in Excel?
How do I cap it at the annual maximum?
Why use the allowed fee, not my fee?
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