Here is the original spreadsheet that I used first(not mine):
https://docs.google.com/spreadsheets/d/ ... edit#gid=0
Here is the one I created from it, and the one I'm asking to be checked:
https://drive.google.com/file/d/1cUJN1s ... sp=sharing
The vertical axis is a raw medical bill amount and the horizontal axis is "$$$ in play". By this I mean the amount of money that will be directed towards, paying bills, maxing HSA, and contributing towards 403b. The "$$$ in play" does not include premium costs but I do include the premium savings in the calculations. The FICA is only set at 1.45% due to non SS employment. The top table shows how much better either the LDHP or HDHP is and color codes it to identify which plan was the winner after tax. Not inflation adjusted and only for a single year's impact.
On a side note, the second sheet compares the results of prioritizing the payment of Medical bills from taxable income first before tapping into the HSA funds. I wasn't sure if there were various scenarios where one would sometimes be better than the other or to what magnitude.
I'd hate to use it or share it if it has critical errors in it. The last two sheets were used to check the tables on the first sheet and are more informative in terms of how I performed the calculations.
Calculations based on these understandings:
Standardized Deduction / Not Itemized
HSA - No taxes ever
403b - FICA before contribution & Federal after distribution... no State Tax on 403b
Medical payments not from HSA are taxed with FED+STATE+FICA before being applied to payment
Ignores FSA availability (maybe add later)
LDHP Personal Responsibilities are best estimates. It ignores potential copays but also the fact that not all services apply the coinsurance either. Without calculating with specifics on what services are being used, an accurate cost seems pretty impossible.
Edited: 11_15_2019 to change link to updated version
Maybe I need a clickbait title change... like:
"You're all wrong about HDHP's"
What is it that doesn't look correct to you? Having a specific example may be helpful.
I appreciate you taking the time to look at it. If accurate I'm hoping it will help people visualize and optimize. I also like that it allows you to "invest" the difference to better understand what your giving up / getting long term.
Here is a specific example:
This is entered into "the original"
Code: Select all
Low Deductible Health Plan High Deductible Health Plan Premium (monthly) $368.40 Premium (monthly) $320.38 Deductible $1,500.00 Deductible $5,600.00 Co-insurance 10.00% Co-insurance 0.00% Out of Pocket Max. $3,000.00 Out of Pocket Max. $5,600.00 Annual FSA Contributions Annual HSA Contributions(HDHP Only) Employer $0.00 Employer $2,000.00 Employee $0.00 Employee $5,100.00 Total $0.00 Total $7,100.00 Taxes Federal Tax Rate 22.00% State Tax Rate 4.95% FICA Tax Rate 1.45% Total Tax Rate 28.40%
Code: Select all
LOW DEDUCTIBLE HEALTH PLAN (LDHP) HIGH DEDUCTIBLE HEALTH PLAN (HDHP) $4,420.80 Annual Premium $3,844.56 Annual Premium $1,500 LDHP deductible $5,600.00 HDHP deductible 10% LDHP coinsurance 0.00% HDHP coinsurance $3,000 LDHP maximum out-of-pocket $5,600.00 HDHP maximum out-of-pocket $7,100.00 HSA maximum annual contribution limit $2,000.00 employer contribution to HSA GLOBAL VARIABLES 0 years of growth 7.00% CAGR 22.00% federal tax bracket in retirement 12.00% federal tax bracket for current medical expense 4.95% state tax (403b are State Tax Free contributions & distributions in Illinois) 1.45% FICA tax
Perhaps part of the difference is that I am using "my" spreadsheet with the expectation that enough $$$ is being tax-deferred to force the medical bills to be payed with money taxed at a 12% Fed rate whereas "the original" spreadsheet applies all taxation at the same 22% Fed rate.
Also, it may help to understand that this was made for a "Boglehead" who would be interested in leveraging the tax efficiency of the HSA and a 403b/457b. The $$$ in play is a bucket of money left after paying the insurance premiums. The premium savings for HDHP gets added to it behind the scenes.
For LDHP the $$$ in play is first used to pay personal responsibility for medical costs and all the rest goes into the 403b.
For HDHP the $$$ fills up the HSA first. Then the left over money tries to pay for the medical costs out of pocket and the remainder goes into the 403b. If there wasn't enough money to pay the medical bills OOP then the remainder of the medical costs are paid from the HSA.
Yes the first one is consistent with the toolbox one and I used the same tables to calculate the cost of the medical bills in my spreadsheet. Do you think this is even a worthwhile endeavor? I'm constantly surprised how many people around me "like what they have". I"m not pushing this on anyone but every year when open enrollment comes up people talk about it a lot and everyone goes with the "I hear that" or "I think" type of discussion. I thought a colorful, visual representation might be helpful if they want to look at it.
Like many mini-projects, for me, I'm learning a lot personally in the process. Specifically I'm learning a ton about taxes. For example, for whatever reason I never realized that my state never taxes 403b/457b plans... ever. Not the contributions or distributions.
Appears you have figured it out. The toolbox spreadsheet also shows the HDHP wins for all expenses with a 28.4% marginal tax rate, but if that changes to 18.4% one getscsmath wrote: ↑Thu Nov 14, 2019 9:43 pmPerhaps part of the difference is that I am using "my" spreadsheet with the expectation that enough $$$ is being tax-deferred to force the medical bills to be payed with money taxed at a 12% Fed rate whereas "the original" spreadsheet applies all taxation at the same 22% Fed rate.