A spreadsheet that will help you pick the right health insurance plan


A spreadsheet that will help you pick the right health insurance plan insurance

So there I was, age 25, working for the first time at a company
large enough to offer health insurance, and presented with more
choices than a Gatorade vending machine. Choosing the right
health insurance plan is notoriously tricky — the choice is
heavily dependent on your employer’s offers and your own needs
— so Googling "best health insurance" was out of the
question. I asked my friends what they thought of their plans,
but got answers that were so specific as to be useless: Reasons
ranged from "my physical therapists takes this insurer" to "the
hospital is close to my house." Recommendations were out too.

Finally, I decided to approach my health insurance plan like an
optimization problem. Given my preferences, needs, risk
tolerance, and budget, which plan would be most affordable? From
this question, the health insurance spreadsheet was born.

Want to see the spreadsheet itself? View it here.

Obviously, I could fall victim to any number of diseases, which
might require weekly visits to specialists, months-long hospital
stays, or worse. But I worked under the assumption that if
something exceptionally bad happened, I’d be facing hefty bills
no matter what plan I chose, so I limited the list to situations
that I could imagine happening without a new, major diagnosis.

The final list ranged from costs I knew I’d pay (premiums,
primary care visits) to specialty work (labs, outpatient surgery)
to emergencies (ambulance, hospital stay) to medications
(generic, preferred, etc.).

Next, I added in plan details like out-of-pocket maximums,
co-insurance, and annual deductibles. This would help me with
calculations down the line.

I also wanted to consider the fact that I might have to change
doctors. If I switched to an HMO, I couldn’t keep my current physician or
choose my new doctor. To capture this, I added switching doctors
and doctor choice to the list of scenarios.

Now came the grunt work. I filled in the cost (or limit, or
co-insurance) for each plan I was considering, taking my
information from the plan comparison sheet I received.

Every optimization problem starts with constraints. On a new tab
of the spreadsheet, I wrote out the maximum I was willing to pay
in premiums, and the most I’d pay annually in a worst-case
scenario (that is, if I were medically screwed, I wouldn’t be
financially screwed as well).

I also entered how much I valued keeping my current doctor and
being able to choose my doctor, to put a dollar value on the
intangibles. Finally, I estimated how long I’d be on that health
plan, to amortize those intangible costs.

Of course, the "best" health insurance plan depends on how much
medical care you need. An HMO is best if you’re totally healthy,
but springing for an expensive PPO might actually save money if
you have complex or long-term health concerns. To capture this, I
imagined what I’d need every month in a best-, medium- and
worst-case health scenario.

Best case: I’m taking prescription medications
and typically see my primary care doctor for a checkup once a
year even if I’m healthy. For this scenario, I estimated I’d fill
two prescriptions a month and see my doctor once a year (that is,
I’d see her 1/12th time per month). In a best-case
scenario, I wouldn’t need any other care.

Medium case: That’s all well and good, but what
if I have a seizure and dislocate my shoulder, requiring an
emergency room visit, follow-ups with specialists, physical
therapy, and mental health care (which happened a few years ago)?
I based this hypothetical on my own history, as something that’s
bad but not completely crazy to imagine.

Worst case: Here, I estimated what I’d need if I
had some sort of complicated, severe disease that required lots
of tests and specialists, an ambulance ride and ER visit, and a
month’s stay in a hospital. I chose the worst scenario I could
imagine, without being so expensive that my choice of insurance
wouldn’t matter.

I’ll spare you the formula-building, but I calculated the cost of
each plan in each scenario, taking into account premiums,
out-of-pocket maximums, deductibles, etc. Feel free to use the
same calculations if you decide to build your own.

Once all the numbers were crunched and the formats made
conditional, I could see at a glance how much each plan would
cost me in each scenario. I could also see whether it met my
budget constraints (monthly premium and annual total cost).

I was deciding between three plans: an HMO, a basic (silver)
PPO, and a fancier (gold) PPO. Unsurprisingly,
the one HMO plan I considered was the cheapest in a best-case
scenario. There’s no point paying higher premiums if you don’t
need much medical care. However, if things went south, the HMO
got really expensive, really fast.

But here’s the big takeaway: Even in a worst-case scenario, the
silver PPO beat out the gold. Given my medical needs, the gold
plan’s lower deductible and out-of-pocket maximum weren’t enough
to compensate for the higher premium.

So after all the blood, sweat, and typing, I’d cut through the
Gregorian knot of insurance codes, co-pays and catastrophes to
find the best health insurance plan for me. Well…

Okay, so I’m no healthcare expert. I’ve had enough experience
(and costly surprises) with my insurer that I can somewhat
reasonably guess what services I’d need under what scenarios. But
while I knew the difference between generic and brand name
prescriptions, and I think I’d heard of formulary drugs on NPR,
the finer points were beyond me.

Turns out, that one small detail — classification of
prescriptions — had a massive impact on my healthcare costs.
I take two generic prescriptions a month, so I dutifully entered
"2" into the generic prescriptions spreadsheet cell.

But there are different shades of generic. One of my
prescriptions was a preferred specialty generic drug, and those
two extra words were the difference between a $15 and $90 co-pay.

Yep. Should’ve done my homework before I got too excited about my

I learned the hard way that small misclassifications can mean big
increases in healthcare costs. If you’re trying to choose a
health insurance plan, it’s well worth your time to highlight
costs you know will come up — seeing specialists, labs, and
of course prescriptions — and can move the needle on your
out-of-pocket costs. Once you’ve identified potential pitfalls,
here are some strategies for accurately pinpointing how much
you’ll pay, and potentially lowering your costs.

Nobody will be surprised by the complexity and idiosyncrasy of
health insurance plans. And as I found out, even the most
diligent spreadsheet construction can fall short. However, I hope
this can help you as you navigate through the American healthcare