Employee Health Plan Total Cost Comparator

Purpose and model boundary

This calculator compares up to five employer health plans on what one plan year of care would cost you. You enter each plan's employee premium and its deductible, out-of-pocket limits and account funding, a rule for every service your claims use (covered or not, and how the cost is shared), and the claims you expect for the year, each at the plan's allowed amount. The workbook then adjudicates every claim, in date order, under every plan: it applies the deductible, copay and coinsurance in the order the plan sets, tracks each member's and the family's deductible and out-of-pocket amounts, stops your cost share at the out-of-pocket limit, and applies any HRA money afterwards. The main result is the cheapest plan for that year, on the basis you choose, and how far ahead it is.

Two further questions can be added through the calculation choice. Simulate replays 100 illustrative years, each drawn from claim bundles you describe (for example low, medium and high use) by the weights you give them, and reports how each plan's annual cost spreads out and how often each plan is cheapest. Scenario grid shows, in an 11 by 11 table, how the cost difference between two plans moves when every allowed amount is scaled up or down and when one plan's premium changes.

Every figure on this page is computed by the spreadsheet; the page adds nothing to it. The model is educational. The plan terms, claims, bundles and weights are yours; the simulated shares follow from them and are not actuarial probabilities. It is not insurance, tax, benefits or medical advice, and it holds no plan's actual terms.

Inputs and units

• Coverage tier: EE (employee only), ES (employee and spouse), EC (employee and children) or FAM (family). Employee only applies the individual limits; the other tiers apply both the individual and the family limits.
• Plan year start: from 2020-01-01 to 2040-12-31. The plan year ends the day before the same date a year later, and every claim must fall inside it.
• Rank plans by: annual cash cost, or account-adjusted cost, which also subtracts employer HSA deposits.
• Near-tie margin, optional: when the two cheapest plans differ by less than this many dollars a year, the status reads CHECK. Blank means 25 dollars; the limit is 0 to 10,000.
• Plans, P1 to P5, filled from the top; a plan is used when it has a name. Each plan has a synthetic name; the employee premium per pay period and its frequency (weekly, biweekly, semimonthly or monthly, with the standard 52, 26, 24 or 12 payments) and, optionally, a different number of payments from 1 to 53; the employer HSA deposit and the HRA funding a year, blank meaning 0; which costs the HRA reimburses (none, all covered cost share, the deductible only, or the medical cost share only); the individual and family deductibles and out-of-pocket limits, each family limit embedded or aggregate; and whether prescriptions share the medical deductible, have a separate one or have none, and share the medical out-of-pocket limit or have a separate one, with the separate amounts. A blank limit is never read as zero: enter every limit that applies.
• Services, S01 to S20: a name and a benefit class, medical or prescription. Prescription tiers are simply service rows.
• Plan rules, up to 100 rows, one for each plan and service a claim uses: whether the service is covered; the method (no cost share, coinsurance, copay, or copay and coinsurance); whether the deductible applies; for a copay with a deductible, whether the copay comes before or after the deductible; the copay per claim; the coinsurance rate from 0% to 100%; and whether the cost counts toward the out-of-pocket limit (blank means yes). The rows can be pasted from a spreadsheet, and each plan and service pair may appear once.
• Amounts already met this year, optional, up to 50 rows: for a plan and a member (M1 to M10), the medical and prescription deductible and out-of-pocket amounts already met, blank meaning 0. The family amount is the sum of the members'.
• Claim amounts are allowed amounts: confirm Yes. The allowed amount is the plan's negotiated price, not the provider's billed charge.
• Claims for the year, up to 100 rows: a claim ID (a capital letter followed by up to 15 capital letters or digits), the member, the date of service, the service, and the allowed amount from 0 to 10,000,000 dollars in whole cents. Use synthetic IDs, never names or diagnoses. The rows can be pasted and are put in date order, then claim ID order.
• Simulated years: up to six claim bundles, each with a name and a weight from 0 to 1, where the weights of the bundles you use must add up to 1; the bundle claims, up to 120 rows and at most 20 per bundle, with the same columns as the claims plus the bundle and in the same order; whether to vary allowed costs between years, off or by five factor levels; the five levels, each a factor from 0.25 to 4 with a weight, the weights adding up to 1; and an optional random seed from 1 to 2,147,483,646 (blank is 42).
• The calculation choice; the plan shown in the claim ledger; for the scenario grid, the reference plan and a different comparison plan, the allowed-cost multiplier step from 0.01 to 0.19 and the annual premium step from 1 to 100,000 dollars.

