Need withdrawal formula

[hmr2662]

Hi,

I am finishing up a retirement calculator and realized that I have to incorporate the following logic into my retirement withdrawals:
1) When a retirement withdrawal is required, pull 92% of needed monies from 401k balance, 4% from IRA balance and 4% from Roth IRA balance - DONE
2) The problem comes in when the required withdrawal for 401K monies, for example is GREATER THAN the 401K ending balance for that year.
----Currently, the formulas are pulling that entire amount needed, creating a negative balance in 401k balance. That does not make sense. It can only pull from available funds. Then it must pull from IRA and ROTH IRA funds. The only resulting Broke@ condition should be the first year when ending retirement balance is <0
3) Similar formula logic is needed for IRA and ROTH IRA

I am so close to finishing this but this last piece of the puzzle makes sense since all numbers in retirement accounts must be "0" before you go broke.

My attempt at the formula was this below but it did not work:
=IF(B21>$C$6+$C$5,"",MIN(IF(AND((ISNUMBER(AD21)),AE21<=G21),AE21,G21)))

AD21 = total retirement withdrawal needed
AE21= expected 401K withdrawal
G21=Ending 401K balance

File attached and cells in question highlighted in yellow.

Thanks
HR

[TMS]

I'm not sure that I can help you ... but I would point out that keep asking the same, or very similar question, without providing more information is unlikely to get you an answer. I would suggest that you go back to the other threads, add links to the latest thread, and then mark them as solved.

Then you need to review whether or not you have explained clearly enough what you want.

Having looked at the spreadsheet, I find it difficult to follow the logic of the formulae. I would suggest that you perhaps define some Named Ranges for the static values so that you can refer to them by name ... might make it clearer.

You also need to check some of your fomulae and fix them. For example, O52:

Please Login or Register  to view this content.

shows #VALUE!

This is because N52 evaluates to a blank and using that with a plus sign (+) causes the error which is propagated. If you use +, then SUM is redundant and vice versa. However, SUM can cope with non-numeric values, so use SUM(O51,M52,N52) not SUM(O51+M52+N52).

You also have things like: =IF(B22>$C$6+$C$5,"",T22-IF(ISNUMBER(U22),U22,0)). The IF(ISNUMBER(U22),U22,0) could be reduced to N(U22).

As I say, I think you need to audit all your formula a) for errors (as above) and b) for logic. That might help you find the reason for the negative values.

Regards, TMS

[hmr2662]

Thanks TMS,

I will check these formulas. I am cleaning them up a bit and using named ranges where I can. I realize that these are quite complicated conditional formulas and welcome any expert advice on how to clean them up.. I will rework it this weekend and post an update for further clarification.

HR

[hmr2662]

TMS,

I am resolving this one and linking it to my new post for "cannot resolve circular reference Cell AP35" as I think I have all the logic worked out but for some reason, this final formula is pointing back to itself.
HR

[hmr2662]

TMS,

I am resolving this one and linking it to my new post for "cannot resolve circular reference Cell AP35" as I think I have all the logic worked out but for some reason, this final formula is pointing back to itself.
HR