Medical Plans and Scenario Comparisons in One Sheet

Michael Data & Graphics November 6, 2018

The U.S. health insurance is like a maze. Projecting your next year’s health needs already invites a ton of anxiety. Then variables such as deductibles, out-of-pocket maxes, coinsurance levels and others work together in such an intricate system that it is never a straight forward A+B-C*D%. It could easily be hours, or even days, of post-dinner homework running the numbers. This year, however, I developed my own tool, a spreadsheet, to help me and my family do the math magic. So from this year onward, the math part of our health insurance shopping will be no more than a few minutes, even comparing a dozen scenarios. I also validated our pick last year using this spreadsheet with hospital bills for having a baby and we saved $3-4,000 compared with all three other plans.

We had a baby this past May, and bills from the hospital seemed never to stop coming. When I looked at all the numbers recently, the total of the full bill for having a baby shot north of $64,000, but of course thanks to the insurance, we just needed to pay a small portion of it, in our case $6,000 (max out-of-pocket for the $1,500 deductible plan). All insurance carriers have a contract with healthcare service providers/hospitals, etc. and they get a discount/negotiated rate out of the full bill. In our case, the $64,000 got reduced to $34,000, split onto two people, my wife and the baby. It’s about a 50% discount, but this depends on the types of work and services though. And this $34,000 is our (family and insurance company’s) total payment to the care providers/hospital for having a baby. The insurance company paid the most. To use the spreadsheet proficiently, pay special attention to this payment number, which is the member/discount rate of the full cost billed by care providers/hospitals.

COLOR CODES: Where to do what?

  • Green shaded cells: plug in the costs of the plans you are considering, and don’t forget your employer contribution to HSA, if you have it.
  • Green boxes (3) hanging on the right hand side of the main table: put your estimated full payments for only the two persons with the most expenses. Note: this payment number isn’t the hospital/care provider’s full bill. It is your insurance carrier’s negotiated/member rate on that full bill. For instance, our full bill of having a baby was $64,000, our payment for that was down to $34,000, most of which was paid for by the insurance company. Also note that you only need to enter two numbers here, that is the payments for the two who will account for the largest share of the family medical payments. (See some sample services and their full costs/member rates toward the end of the page.)
  • Red cells: you have maxed out individual or/and family deductibles/max out-of-pockets.
  • Pink cells: how much the plans cost in full (premium + out-of-pocket in your scenario) a year
  • Right above the pink row, see how much first three plans save vs. the $4,500 deductible plan (or lose if the number is in red/negative).

TWO SETS OF SCENARIOS (with my own plan numbers):

  1. If two or more in the family have a significant condition each, such as a hospital stay, a major surgery or multiple emergency room visits, or the equivalent of being hit with a payment of high thousands, the $1,500 deductible plan wins, beating the $4,500 deductible plan by up to $3,600/year.

    When two people have both maxed out out-of-pockets, $1,500 deductible plan saves $3,500 over $4,500 deductible (When two people have a $15,000 or more in payments each for the year, most plans will be maxed out; having a baby is very similar to this)

    When two people both have significant payments, $9,000 each, $1,500 deductible plan saves $3,200 over $4,500 deductible plan. (If they both have 3 ER visits each, copay plan saves $5,300 over $4,500 deductible plan, but need to calculate copay plan separately though, the formula doesn’t apply to it when it comes to ER visits.)

    If one person has maxed out, another has $3,000 in payments, $1,500 deductible plan saves $500 over $4,500 deductible plan

  2. If no more than one person in the family has a significant condition, then the $4,500 deductible plan wins, by up to $2,400 compared with the $1,500 plan.

    If only two people have had 1-2 sick visits or $300 in payments each, $4,500 deductible plan saves $2,400 over $1,500 deductible plan

    If one person has maxed out, aother has had a few sick visits or $600 in payments for blood work or tests, $4,500 deductible plan saves $1,800 over $1,500 deductible plan

Abdominal pain (emergency room) $10,075.37 $2,991.57
Asthma (emergency room) $7,355.37 $2,998.81
Strep throat (emergency room) $5,212.20 $2,320.98
Chest pain (emergency room) $3,453.53 $674.00
Constipation (emergency room) $7,985.08 $2,996.02
Dehydration (emergency room) $8,394.88 $2,996.02
Diarrhea (emergency room) $8,559.45 $2,990.10
Migraine (emergency room) $9,452.95 $2,991.26
Hypertension -high blood pressure (emergency room) $3,421.96 $1,183.28
Kidney stones (emergency room) $9,563.72 $3,007.73
Pneumonia (emergency room) $8,218.47 $2,991.19
Stitches for laceration (emergency room) $2,018.80 $674.00
Syncope – fainting or extreme weakness (emergency room) $11,070.85 $4,321.00
Urinary tract infection (emergency room) $7,118.42 $2,706.63
Viral Infection (emergency room) $6,701.69 $2,996.02
Total Knee Replacement (1 knee) – Inpatient $137,777.46 $19,312.88
Cataract/Lens Procedures $24,736.37 $2,758.50
Total Hip Replacement (one hip) – Inpatient $78,309.92 $17,773.53
Kidney Stone Treatment (Lithotripsy) $26,108.13 $3,758.41
Hernia repair (Inguinal Herniorrhaphy) $37,275.13 $4,687.16
Ear Tube Insertion (Myringotomy) $25,137.52 $3,237.00
Laparoscopic Cholecystectomy (Gallbladder Surgery) $47,671.88 $3,951.71


The spreadsheet and the scenario estimation may be a little oversimplified than real life, thus always consult with a healthcare/insurance professional or your carrier’s customer service reps for your plan-specific questions or situational medical inquiries. This exercise’s main goal is to make the math part of health insurance shopping process effortless, so you just figure out what scenario(s) you are most likely preparing for the coming year, or how risk averse you want to be in planning your family’s healthcare. (If you are shopping only for yourself, then change all the family deductible/out-of-pocket numbers to the individual numbers.)


  • Prescription drugs may not work here.
  • The copay plan’s total cost is exaggerated when it comes to ER visits and shall be manually calculated for ER payment estimates.
  • Tax savings of insurance premiums aren’t included.
  • All calculations are in-network.
  • If you estimate a third person in family would have quite a few sick visits or more than one ER visit, and the first two people haven’t yet maxed out on some of the plans, you’d need to add the third person’s math on your own to the plans that haven’t maxed out yet.

And here is the spreadsheet:

Medical plans_scenario calculators_PLUG IN YOUR NUMBERS. [Right click and save file as… in your local drive and open in Excel may be the best way.]

*** Is it just me, or can we all still hear Canadians chuckle?