Renewal Analysis Tool

The Complete Renewal Picture

Compare your current plan, the quoted renewal, and up to three offer options side by side. See the quoted number, the negotiated number, and the all-in cost — including plan design changes, offsets, and the member-level impact by tier. Options with zero enrollment are automatically left out of the analysis.

How This Works — 2 Minutes to Set Up

Grab these four things first:

  • Current rate sheet or invoice — your plan design and monthly rates today
  • The renewal letter — the quoted rates for keeping the same plan
  • The proposed option(s) — the plan grid your broker presented
  • Your contribution schedule — what employees pay per month, per tier

Then it’s three moves:

  1. Answer the three questions below — the table shapes itself to your offer
  2. Type your numbers over the gray examples — every cell shows the format it expects, and the example disappears the moment you start typing
  3. Run the analysis — the quoted number, the negotiated number, and the all-in picture, for every seat at the table
Don’t have every number? That’s fine. The assumption fields (visit counts, average visit costs) fall back to reasonable defaults if you leave them blank — the gray example IS the default. The only things the tool truly needs from you: rates, enrollment, and contributions. Nothing you type leaves your device.
How many plans are in the new offer?
Does the employer fund an HSA / HRA?
Is there a gap / supplemental plan?
Step 1 — Your renewal, column by column

Current → Renewal (as quoted) → your offer options. Rename the option columns to match your offer ("Base," "Mid," "Rich PPO" — whatever fits). The columns and offset rows shown match your selections above. The gray text in each cell shows the format — type your own numbers over it. Cells that differ from Current highlight gold automatically.

Plan Provision Current Renewal as quoted — same plan offer option 1 offer option 2 offer option 3 — 0 enrolled = excluded
Plan Build
Carrier / Plan
Deductible (Ind / Fam)
OOP Max (Ind / Fam)
Primary Caree.g. "$20 Copay" or "$35 Copay after deductible"
Specialist
Emergency Room
Rx Copays
Plan Richness (AV)% of typical costs covered — Gold≈80, Silver≈70
Monthly Rates — total premium per enrolled
EE Only
EE + Spouse
EE + Child(ren)
Family
Employee Paycheck Contributions — monthly, what comes out of the check
EE Only contribution
EE + Spouse contribution
EE + Child(ren) contribution
Family contribution
Enrollment — current census, quoted-renewal census, and the split across options
EE Only enrolled
EE + Spouse enrolled
EE + Child(ren) enrolled
Family enrolled
Employer Offsets — softening dollars per option (enter 0 if none)
Gap plan — employer cost$ / enrolled / month——
Gap plan — covers up toInd / Fam per year——
HSA / HRA employer fundingInd / Fam per year——
Step 2 — Utilization assumptions (drive the copay-conversion math — tune per group)

How often does a typical household use care in a year? If you're not sure, leave these blank — the gray numbers are sensible defaults and the math will use them. Young workforce? Type lower numbers. Older or family-heavy census? Higher.

Per Household / YearEE OnlyEE + SpouseEE + Child(ren)Family
Primary care visits
Specialist visits
Rx fills
Step 3 — Persona scenarios (the "Three Real Years" family cards)

These build the three "what a family actually pays" cards in the results. Blank = the gray defaults. The heavy year is always modeled at the plan's out-of-pocket maximum — no inputs needed.

Family ScenarioPCP VisitsSpecialist VisitsRx FillsMedical Event $ ded-eligible spend
Healthy year
Normal year
Heavy yearalways modeled at the OOP max ceiling———hits OOP max
Step 4 — Context that turns numbers into meaning

Payroll and wage turn the renewal into CFO units (% of payroll, per-hour cost). The two "5 years ago" fields power the creep tracker — pull them from an old invoice, or skip them. Visit-cost fields can stay blank; the gray defaults are typical contract rates.

For the "% of payroll" translation. Rough is fine.
For "cost per employee per hour" and wage-equivalent lines.
For the multi-year creep tracker.
Whole-group annual spend back then.
Contract rate a member pays pre-deductible. ~$150–200 typical.
Pre-deductible contract rate. ~$250–350 typical.
Blended pre-deductible cost per fill.
Share of care at full price. 50–70% typical.
Pick the honest comparison for the room — national, small-firm, or type your own regional/industry figure.
Takes 2 seconds · No login · Your numbers stay on your device
🔎

The Complete Picture Appears Here

Quoted → negotiated → all-in, then the view for every seat at the table

—
—
The Quoted Renewal
Same plan, new price
—
—
The Negotiated Number
Blend of the offered options
—
—
The All-In Number
All parties, all shifts, all offsets
quoted → negotiated → all-in
—
All-in change as % of payroll
—
Per employee, per working hour
—
Wage-cut equivalent, option-1 family
—
All-in change per employee per year
How the Negotiated Number Was Built — the full waterfall
Copay-conversion cost = (contract rate − old copay) × your utilization assumptions × share of care occurring pre-deductible, for services that moved from flat copay to "after deductible," weighted by enrollment across all included options.
Total Cost of Care — Who Pays What
The total rarely goes down. The slices just move.
Current Plan
—
estimated total cost of care
The New Reality
—
estimated total cost of care
Employer premium (net of contributions) Employer offsets (gap + HSA/HRA) Employee paycheck contributions Employee out-of-pocket exposure
Plan Richness — What a Dollar of Premium Buys Now
Benchmark — Your Lowest-Option Deductible vs. the Market
Member Impact — Line by Line, by Tier
Paycheck + plan design + offsets = what each household actually absorbs vs. today, for every included option
Red = new annual cost landing on the member. Green = member comes out ahead. Copay-conversion line uses your utilization assumptions. Net line = paycheck change + deductible shift + copay conversion − gap − HSA.
Three Real Years — A Family, Option by Option
Same household, three kinds of year (your Step 3 scenarios). Estimated out-of-pocket, after each option's offsets.
Healthy and Normal years use your Step 3 visit counts and event spend. Heavy year models the worst case at each plan's family OOP max. Offsets applied where they help. Estimates, not projections.
The Five-Year Creep — This Renewal Is Not an Event, It's a Pattern
The Counterfactual

The Summary for the Decision-Maker

The renewal was quoted at —. The offer as structured lands the premium number at —. Counting the plan design changes, the offset dollars now funded, and the cost that moved to members, the all-in change is —.

A multi-option offer with a funded base plan can be exactly the right strategy. This analysis isn't an argument against the deal — it's the honest accounting that should sit next to it, so the trade is made on purpose and the offsets reach the people carrying it.

Educational estimates only — not a quote, offer, actuarial certification, or guarantee. Plan documents govern. AV figures are user-entered approximations of metal-tier actuarial value. Benchmark figures are user-selected (KFF 2025 Employer Health Benefits Survey presets provided). Utilization, persona, and contract-cost assumptions are editable inputs; results scale accordingly. Options with zero enrollment are excluded from all calculations. Dillingham Benefits · cpalm@dillinghambenefits.com · 918-200-5631