Money is in US dollars a year, except the premium, which is per pay period; rates are percentages.

Governing relationships

Order and accumulators. Claims are adjudicated in date order, and claims on the same date in claim ID order. The workbook checks this order and never re-sorts. Accumulators start at the opening balances at the start of the plan year. Each plan keeps a member and a family deductible and out-of-pocket amount for each set that applies: medical, and a separate prescription set when the plan maps prescriptions as separate.

Remaining deductible and out-of-pocket room. Before each covered claim with cost sharing,

remaining deductible = individual limit − member's deductible so far for employee only;

for a family tier, the family limit less the family's amount so far when the mode is aggregate, and the smaller of the member's and the family's remaining room when it is embedded. The remaining out-of-pocket room follows the same rule with the out-of-pocket limits. Neither is ever below 0.

Cost share before the out-of-pocket limit. With allowed amount A, copay C and coinsurance rate r:

• deductible then coinsurance: deductible = min(A, remaining deductible), then coinsurance = (A − deductible) × r;
• copay before the deductible: copay = min(A, C), then deductible = min(A − copay, remaining deductible), then coinsurance = (A − copay − deductible) × r;
• copay after the deductible: deductible = min(A, remaining deductible), then copay = min(A − deductible, C), then coinsurance = (A − deductible − copay) × r;
• with no deductible the claim skips that step, and with no cost share the plan pays the whole allowed amount.

Coinsurance is rounded to the cent.

The out-of-pocket limit. When the cost counts toward the out-of-pocket limit, the deductible, copay and coinsurance are posted in that order, each limited to the room left after the ones before it. What the limit removes is shifted to the plan, and the ledger shows it. Only the deductible actually posted is credited to the deductible; an amount the cap shifted to the plan is not. A cost that does not count toward the limit is neither counted nor capped.

Not covered. For a service the plan does not cover, you pay the whole allowed amount, the plan pays nothing, and no deductible or out-of-pocket amount moves. Scaling a claim never changes whether it is covered.

Every claim balances: what you pay plus what the plan pays equals the allowed amount.

HRA. After the claims are adjudicated, the HRA reimburses the eligible part of each claim's posted cost share in date order, earliest first, until the year's funding is used. It never feeds back into any deductible or out-of-pocket amount. Noncovered costs are not reimbursed, and unused HRA money is given no cash value.

Annual cost. For each plan,

annual premium = premium per pay period × number of payments

annual cash cost = annual premium + covered cost share + noncovered costs − HRA reimbursement

account-adjusted cost = annual cash cost − employer HSA deposit

An account-adjusted cost below zero is a net employer-funded account advantage, not a negative medical bill. Your own HSA contributions are never subtracted. Money totals are rounded to the cent. A plan's rank is 1 plus the number of plans strictly cheaper to the cent, so tied plans share a rank. For each pair of plans, the break-even premium change is the row plan's cost less the column plan's: the change in the column plan's annual premium at which the two would cost the same.

Simulation. Each of 100 years draws one bundle by its weight from a seeded random stream and, with factor levels on, one level by its weight; the same seed and inputs always give the same 100 years. The year's cost for every plan is that bundle's claims adjudicated at that level's multiple of every allowed amount. Each pair of bundle and level is adjudicated once, so the draws only choose among exact results. For each plan the workbook reports the mean, 10th, 50th and 90th percentiles, lowest and highest annual cost, the share of years in which it is cheapest, with tied years split equally, its standard error

standard error = √( p × (1 − p) / 100 )

and the mean and share for years 1 to 50 and 51 to 100 as a convergence check. It also reports the share of years with a tie, and how many years drew each bundle and each level.

Scenario grid. Rows multiply every allowed amount in your claims by 1 − 5 steps up to 1 + 5 steps, re-adjudicating the year under both plans each time. Columns set the comparison plan's annual premium to the reference plan's plus the actual difference, moved by −5 to +5 premium steps. Each cell is

comparison plan's annual cost − reference plan's annual cost

on the basis you rank by; negative means the comparison plan is cheaper. The premium axis is exactly additive, and the centre cell equals the base-case difference to the cent.

Calculation sequence

