Author Topic: I made a spreadsheet that graphically compares Insurance Plans  (Read 9008 times)

v8rx7guy

  • Handlebar Stache
  • *****
  • Posts: 1614
  • Location: Bellingham, WA
I made a spreadsheet that graphically compares Insurance Plans
« on: November 13, 2015, 07:09:23 PM »
It’s November so that’s open enrollment time for me at my company. This year is the first year that they are offering an HDHP plan with HSA in addition to the “PPO hi” and “PPO lo” plans offered in the past. I was very excited to hear this news because I have read quite a bit about them here and felt that since my family is generally pretty healthy that it might be a great opportunity to save some money on premiums as well as eventually build up a little “medical nest egg” for the future. To sweeten the deal, my company adds a $2,000 as a bonus for signing up for the HSA.

Being the geek that I am, I took the decision making process to the nth degree and decided to set up a little excel spreadsheet to compare the plans. I decided to plot each of the 3 plans on top of each other with potential yearly medical expenses vs. resulting cost out of pocket including premiums. The results make the decision pretty clear to me.

A couple of things that I noted right off the bat:
1. HDHP + HSA is hands-down the best plan for yearly medical expenses less than $4,000. Athough it's not shown, in this range is where the residual HSA funds start building up.
2. HDHP + HSA is the best plan for yearly medical expenses less than $18,000, but it is not significantly different from either the PPO_Hi and PPO_Lo plans in this $4,000-$18,000 range (only $800 different from worst case to best case)
3. For years with very high medical costs ($30K+), the PPO_lo is best with the lowest OOP Max at about $8,800 , HDHP would hit the pocket book the worst with a OOP Max of about $10,500
4. PPO_Hi is only the superior plan between about $18K and $32K in yearly expenses.

Anything else jump out at anyone?

Here is a link to my "beta" google spreadsheet.  It is set to "view only" but if you save a copy (file:save a copy) you should be able to edit it with your own values:

https://docs.google.com/spreadsheets/d/1ZErow8X8GUzvYMGxQgOnqh8SjGRh-I-cCKmT8uiS-jM/edit?usp=sharing
« Last Edit: November 14, 2015, 02:43:05 PM by v8rx7guy »

sunday

  • Stubble
  • **
  • Posts: 202
Re: I made a spreadsheet that graphically compares Insurance Plans
« Reply #1 on: November 13, 2015, 07:21:29 PM »
Great job. This could be useful for a lot of people.

v8rx7guy

  • Handlebar Stache
  • *****
  • Posts: 1614
  • Location: Bellingham, WA
Re: I made a spreadsheet that graphically compares Insurance Plans
« Reply #2 on: November 13, 2015, 07:36:48 PM »
Great job. This could be useful for a lot of people.

Thanks!

Maybe someone else can post up their two or more plan options and I'll beta test my spreadsheet before making it public :)
« Last Edit: November 13, 2015, 07:44:55 PM by v8rx7guy »

Frs1661

  • Stubble
  • **
  • Posts: 115
Re: I made a spreadsheet that graphically compares Insurance Plans
« Reply #3 on: November 14, 2015, 05:01:45 AM »
I'd be interested in an open source version. If you want some plans to look at, I'm eyeballing the hdhp and hmo here: http://www.aetnafeds.com/results.php?zip=22315&member=Yes

How do you account for different spending categories? E.g. prescriptions vs pcp visits vs inpatient...



Sent from my Nexus 4 using Tapatalk


jorjor

  • Bristles
  • ***
  • Posts: 351
Re: I made a spreadsheet that graphically compares Insurance Plans
« Reply #4 on: November 14, 2015, 08:10:06 AM »
This is great! A couple suggestions if you're going to make it open source, in order of complexity (easiest first). I am assuming the spreadsheet shown are for a non-single plan (family, employee + spouse, etc).

1. I don't see the benefit of tax savings from an HSA contribution anywhere. You get a $2,000 employer contribution, but can contribute another $4,750 to the HSA plan in 2016 (assuming family coverage) and use that money to pay medical expenses tax-fee. In the non-HSA plans, your expenses might not be high enough to be tax-deductible. Adding this would require the user to input their HSA contribution and marginal tax rate (including the avoidance of payroll taxes, if contributed through a cafeteria plan), and would be a direct benefit to the HSA option.

