401k and Taxable Account Calculator

[erikdhoward]

I have created a combined excel sheet that incorporates both a 401k and Taxable Account for Early Retirement planning purposes, and I would love your input.

A few caveats:

• Most items are linked via formulas
• Items in RED should be adjusted to your own situation
• Calculations do NOT include inflation (i.e., all values are in TODAY'S dollars)

Critique away!

https://www.dropbox.com/s/r2dptzpn3yijc4l/Retirement.xlsx

[Heart of Tin]

Since you didn't specify in your post, I'll assume that you want me to critique this as a general retirement planning document, not a retirement document specific to you.

Initial thoughts:

• Not everyone is 28. Allow the user to fill in their age at the top, then drive the ages below that by formulas. (=A7+1 in A8, =A8+1 in A9, etc.)
• What's with the blue line? Does it signify something? If so, use conditional formatting so that when the spreadsheet changes you don't need to reformat by hand. If not, get rid of it.
• What if my taxable or 401k accounts aren't starting from zero?
• Where can I put my IRA?
• Where can I put my ROTH account(s)?
• Some people are in tax brackets past the 28% bracket. Further, some people are married, married filing jointly, or heads of house. There are different tax brackets for all of these statuses.
• The AGI column is misleadingly named. Investment and retirement income count towards your AGI, yet column C shows $0.00 after salaries stop. Further, some deductions and benefits do not count toward AGI. For instance, the standard deduction and personal exemptions affect your taxable amount, but not your AGI. AGI is important, because it determines your eligibility for certain deductions, credits, etc. I think the spreadsheet would benefit from an actual AGI column, but what you have in column C is not AGI for the purposes of Form 1040.
• Your Tax formula is overly complex. Try using multiple columns instead of doing all that work in one cell. Or you could use a nice lookup formula to condense the if functions.
• There isn't much flexibility here. What if I get married or divorced creating all kinds of new tax implications? What if I change jobs, and my new employer has a different 401k match? What if I change jobs in the middle of the year, and I need two different 401k situations in the same year? What if my deductions and benefits change? etc.
• The term "annual return" is confusing to me. It looks like you're going for the nominal return rate compounded monthly as opposed to the effective annual return rate. I usually use the effective rate, so that threw me for a loop.
• About your inflation assumption: if all values are in today's money, then the annual return on investment should be the real return, not the nominal return that we usually use. A 6% real return would probably require nearly 100% equities which is an unusual asset allocation. Just a thought. You should make it clear that the return rate should be inflation adjusted.
• Since you're already compounding monthly, why not change the formula in column H to, for instance, =FV($H$3,12,(-F8+I7)/12,-(H7),1) in cell H8 so that withdrawals are taken out monthly?
• Total withdrawals, in general, should be 4% of total net worth, not 4% of each account. If money is concentrated in the 401k during retirement but before 65 withdrawals tend to be very small. Then they increase quite a lot at 65 since you are now able to access the 401k. In cases like this, you should be able to take more from the taxable account since you only need it to tide you over until 65. Maybe use something like =IF(OR(C7=0,C7=""), IF($A7 > 64, H7*4%, MIN((H7+P7)*.04,H7*1/COUNTIF($A7:$A$200, "<65"))),0) in cell I7, for instance. To even out the payments even more, maybe you could use =IF(OR(C7=0,C7=""), IF($A7 > 64, H7*4%, MIN((H7+P7)*.04,PMT(MIN(.04,$H$2), COUNTIF($A7:$A$200, "<65"), -H7, , 1))),0) in cell I7.
• Currently the employer match does not change based on the employee's yearly contribution. I suggest the following formula in cell M7 =IF(L7>0, MIN(B7*P$2*P$3,L7*$P$3),0).

[erikdhoward]

Wow - thanks for the suggestions! I will run down the list over the weekend. Once I have an improved version, I will post again. Thanks again!