Patient Out-of-Pocket

Excel Formulas › Dental Practice

All versions

The patient portion is the fee minus what insurance pays — the deductible plus the coinsurance share, plus anything over the annual maximum. It’s the number patients actually ask about.

Quick formula: patient responsibility on a procedure:
=fee - estimated_insurance_payment
The provider's fee minus the estimated insurance payment is what the patient owes. Includes deductible and copay.

The example

$200 fee, plan pays $120.

AB
1ItemValue
2200 − 120—
3Patient owes→ $80

The formula

The formula:

=fee - estimated_insurance_payment // fee − insurance payment

How it works

How it works:

  1. Start with the provider fee (or allowed fee for a participating provider).
  2. Subtract the estimated insurance payment for the patient portion.
  3. It bundles the deductible + coinsurance, plus any amount over the annual max.
  4. For a treatment plan, sum the patient portion across all procedures.

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

Live demo

Fee and estimated insurance payment.

Patient owes:

Variations

Treatment plan total

Sum patient portions:

=SUM(fees) - SUM(insurance_payments)

Percent patient pays

Share of fee:

=(fee - insurance_payment) / fee

With over-max amount

Add uncovered:

=patient_share + MAX(fee - allowed, 0)

Pitfalls & errors

Fee vs allowed. A participating provider charges the allowed fee; the difference is a write-off, not patient cost.

Estimate. Final patient cost depends on the carrier’s actual payment.

Annual max. Amounts over the max fall to the patient.

Practice workbook

📊
Download the free Patient Out-of-Pocket practice workbook
An out-of-pocket sheet with the plan-total, percent, and over-max variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I calculate a patient's out-of-pocket in Excel?
Fee minus the estimated insurance payment: =fee - estimated_insurance_payment. A $200 fee with $120 paid leaves $80.
How do I total a treatment plan's patient cost?
=SUM(fees) - SUM(insurance_payments).
What if the fee exceeds the allowed amount?
For a participating provider the difference is a write-off; otherwise it adds to the patient's cost.

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

Related formulas: Insurance estimate · Treatment plan financing · Realization rate