How to Use a Spreadsheet to Compare Health Insurance Plans
Gather Plan Data
The very first step you will want to take is gathering all that confusing data. Don’t worry, though. I’ll walk you through it.
Follow along by making a copy of this Google Sheet and inputting your own numbers along the way.
Premiums
A premium is
One will be the sticker price of the plan. This is what you would pay if you were offered no subsidies.
You’ll also see your subsidy amount, which is how much the government is willing to write off every month based on your income. Somewhere near this subsidy amount, you’ll probably see an equation subtracting the subsidy from the sticker price.
This is your effective monthly premium and the amount you’ll pay to your insurer each month.
Copayments
Every time you visit a medical professional, you’ll likely be asked for a copayment. Your insurance is covering most of your costs, but you pitch a little in, too.
You may see up to six co-payment options on your plan for those times when you need to consult with a medical professional. These are in-network and out-of-network charges for each of the following types of visits: Primary Care Physician (PCP) visits, Specialist visits and ER visits.
You’ll notice that on my chart, I do not include out-of-network copays. I assumed that all of your visits would be in-network for simplicity’s sake, but if that’s not possible in your area, remember to factor out-of-network costs in.
You’ll also notice that some of my “copayments” are decimals. That’s not because you’ll only owe ten to twenty cents as a copayment. It’s because in some instances on some plans, coinsurance will be implemented rather than a copayment.
For example, ER visits in our examples operate via coinsurance rather than traditional copays. You’ll come out of the hospital paying 10%-20% of the total bill, with your insurer picking up the rest.
Deductibles
If you have a deductible, you’ll have to pay it before coinsurance kicks in. Be careful as you may have a copay for your overall doctor’s visit, but tests like MRIs or blood tests may not be covered under that copay. This is when you’re staring down a deductible, and you’re not going to get that sweet 10%-20% coinsurance deal until it’s met.
Out-of-Pocket Maximum
After you’ve met your deductible, you will use your coinsurance until you reach your out-of-pocket maximum. When you hit the max, insurance should start picking up the entire bill as long as it’s a covered expense. There are separate out-of-pocket maximums for individuals and families; be sure to know which is which so you can plan healthcare expenses accordingly.
Gather Your Own Data
There’s no way to predict what your healthcare expenses will look like over the next year. The best we can do is estimate, and a common way to estimate is by looking at past expenses. It’s an imperfect system, but short of a crystal ball, it’s one of the better options we’ve got.
Average Monthly PCP Visits
How many times have you been to the PCP over the past year? How about your spouse? Remember not to count your annual visit, as if you have an ACA plan, these annual visits should be free.
For our specific sheet, add up your total visits outside of your annual checkup and divide by twelve to get your average monthly number.
Average Monthly Specialist Visits
Do the same thing for specialist visits. Remember that certain appointments will also be covered sans copay or coinsurance if you are on an ACA plan. For example, if you’re female, your annual visit to the OBGYN is covered.
Average Monthly ER Visits
You should calculate ER visits in the same manner. The ER is where you’re most likely to see coinsurance rather than a copay. Often, copays are in the hundreds, and even at ten-percent coinsurance, ER visits can get pricey quickly and unexpectedly. Coinsurance isn’t necessarily more expensive; it just depends on if you’ve already met your deductible for the year and the end cost of your individual visit.
Crunch the Numbers
This is where the magic really happens. This is the sheet with all the formulae which will help you figure out which plan is likely to be the cheapest for you.
Note: If you are consistently on prescription medications, you will also want to add in prescription coverages and costs. You should be able to find your copays and/or coinsurance for different classes of drugs in your plan—the text of which you should have received via snail mail in print but is likely accessible via your online account with your insurer, as well.
Annual Premiums
The formulae for this category are simple. You start by multiplying the monthly premium by twelve for each month in the year. When you type ‘Costs!B2’, you are pulling from the first tab which is labelled ‘Costs’. The data in B2 on that chart is $641.27.
12*$641.27=$7,695.24
Therefore, your annual premium is $7,695.24.
For the other two plans, you will pull from the same row but different columns. So their formulae will look like the following:
=12*(Costs!C2)
or
=12*(Costs!D2)
PCP Visits
Here I’m going to make an assumption. I’m going to assume that when you visit your PCP, the cost your coinsurance is based on will be $250. If you know how much you are charged per PCP visit to the penny.
Then you’ll need to add the factor of your coinsurance:
=250*(Costs!B10)
Note: If you know how much you are charged per PCP visit to the penny, you can replace the highlighted section of this formula with a single number: your cost per visit.
Tell Sheets to multiply this by the total number of visits your family makes per month on average:
=250*(Costs!B10)*Personal!D2
For the next plan, you will use the same formula only you’ll swap the copays out. So you would change the ‘B’ to a ‘C’.
When you get to the final column, you’ll need to apply a different formula since you’re working with concrete copays rather than percentage-based coinsurance. You know you made four visits to your PCP last year where you would have to make copays. You know that because you can find it in cell D2 on your Personal sheet:
=(Personal!D2)
You also know that your copay is $60 per visit. You know this because you can find it in cell D10 on your Costs sheet:
=(Costs!D10)*Personal!D2
When Sheets multiplies these numbers together, it displays the total amount you would have spent on PCP visits over the past year under this plan. In this example, that number is $240.
Remember, after you input your own data, the numbers on this final sheet will automatically update, and will no longer match the numbers you see in this article. The formulae will remain consistent, though.
Specialist Visits
The formulae for this one are going to be very similar to those used for the PCP visits. You’ll just be pulling data from different cells as you’ll be working with the copay and coinsurance for specialists rather than PCPs.
Individual Deductible
Because you have to meet your deductible before coinsurance will kick in, you’ll have to pay the max individual deductible before insurance will cover anything. The chart will pull you individual deductible automatically.
However, on this chart, Partner 2 has all of their deductibles zeroed out as they did not visit the ER at all. Be sure to edit this if your situation is different.
If you have a larger family or plan on having a lot of medical expenses in the next year, you may need to calculate your family deductible into your equations, as well.
ER Visits
After Partner 1 meets their deductible, their coinsurance will kick in for the rest of their ER visits. To write this formula, we start by multiplying the estimated costs of $2,000 times two ER visits with the following formula:
=(Costs!B15*Personal!D4)
Then, we’ll subtract the amount we’ve already paid to meet that individual deductible:
=(Costs!B15*Personal!D4)-(B5)
Finally you factor in your coinsurance from the Costs page:
=((Costs!B15*Personal!D4)-(B5))*Costs!B13
The “B”s in this formula will change depending on the healthcare plan. So in column C you’ll replaces the “B”s with “C”s, in column D you’d replace the “B”s with “D”s, etc.
These formulae are already written for you and should calculate automatically after you’ve inputted all other data.
And the winner is…
In my example, the cheapest healthcare option in Insurer 2’s Advantage Health Savings Account (HSA).
When you have a health savings account, the money you put aside for deductibles and other healthcare costs will not be taxed for income purposes.
However, we’re assuming that health costs will stay stagnant. If you have a rough year, the cost/benefit analysis may change. Insurer 1’s HMO only costs $300 more per year if everything goes right (see Applied Costs!B8, Applied Costs!C8, but if everything goes wrong your deductible is $300 lower (see Costs!B5, Costs!C5.)
Not only is the deductible lower, but the coinsurance is lower, too, so it will take you longer to reach that out-of-pocket max. That’s a good thing. It means your insurer is covering more of your costs as you incur them.
However, you cannot use an HSA with Insurer 1’s HMO. That may prove to be incredibly relevant or irrelevant depending on your individual tax situation.
Tagged: health insurance, Spreadsheets