With a childcare subsidy, the agency pays up to a set amount and the family covers the rest as a copay. The split — subsidy capped at its max, family pays the remainder — needs careful clamping.
The example
$320 tuition, $240 subsidy max.
| A | B | |
|---|---|---|
| 1 | Item | Value |
| 2 | Subsidy MIN(240,320) | $240 |
| 3 | Family copay | → $80 |
The formula
The formula:
How it works
How it works:
- Subsidy paid = MIN(subsidy max, tuition) — never more than the tuition.
- Family copay = tuition − subsidy paid (floored at zero).
- A separate assigned copay may override — family pays the copay, subsidy covers the rest.
- When subsidy ≥ tuition, the family pays nothing beyond any fixed copay.
Two structures, clamp accordingly. Some programs set a subsidy maximum (agency pays up to X, family covers the rest); others assign a fixed family copay (family pays Y, subsidy covers tuition minus Y). They’re different formulas — the first is tuition − MIN(max, tuition), the second is just the assigned copay with subsidy = tuition − copay. Read the authorization. This is illustrative, not benefits advice.
Try it: interactive demo
Tuition and subsidy maximum.
Variations
Subsidy paid
Capped at tuition:
Fixed copay model
Assigned copay:
Annual family cost
Copay × weeks:
Pitfalls & errors
Cap the subsidy. MIN at tuition so it never overpays.
Max vs fixed copay. Two models — use the right one.
Not benefits advice. Subsidy rules vary — follow the authorization.
Practice workbook
Frequently asked questions
How do I split a childcare subsidy and copay in Excel?
What if there's a fixed copay instead?
Why cap the subsidy with MIN?
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