The spreadsheet first checks the inputs and every plan, service, rule, opening, claim, bundle and level row. It then builds each plan's rule for every service, lays the claims out in one adjudication ledger (the base year, then the bundles or the scenario multipliers the chosen mode needs), adjudicates every row under every plan, and adds up each plan's premiums, cost share, noncovered costs, plan payments and HRA reimbursement. In the chosen mode it then draws the 100 years or fills the scenario grid. A block of consistency checks confirms that no accumulator passes its limit, that no posted amount exceeds its amount before the cap, that the HRA never pays more than its funding, that the shares of years cheapest add up to 1 and that the grid's centre equals the base case, before the status is returned.

Outputs and interpretation

The headline is the cheapest plan for your year of claims, on the basis you rank by, and the gap in dollars a year to the next cheapest. The ranking table gives, for each plan, the annual premium, your covered cost share, noncovered costs, what the plan paid, the HRA reimbursement, the annual cash cost, the employer HSA deposit shown separately, the account-adjusted cost, the rank, and the deductible and out-of-pocket amounts credited and the out-of-pocket room left. The break-even table compares every pair of plans. The claim ledger lists every claim for the plan you choose: the cost-share order, the deductible, copay and coinsurance posted, the amount shifted to the plan by the out-of-pocket limit, what you and the plan pay, the HRA reimbursement and the family's running totals, and a table of the month-end totals beside the limits. The bundle table replays each bundle through each plan at the allowed amounts entered; it is filled for Calculate, and for Simulate without factor levels. Under Simulate the cards add the share of years with a tie, the years that failed to evaluate (always 0), the seed used and the number of years, with the simulation and draw tables. Under Scenario grid a note names the two plans and the units.

In the scenario grid, the first column gives each row's allowed-cost multiplier and the column headings count premium steps from −5 to +5; the premium step and the premium difference at each step are listed in the table beside the grid. A cell without a value is explained in the reasons table, which has the same layout.

Any warnings appear under the results. The model version and the other run details are in the downloaded CSV and JSON files. The per-row check tables are returned to the API and MCP, and the first problem they find is named in the status.

Under the default inputs, a family of four with a plan year from 1 January 2027 and 16 claims for the year, three plans paid biweekly are ranked on annual cash cost. The mid deductible plan with an HRA (150 dollars a pay period) costs 6,197 dollars a year: 3,900 in premiums plus 2,697 of covered cost share and 600 for a noncovered claim, less 1,000 of HRA reimbursement. The high deductible plan (95 dollars) costs 6,376 dollars, and the copay plan (210 dollars) 7,605 dollars. The mid deductible plan is cheapest, by 179 dollars, more than the 25-dollar near-tie margin, so the status is OK. On the account-adjusted basis the high deductible plan's 1,500-dollar employer HSA deposit would bring it to 4,876 dollars. In the bundle table the copay plan is never the cheapest: in the low use bundle the high deductible plan costs 2,743 dollars, and in the high use bundle the mid deductible plan costs 12,900 dollars against 13,460 and 15,470 for the other two.

The first chart breaks each plan's cash cost into its parts, with the HRA below zero. The second shows the spread of simulated annual costs, the third the deductible and out-of-pocket progression of the plan shown, and the fourth how often each plan is cheapest.

Validation and status logic

The workbook returns one status, chosen in this order: NOT VALID, then CHECK, then OK. It has no INFEASIBLE status, because it does not optimize.

• NOT VALID names the first problem it finds, checked in this order: the calculation choice, the allowed-amount confirmation, the coverage tier, the plan year start and the ranking basis; then the plan, service, rule, opening, claim, bundle, bundle-claim and factor-level rows (the levels only when they are in use), each naming its row; at least one plan; a required limit left blank, a deductible above its out-of-pocket limit, or an embedded individual amount above the family amount; a used service without a name or class; a used plan and service pair without a rule; a prescription rule that applies a deductible in a plan with no prescription deductible; opening amounts above their limits; an allowed amount not in whole cents; claims for more than one member under employee only; a ledger plan that is not entered; the seed, under Simulate only; the near-tie margin; the cost-factor choice; and, in the mode that uses them, a bundle with more than 20 claims, bundle or level weights that do not add up to 1, grid plans that are not two different entered plans, a step out of range, more than 60 claims in the scenario grid, and the workbook's adjudication capacity. Claim rows are refused for a gap, a missing value, an ID that is repeated or malformed, a date outside the plan year, or rows out of date-then-ID order. NOT VALID is a refusal, not a numeric answer: every figure is withheld.
• CHECK accompanies a computed result that needs the stated review: a model consistency check failed; the two cheapest plans cost the same to the cent, or differ by less than the near-tie margin; or only one plan is entered, so there is nothing to compare.
• OK otherwise.