2. Assuming this is a family plan, there was a regulation clarified in the past year that would further benefit the HSA plan in some cases. The maximum allowable out-of-pocket for ACA plans is $6,850 in 2016. Regulations clarified that, even though a family plan may have a higher OOP max than $6,850, a single family member cannot be charged more than $6,850 in cost sharing in any one year. Where does this come into play? Assume you have an employee + spouse in your $10,000 max OOP HSA plan. One has a chronic condition which will exceed $10,000 in costs. The other is super healthy and will only utilize annual free preventive care. That first person can only be charged up to $6,850 in cost sharing. The effective cost of the HSA plan would go way down. Adding this would basically add more dimensions to the chart since the cost of plans would differ by each person's cost. The more I think about it, it adds a significant layer of complexity. Maybe I'm only including it here so people can keep in mind that a portion of the high HSA family OOP maxes are avoided in some instances, which should be considered when picking a plan for high healthcare utilizers. A simplifying assumption would be to add an input for the percentage of total costs incurred by each family member, so the user can play around with that.
« Last Edit: November 14, 2015, 08:21:58 AM by jorjor »

jorjor

  • Bristles
  • ***
  • Posts: 351
Re: I made a spreadsheet that graphically compares Insurance Plans
« Reply #5 on: November 14, 2015, 08:20:18 AM »
I'd be interested in an open source version. If you want some plans to look at, I'm eyeballing the hdhp and hmo here: http://www.aetnafeds.com/results.php?zip=22315&member=Yes

How do you account for different spending categories? E.g. prescriptions vs pcp visits vs inpatient...



Sent from my Nexus 4 using Tapatalk

It wouldn't matter for plans that just use coinsurance percentages rather than flat dollar copayments. One can just choose their expented costs along the continuum, and the deductible/coinsurance amounts would be applied accordingly. Different categories do matter when the copay amounts are different by category. At a previous job when I was working with self-insured employers, I had to create things like this when the client wanted some sort of plan comparison module on their open enrollment website. Those plans often had flat dollar copays instead of coinsurance percentages, which becomes a pain in the butt because you have to make assumptions about number of visits, unit cost for each of those visits, etc. There are a lot of different possibilities to consider. I wouldn't go down that rabbit hole if I were the OP.
« Last Edit: November 14, 2015, 08:22:25 AM by jorjor »

JZinCO

  • Pencil Stache
  • ****
  • Posts: 701
Re: I made a spreadsheet that graphically compares Insurance Plans
« Reply #6 on: November 14, 2015, 09:18:03 AM »
This is pretty useful. Anthem has a similar online calculator that uses your employer's plans and information from your avg. annual claims to find an optimal plan.

v8rx7guy

  • Handlebar Stache
  • *****
  • Posts: 1614
  • Location: Bellingham, WA
Re: I made a spreadsheet that graphically compares Insurance Plans
« Reply #7 on: November 14, 2015, 09:32:30 AM »
I'd be interested in an open source version. If you want some plans to look at, I'm eyeballing the hdhp and hmo here: http://www.aetnafeds.com/results.php?zip=22315&member=Yes

How do you account for different spending categories? E.g. prescriptions vs pcp visits vs inpatient...



Sent from my Nexus 4 using Tapatalk

I can't find the maximum OOP for those plans listed anywhere?

v8rx7guy

  • Handlebar Stache
  • *****
  • Posts: 1614
  • Location: Bellingham, WA
Re: I made a spreadsheet that graphically compares Insurance Plans
« Reply #8 on: November 14, 2015, 09:43:55 AM »
This is great! A couple suggestions if you're going to make it open source, in order of complexity (easiest first). I am assuming the spreadsheet shown are for a non-single plan (family, employee + spouse, etc).

1. I don't see the benefit of tax savings from an HSA contribution anywhere. You get a $2,000 employer contribution, but can contribute another $4,750 to the HSA plan in 2016 (assuming family coverage) and use that money to pay medical expenses tax-fee. In the non-HSA plans, your expenses might not be high enough to be tax-deductible. Adding this would require the user to input their HSA contribution and marginal tax rate (including the avoidance of payroll taxes, if contributed through a cafeteria plan), and would be a direct benefit to the HSA option.

