Health Plan+HSA+403b Optimization - Help Review Spreadsheet

Non-investing personal finance issues including insurance, credit, real estate, taxes, employment and legal issues such as trusts and wills
Post Reply
Topic Author
csmath
Posts: 91
Joined: Sat Oct 13, 2018 11:32 am

Health Plan+HSA+403b Optimization - Help Review Spreadsheet

Post by csmath » Thu Nov 14, 2019 12:42 am

I've been curious about comparing LDHP + 403b scenarios with HDHP + HSA + 403b scenarios and tried making a spreadsheet. The result surprised me a bit and I was hoping one of you would be willing and able to audit it and give me feedback on it. Part of the reason why I was surprised is because I do not seem to be getting results that are consistent with this spreadsheet that prompted me to look further.

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
Last edited by csmath on Fri Nov 15, 2019 2:51 pm, edited 2 times in total.

Topic Author
csmath
Posts: 91
Joined: Sat Oct 13, 2018 11:32 am

Re: Health Plan+HSA+403b Optimization - Help Review Spreadsheet

Post by csmath » Thu Nov 14, 2019 1:01 pm

Shameless bump. I think the spreadsheet would be helpful for people if it is accurate... but I could use some help verifying it is at least appropriately accurate.

Maybe I need a clickbait title change... like:

"You're all wrong about HDHP's"

User avatar
FiveK
Posts: 7860
Joined: Sun Mar 16, 2014 2:43 pm

Re: Health Plan+HSA+403b Optimization - Help Review Spreadsheet

Post by FiveK » Thu Nov 14, 2019 8:40 pm

I believe the first spreadsheet is consistent with the 'HDHP Analysis' tab in the personal finance toolbox spreadsheet. Unless one person started with the other's, or independently made the same mistake, having two different tools that give the same result suggests (but doesn't prove) that each is correct.

What is it that doesn't look correct to you? Having a specific example may be helpful.

Topic Author
csmath
Posts: 91
Joined: Sat Oct 13, 2018 11:32 am

Re: Health Plan+HSA+403b Optimization - Help Review Spreadsheet

Post by csmath » Thu Nov 14, 2019 9:43 pm

FiveK wrote:
Thu Nov 14, 2019 8:40 pm
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%			
I enter the same numbers into "new" spreadsheet and set "years" = 0 to ignore the growth factor.

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				

		
Here is what I don't get... the original one shows that the HDHP is superior for ALL possible levels of Medical Costs. I would then expect on the new one that HDHP would also show a higher $$$ amount for all levels of Medical Costs. (Is this a logic error on my part?) But it doesn't. There are results showing that the LDHP plan is superior on the interval where "$$$ in play" is [$5000, $8500] and Medical costs are in the interval [$5500, $7000] and the surrounding cells also give HDHP a smaller advantage than I would expect.

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.

Topic Author
csmath
Posts: 91
Joined: Sat Oct 13, 2018 11:32 am

Re: Health Plan+HSA+403b Optimization - Help Review Spreadsheet

Post by csmath » Thu Nov 14, 2019 9:50 pm

FiveK wrote:
Thu Nov 14, 2019 8:40 pm
I believe the first spreadsheet is consistent with the 'HDHP Analysis' tab in the personal finance toolbox spreadsheet.
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.

User avatar
FiveK
Posts: 7860
Joined: Sun Mar 16, 2014 2:43 pm

Re: Health Plan+HSA+403b Optimization - Help Review Spreadsheet

Post by FiveK » Thu Nov 14, 2019 10:24 pm

csmath wrote:
Thu Nov 14, 2019 9:43 pm
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.
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 gets
Image

Topic Author
csmath
Posts: 91
Joined: Sat Oct 13, 2018 11:32 am

Re: Health Plan+HSA+403b Optimization - Help Review Spreadsheet

Post by csmath » Fri Nov 15, 2019 12:29 am

FiveK wrote:
Thu Nov 14, 2019 10:24 pm
Thanks for taking a look at it!

Post Reply