Excel Formula to calculate 401K Withdrawal amount to factor in Federal Income Taxes

Hi,


I'm considering retiring and I'm looking for a way to calculate how much money I will need to withdraw from my 401K Plan if I need say, $40,000 per year and my tax bracket is say, 25%.

The way I see it now is that if I withdraw $40,000 and I have to pay Uncle Sam say 25% of it (25% of $40,000 = $10,000) I'm left with only $30,000. But I actually need the $40,000 for the year to live on. That means that I'll have to withdraw more than $40,000. Right?

Certainly there is a formula to do this calculation in Excel. Or, if someone can explain to me the Math around this I might be able to figure some kind of formula (I'm not so great at Excel, though). If a spreadsheet already exist, that would be great.

Many thanks to anyone who can help me with this. Much appreciated.

Cheers!

Hi Pepe Le Mokko

Thanks.

How did you come-up 0.75?

53333.333 dollars is correct as I used the following method used in the attached Spreadsheet which I do not like. There got to be a formula method so I can plug in any Tax % and any desired withdrawal amount.

Once again, many thanks.

Hi Pepe Le Mokko

Thanks. It works. Much appreciated.

Cheers!

Originally Posted by Par

I'm considering retiring and I'm looking for a way to calculate how much money I will need if I withdraw $40,000 and I have to pay Uncle Sam say 25% of it (25% of $40,000 = $10,000) I'm left with only $30,000. But I actually need the $40,000 for the year to live on. That means that I'll have to withdraw more than $40,000. Right?

More generally, the formula is:

=40000/(1-t)

where "t" is the marginal tax rate (e.g. 25%).

If you understand basic algebra, the explanation is: You need to withdraw "x" such that:

x*(1-t) = 40000

So:

x = 40000/(1-t)

Hi joeu2004

Got it! Thanks very much. Much appreciated.

I suppose you'll do the same if I had to factor in State Tax Bracket as well, that is:

x=$40000*(1-(Ft1+St1)

Thanks.

Hi joeu2004,

This is good. I like it. Thanks. I go ahead and factor in the State

You guys are the best on this Forum.

Cheers!

Hi joeu2004,

I thought I'd share this piece of information I found on the web with you since you were so kind to help me out this morning:

How to Calculate Your Combined Federal and State Marginal Tax Bracket

The tax rate schedules list tax rates for Federal income tax only. It does not include state income tax. To account for "overlapping" levels of taxation (i.e., the fact that you can deduct state income tax payments on a federal tax return if you itemize deductions), to determine your combined (federal and state) income tax bracket, use the following formula:

(100% - Federal tax bracket) x State tax bracket = Effective State bracket

For example, if you are in a 3.5% state tax bracket and the 25% Federal tax bracket, your effective state tax bracket would be 3.00%, computed as follows:

(100% - 25%) x 3.5% = .75 x .035 = .03 = 3.00%

Next, add your effective state bracket to your federal tax bracket to determine your combined tax bracket using the following formula:

Federal tax bracket + Effective state bracket = Combined (state & federal) tax bracket

In the above example of a taxpayer in the 25% federal tax bracket and a 3.5% state tax bracket, the combined tax rate would be computed as follows:

25% Federal rate + 3% Effective state rate = 28% combined tax rate.

Cheers!

Originally Posted by Par

to determine your combined (federal and state) income tax bracket, use the following formula:
(100% - Federal tax bracket) x State tax bracket = Effective State bracket
[....]
Federal tax bracket + Effective state bracket = Combined (state & federal) tax bracket

Yes. And based on that, you could use one of the following equivalent formulas to determine how much to withdraw in order to have 40,000 after taxes:

40000 / (1 - t - s*(1-t))
or
40000 / (1-t) / (1-s)

But that assumes the state income tax is deductible. That is, you itemize federal deductions, and your federal AGI is below some threshold, depending on your filing status.

So, for example, if your federal marginal rate is 25% and your state marginal rate is 9.3%, and the withdrawal does not affect those rates:

1. Assuming state income tax is not deductible, but it is, would result in an over-withdrawal of about 1420.

It would result in an over-withdrawal of at least 3400 if the withdrawal pushes you into a higher federal marginal rate and we apply that rate to the entire withdrawal, another simplification that I suggested.

2. Assuming state income tax is deductible, but it is not, would result in an under-withdrawal of about 1370.

Since you did not provide sufficient details (understandably), I made the conservative and simplifying assumption that state tax is not deductible.

For my own planning, I prefer to somewhat over-estimate after-tax income rather than under-estimate it. But reasonable people might disagree.

Hi joeu2004,

Please indulge me for a bit. I do not understand:

So, for example, if your federal marginal rate is 25% and your state marginal rate is 9.3%, and the withdrawal does not affect those rates:

1. Assuming state income tax is not deductible, but it is, would result in an over-withdrawal of about 1420. How can state income tax is not deductible, but it is?How did you come up with 1420 or close to it?

It would result in an over-withdrawal of at least 3400 if the withdrawal pushes you into a higher federal marginal rate and we apply that rate to the entire withdrawal, another simplification that I suggested.

2. Assuming state income tax is deductible, but it is not, would result in an under-withdrawal of about 1370. How can state tax be deductible, but it is not? How did you come up with 1370 or close to it?

Please see Row 38 thru 47 of my attachment. Can you modify to demonstrate what you meant, and re-attach?

Thanks.

PS....

Originally Posted by joeu2004

See the calculations in "pre-tax 401k-IRA withdrawal.xls" (click here) [1].
[....]
[1] https://app.box.com/s/vdk68zbkhi24mfbox4e0m72m9b9b6ym5

If you already downloaded the file, you might want to download it again.

I improved the description of when the state income tax is deductible, but ineffective. The change avoids some ambiguity.

Hi joeu2004,

Thanks. I got it now. Sorry, I didn't mean to get into the Tax theory bit.

Thanks for the Box posting. I'll be able to work with this now.

Cheers!