Group health insurance comparison spreadsheet
Start with a fully populated fictional medical renewal: three plans, all four coverage tiers, employer contributions and employee paycheck costs. A separate blank workbook is available for your own case. Free downloads, no email required.
Download the completed four-tier example (.xlsx)Download a separate blank workbook for your own case
Want to see where the comparison goes next? Inspect the fictional source packet and actual Planlined proposal PDF. The sample is free to view and shows the finished client deliverable.
Completed example · all four coverage tiers
A full comparison you can inspect before signing up.
Ridgeline Works · 10 enrolled employees · 2027 medical renewal. Synthetic employer, carriers, plans and figures. No real client or employee information.
View every row and download the completed files here. No email, account or client upload required.
Monthly premium by coverage tier
| Coverage tier | Enrolled | Current | Renewal | Proposed |
|---|---|---|---|---|
| Employee only | 5 | $500.00 | $550.00 | $520.00 |
| Employee + spouse | 2 | $1,000.00 | $1,100.00 | $1,040.00 |
| Employee + children | 1 | $900.00 | $990.00 | $936.00 |
| Family | 2 | $1,400.00 | $1,540.00 | $1,456.00 |
| Group premium / month | 10 | $8,200.00 | $9,020.00 | $8,528.00 |
Employer contribution and employee paycheck cost
Proposed plan · employer pays 75% of each tier’s premium · 24 benefit deductions per year.
| Coverage tier | Premium / month | Employer / month | Employee / month | Employee / deduction |
|---|---|---|---|---|
| Employee only | $520.00 | $390.00 | $130.00 | $65.00 |
| Employee + spouse | $1,040.00 | $780.00 | $260.00 | $130.00 |
| Employee + children | $936.00 | $702.00 | $234.00 | $117.00 |
| Family | $1,456.00 | $1,092.00 | $364.00 | $182.00 |
Proposed annual premium$102,336.00
Employer annual share$76,752.00
Employees’ annual share$25,584.00
These are designed worked examples from manually prepared synthetic inputs. They are not customer outcomes or proof of automatic extraction. Review plan benefits and network terms alongside cost.
What the separate blank spreadsheet compares
The Plans tab holds the 22 fields that decide most group health decisions: network type, deductibles and out-of-pocket maximums for individual and family, office and urgent care copays, emergency room and inpatient cost sharing, four prescription tiers, out-of-network coverage, referral rules, and a Source row so every number points back to a page in the carrier document. The Rates tab takes monthly premiums by tier, and the Contribution tab turns an employer percentage or fixed dollar amount into the employee cost per pay period for 24 and 26 pay schedules.
How to fill it from an SBC and a rate sheet
Every plan's Summary of Benefits and Coverage follows the same federal layout, so the Plans fields map to the same rows on every SBC: deductible and out-of-pocket limit on page 1, office visits, emergency and hospital services on pages 2 and 3, and the drug tiers in the prescription section. Copy the in-network value and note the page number. Rates come from the carrier's rate sheet or quote; enter the four-tier monthly premium and the Contribution tab does the arithmetic. If you would rather not read SBCs by hand, the SBC comparison tool extracts these fields from the PDF.
The five analysis formats brokers build in Excel
Most benefit analysis in Excel is one of five layouts. This workbook is the first; the others are variations on the same Plans and Rates data.
- Side-by-side plan comparison. Plans in columns, benefits in rows. The format on the Plans tab of this workbook, and the one a client can decide from.
- Total cost analysis. Annual cost for a few representative employees (single, family, high and low utilisation) so the plan that is cheapest on premium can be checked against the plan that is cheapest in a bad year.
- Contribution modeling. The same rates under two or three employer contribution rules, with the employee cost per paycheck for each. The Contribution tab does this for one rule at a time.
- Premium rate comparison. Rates by tier across carriers, with the year-over-year change. Useful for the renewal conversation, useless without plan design beside it.
- Plan value scoring. A weighted score across coverage, network and cost. Only as good as the weights, which should be the client's, not the broker's.
The formulas, and what each one is for
| Figure | Formula | Note |
|---|---|---|
| Annual premium | =Monthly_Premium * 12 | Per tier, before any contribution. |
| Employer contribution | =Monthly_Premium * Contribution_Percentage | Or a fixed dollar amount per tier. Apply the same rule to every plan being compared. |
| Employee cost per month | =Monthly_Premium - Employer_Contribution | Divide by 24 or 26 for the per-paycheck figure employees ask about. |
| Employee cost per paycheck | =Employee_Monthly_Cost * 12 / Pay_Periods | The Contribution tab computes this for 24 and 26 pay periods. |
| Affordability check | =Employee_Only_Monthly_Cost * 12 / Annual_Wages | Compare with the year's affordability percentage under the rate-of-pay, W-2 or federal poverty line safe harbor. The ACA affordability calculator applies the current threshold. |
| Worst-case annual cost | =Annual_Employee_Premium + Out_of_Pocket_Maximum | The number that shows which plan actually caps a bad year. |
If the rates arrived as a table in an email or a portal, the rate sheet to spreadsheet converter moves them into a CSV that drops onto the Rates tab without retyping. How to read a carrier rate sheet covers what on that page is a rate and what is not.
Three mistakes comparison sheets make
First, mixing in-network and out-of-network values in the same row, which makes a PPO look worse than an HMO on every line. Second, comparing deductibles without out-of-pocket maximums, which hides the plan that actually caps a bad year. Third, showing employer cost without the employee's per-paycheck figure, which is the number employees ask about at enrollment. The workbook keeps those apart by design.
Where Excel benefit analysis breaks
Every number is typed by hand, so every number can be mistyped, and nothing in the sheet points back to the page it came from. A carrier revision means re-entering the plan. Contribution modeling beyond one rule needs formulas most people would rather not maintain. Two people cannot work the same file at once, and version names like "renewal FINAL v3" are how the wrong sheet reaches the client. None of this is a reason not to use a spreadsheet for four plans; all of it is why the spreadsheet is the wrong tool at twelve.
Common questions
Is the spreadsheet free, and does it work in Google Sheets?
Yes on both. It downloads directly from this page with no email, and the formulas are standard enough to open in Google Sheets as well as Excel.
What is benefit analysis in Excel?
Comparing health plans in a spreadsheet on plan design (deductibles, copays, coinsurance, out-of-pocket maximums), premium by coverage tier, the employer contribution and the employee cost per paycheck, and a worst-case annual cost. This workbook holds those on three tabs.
Where does a spreadsheet comparison usually go wrong?
Mixing in-network and out-of-network values in one row, comparing deductibles without out-of-pocket maximums, retyping a rate from the wrong tier, and showing the employer's cost without the employee's per-paycheck figure. The workbook separates those by design; the typing errors it cannot prevent.
When is it time to move off the spreadsheet?
When the renewal has more plans than one person can read SBCs for in an afternoon, when the same plan data has to be re-keyed into a proposal and an employee guide, or when two people need to work the same comparison. Planlined reads the carrier files into the same fields and returns values for you to check against the original document; source-page links may be unresolved.
When the spreadsheet stops scaling
Four plans by hand is an afternoon. A renewal with three carriers and two plan designs each is a week. Planlined reads the carrier quotes, SBCs and rate sheets into the same comparison automatically, flags any field it is unsure of so you can confirm it against the source page, and produces the proposal book and employee guide from the approved case. See the side-by-side benefit quote comparison feature, or start from the broader benefit comparison template if you also compare dental, vision, life and disability. For dental and vision on their own, see the dental and vision comparison guide.