Will you save more with standard PPO or a high-deductible PPO Saver plan with a Health Savings Account?
It is currently Open Enrollment season at many workplaces, which is when employees choose their medical insurance and other benefits plans for the upcoming year. It’s also the time of year when people grumble “why is the US healthcare system so complicated” and just elect whatever plan they had in the prior year. Building a spreadsheet can help someone compare the costs and benefits of each of the plans under a variety of different assumptions about tax rate and healthcare expenditure.
Spreadsheet Inputs
How much each plan costs – Usually these are quoted per pay period – i.e. twice per month. Note that amounts that you pay for health insurance are tax-deductible – we will be adjusting everything to final post-tax dollars later on.
Health Care Plan Coverage Details – These are the details for how much each health plan pays for your healthcare. The key variables are the deductible (how much total cost you are responsible before health insurance pays a dime), the in-network coverage % (how much of your healthcare expenses you are responsible for after the deductible is met), and the out of pocket maximum (what the maximum amount you will have to pay yourself is during the year).
Marginal Tax Rate – This is the tax bracket you fall in, which will be important for calculating costs and benefits net of tax. It can be found on the IRS website. Note that you could also add in your state tax bracket here as many of the things that are deductible for federal taxes are deductible for state purposes too, depending on state.
Forecasted Health Expenditures – This is a number that will have some uncertainty (the point of health insurance is to protect against negative surprises in health expense), but can be guessed at based on historical averages or
Health Savings Account (“HSA”)– The key benefit of the PPO Saver High Deductible Plan (other than being cheaper on semi-monthly payments) is being allowed to contribute to an HSA. The key benefit is that HSA contributions are tax-deductible, and the HSA balance is allowed to be rolled over to following years and can be spent only on healthcare expenses. In addition, often employers will put in $500 or $1000 into the HSA as an added benefit of choosing the high deductible plan, which is cheaper for the employer as well.
Desired Spreadsheet Output – We want to calculate the sum of the total expected after tax costs and benefits of each plan to compare them.
Spreadsheet Logic
Annual Cost of Premiums, post tax – We multiply the premiums paid by 24 to get the annual total, then multiply by (1-tax rate), to get the after tax cost. We do this because the premiums paid can be deducted from our income for tax purposes, making them effectively cheaper. Comparing everything post-tax makes our analysis consistent.
Out of pocket expenses – this requires a little bit more logic. Working from the outside in for our formula below, the MIN() function says that if the calculated medical expense is above the annual out pof pocket maximum, you pay the out of pocket maximum. The IF() statement first checks if medical expenses are below the deductible, if so then you pay the medical expenses in full. Otherwise you pay the deductible + 10% of medical expenses above the deductible. Note that medical expenses are paid out of pocket post tax, so we don’t need to multiply it by (1-tax rate) as we did above.
Health Savings Account – Finally, the Health Savings Account. Any amount your employer contributes is a post-tax benefit. Any amount that you contribute can be counted as a benefit too, because it allows you to save on income taxes paid. Therefore, we multiply the amount you contribute by the income tax rate to find the tax savings benefit. We realize that one might argue that this money is now restricted and only earns minimal investment earnings, so the benefit might not be one to one, but the beauty of making your own spreadsheet is that you can adjust this benefit to any amount of your choosing.
And that’s pretty much it – we add the medical expenses, premiums paid net of taxes, and HSA benefits to get the net cost. In the hypothetical example we’ve been using (family plan), the PPO Saver seems to win by about $3,500 versus the normal PPO, with about $2,000 of that coming from the tax benefit from contributing to the HSA.
And of course, the requisite Data Table. Surprisingly, the PPO Saver seems to outperform the Regular PPO in every instance:
Here’s the spreadsheet: Health Insurance – PPO vs. PPO Saver