2. Assuming this is a family plan, there was a regulation clarified in the past year that would further benefit the HSA plan in some cases. The maximum allowable out-of-pocket for ACA plans is $6,850 in 2016. Regulations clarified that, even though a family plan may have a higher OOP max than $6,850, a single family member cannot be charged more than $6,850 in cost sharing in any one year. Where does this come into play? Assume you have an employee + spouse in your $10,000 max OOP HSA plan. One has a chronic condition which will exceed $10,000 in costs. The other is super healthy and will only utilize annual free preventive care. That first person can only be charged up to $6,850 in cost sharing. The effective cost of the HSA plan would go way down. Adding this would basically add more dimensions to the chart since the cost of plans would differ by each person's cost. The more I think about it, it adds a significant layer of complexity. Maybe I'm only including it here so people can keep in mind that a portion of the high HSA family OOP maxes are avoided in some instances, which should be considered when picking a plan for high healthcare utilizers. A simplifying assumption would be to add an input for the percentage of total costs incurred by each family member, so the user can play around with that.

Yes, the plans that I am simulating in the original post are family plans because that is what I am currently looking at for my situation.  The spreadsheet that I have also has a 2nd tab that calculates the individual plans that are offered because I had a few people asking for those to be evaluated at work as well... they are very similar plot profiles pretty much at 1/2 scale.

I struggled with what to do with personal additions to the HSA up to the allowable $6,750... because we have that option as well (as with all HDHP + HSA plans).  The problem is that I don't really consider those to be "out of pocket" because they don't disappear when you add them to your HSA, they are still funds which belong to you.  I decided to leave that out because it gets slightly more complicated and kind of misrepresents "total out of pocket costs" for a casual observer.  Though I do agree there is another level to this, I'm just not willing to go there right now :P.

Did not know about the ACA $6,850 OOP for individuals, and not exactly sure how that would come into effect, sorry :(


jorjor

  • Bristles
  • ***
  • Posts: 351
Re: I made a spreadsheet that graphically compares Insurance Plans
« Reply #9 on: November 14, 2015, 10:12:57 AM »
No need to apologize. Just adding input. It's a useful tool. Good work. In fact, I presented at an industry conference recently and one of the sections I presented was about HDHP decision considerations. One of the slides had almost that exact chart for an HSA vs two hypothetical non-HSA plans.

sol

  • Walrus Stache
  • *******
  • Posts: 8488
  • Age: 43
  • Location: Pacific Northwest
Re: I made a spreadsheet that graphically compares Insurance Plans
« Reply #10 on: November 14, 2015, 10:36:53 AM »
Just to add one data point of confirmation, my own analysis of health insurance vs health care costs gave me very similar results.  The HDHP+HSA was the clear winner for any normal year of family health care expenses.  We would need to have a major catastrophe, like a family car accident that sent multiple people to the hospital for an extended time, for any other plan to be cost effective for us. 

v8rx7guy

  • Handlebar Stache
  • *****
  • Posts: 1614
  • Location: Bellingham, WA
Re: I made a spreadsheet that graphically compares Insurance Plans
« Reply #11 on: November 14, 2015, 01:58:20 PM »
That is really cool/helpful!  If you could put the template on Google Docs, I'd be most grateful.  I'm trying to decide what plan to choose for next year.

I am going to make a few adjustments to make it more universal, but will make it open source in the very near future

v8rx7guy

  • Handlebar Stache
  • *****
  • Posts: 1614
  • Location: Bellingham, WA
Re: I made a spreadsheet that graphically compares Insurance Plans
« Reply #12 on: November 14, 2015, 02:43:36 PM »
Just updated the original post with a link.  Here is a link to my "beta" google spreadsheet.  It is set to "view only" but if you save a copy (file:save a copy) you should be able to edit it with your own values:

https://docs.google.com/spreadsheets/d/1ZErow8X8GUzvYMGxQgOnqh8SjGRh-I-cCKmT8uiS-jM/edit?usp=sharing