The warnings list, under the results, reports a near tie, an account-adjusted cost below zero, HRA money left unused, noncovered claims, that the simulated shares rest on 100 illustrative years, that cost variation uses five levels rather than a continuous distribution, opening balances in use, an empty claim list, same-day claims taken in claim ID order, an out-of-pocket limit reached, costs that do not count toward the limit, and simulation halves that disagree. A warning does not change the status.

Assumptions and limitations

• Capacities are 5 plans, 20 services, 100 rules, 10 members, 100 claims for the year, 6 bundles of up to 20 claims each, 5 factor levels and 100 simulated years. The scenario grid replays at most 60 claims.
• Only in-network care is modelled: there are no out-of-network limits and no balance billing above the allowed amount.
• One plan year; claims outside it are refused. One employee premium per pay period applies all year.
• Only the posted deductible counts toward the deductible; there is no option for copays to count toward it.
• The HRA is fully funded on the first day, with no proration or rollover, and reimburses in date order up to its funding.
• Prescription deductibles and limits follow the medical family modes.
• Opening balances apply to every simulated and scenario year.
• Simulated years come from your bundles and weights and five factor levels; with 100 years a share has a standard error of up to 5 percentage points.
• The break-even figures move premiums only.
• Cents are rounded half away from zero.
• The calculator applies no legal maximum to an out-of-pocket limit; every limit is the one you enter.

Restrictions and non-computing states

The calculator enforces its numeric and choice limits before it accepts a result. A gap between rows, a missing rule, a blank required limit, claims out of order and weights that do not add up to 1 are refused by the workbook. The simulation figures are left blank unless Simulate is chosen, the scenario grid unless Scenario grid is chosen, and the bundle table when Simulate uses factor levels or the scenario grid is chosen.

Errors and warnings

A request rejected by the page's input limits is not a computed result. A transport or service failure, a timeout or a cancelled run cannot establish a fresh workbook answer, and the last result stays on the page marked as out of date. Review the workbook's References and Disclaimer, and your plans' Summaries of Benefits and Coverage, before using a result for a real decision.

Sources and further reading

Every premium, limit, rule, claim, bundle and weight on this page comes from your inputs; the calculator holds no plan's actual terms and no statutory limits. The public sources below define the plan terms it uses and explain how employer health accounts are treated. They do not endorse this calculator.

• HealthCare.gov glossary, Deductible defines the deductible as what you pay for covered services before the plan starts to pay, notes that some services are covered before it is met, and describes individual and family deductibles (retrieved 2026-10-27).
• HealthCare.gov glossary, Coinsurance defines coinsurance as your percentage share of the cost of a covered service after the deductible (retrieved 2026-10-27).
• HealthCare.gov glossary, Out-of-pocket maximum/limit explains that once you reach the limit the plan pays all covered in-network costs for the rest of the plan year, and that premiums do not count toward it (retrieved 2026-10-27).
• HealthCare.gov glossary, Allowed amount defines the allowed amount as the most a plan will pay for a covered service, and explains that a provider's charge above it may be billed to you; the calculator works only from allowed amounts and in-network care (retrieved 2026-10-27).
• U.S. Department of Labor, Summary of Benefits and Coverage and Uniform Glossary describes the standard summary each group health plan provides, which is where a plan's deductibles, copays, coinsurance and limits can be read (retrieved 2026-10-27).
• IRS Publication 969 (2025), Health Savings Accounts and Other Tax-Favored Health Plans explains that employer contributions to a health savings account are not included in your income and that a health reimbursement arrangement is funded by the employer alone and reimburses qualified medical expenses (retrieved 2026-10-27).
• U.S. Bureau of Labor Statistics, Employer Costs for Employee Compensation measures what employers spend on health insurance and other benefits as part of total compensation (retrieved 2026-10-27).

This is an educational comparison of the plan terms you enter, not insurance, tax, benefits or medical advice. Check every amount against each plan's Summary of Benefits and Coverage and plan documents before choosing a plan.