Yet another retirement calculator

[aschearer]

I've been working on a retirement calculator and would appreciate your feedback. After trying another one I found on this forum I felt it was a tad too confusing, so I've tried to keep things simple. In particular I would like your help to find any mistakes I've made in my calculations.

Link to the Excel spreadsheet: http://1drv.ms/1rJKnMA

[YoungInvestor]

Row 21 is off. D22 should be D21*Rate of return. Right now it is C21*Rate of return.

C22 is fine for some reason, though.

[aschearer]

Thanks, hopefully I've fixed the mistake. Now the interest earned on assets (Row 22) is always the sum of the previous year's assets (interest earned, initial wealth, and contribution) multiplied by the rate of return.

[arebelspy]

Fairly simple and clean, I like it.

I wouldn't make the user input current spending as a negative amount, it can be confusing.  Just subtract it instead of adding it in your formula.

I'd also make the row 27 cells where you're below your target red, rather than no color, but I always do that (have it flip red to green), that's just a personal quirk.  :)

[aschearer]

Thanks for the feedback. I've updated the colors so they are red-green for the results row. I've also made all negative numbers positive and changed the math accordingly.

[MDM]

I've been working on a retirement calculator and would appreciate your feedback.

Currently suffers from the same problem many retirement calculators have: not set up to test if one can stay retired.  E.g. if B11, "projected income", = 0, #DIV/0! propagates widely.  If B11 = 1 (to avoid #DIV/0! by using a trivial amount), calculations in B13 and B14 are suspect.

But it does seem a good start....

[arebelspy]

I've been working on a retirement calculator and would appreciate your feedback.

Currently suffers from the same problem many retirement calculators have: not set up to test if one can stay retired.  E.g. if B11, "projected income", = 0, #DIV/0! propagates widely.  If B11 = 1 (to avoid #DIV/0! by using a trivial amount), calculations in B13 and B14 are suspect.

But it does seem a good start....

Can't you just make B13 = B11-B12? (Now that B12 is positive.)

That will give you the annual contributions.

And for B14, you can make it =IF(B11="N/A",0,ABS(B11+B12)/B11)

So it shows N/A (or whatever you want there) in the savings rate if your income is 0.

[aschearer]

Can't you just make B13 = B11-B12? (Now that B12 is positive.)

Yes, good idea.

Currently suffers from the same problem many retirement calculators have: not set up to test if one can stay retired.

Fair criticism. I've adjusted things to avoid dividing by zero, but more work is needed to so things make sense when income is zero and expenses are greater than zero. I'll update this thread once that's finished. My current thinking is that you should be able to set income to zero, keep expenses constant, and set starting assets to whatever your required nest egg is and get the expected results.

[ampersand]

Alright its official; I have to get a new laptop. Mine gets hot enough to fry an egg, and my version of excel is so old its lotus notes. (Ok i exaggerate but its Excel 2003).

Unfortunately I couldn't get your sheet to load; so I'll just throw out one this: the functions IFNA() and IFERROR() can be used to help avoid some of those pesky DIV/0 problems. If you save it as a 2003 copy and attach it to a post I'll take a look and see if there's anything I can try and do and help.

[MDM]

Alright its official; I have to get a new laptop. Mine gets hot enough to fry an egg, and my version of excel is so old its lotus notes. (Ok i exaggerate but its Excel 2003).

Unfortunately I couldn't get your sheet to load; so I'll just throw out one this: the functions IFNA() and IFERROR() can be used to help avoid some of those pesky DIV/0 problems. If you save it as a 2003 copy and attach it to a post I'll take a look and see if there's anything I can try and do and help.

Another holdout using Excel 2003 here also - but I was at least able to download and load on my desktop.  Did get warnings about some conditional formatting being beyond the abilities of Excel 2003, so the following may not be correct for more up to date versions:  Row 27 appears to "cry wolf" due to conditional formatting when row27 < row29 - even if net worth is increasing because row22 > row23.  Further, even if row22 < row23 for one or more columns, if SUM(AE21:AE23) > 0 and no row21 < 0 isn't that "overall success"?

Inputs I used for testing:
Starting Assets    $1,000,000
Current Age            30

Projected Income                           $-   
Projected Spending                       $25,000

Projected Growth Rate                    4%
Projected Inflation Rate                  4%
Projected Capital Gains Rate         15%

[CaptainFrugal]

My hardcore Mustachian opinion here, but I think everyone's situation is different. My wife and I currently save about 75% of our take home pay, will reach FI in less than five years, and as our wealth increases I find that avoiding taxes is my #1 strategic goal (saving is on auto-pilot at this point). I'll be retired before 40, so I'm not looking forward to 20+ years before I can withdraw tax free. 

I've been putting in the 17.5K into the 401K plus matching plus an IRA contribution for years, but I'm starting to become a little uncomfortable with how much of my net worth is tied up in inflexible retirement accounts. It's really tempting to cut back the contributions, but I can't bear to pay a 25% federal rate plus 4% state rate at this time plus paying a high marginal tax rate each year on the gains while I'm still working. I'd rather compound tax-free in the 401K while I still work.

My employer has terrible has terrible fund choices with high fees as several others have pointed out as well. My latest conclusion has been the following:

I plan to continue maxing the 401K contribution which I will roll over to an IRA upon retirement. I've been contemplating setting up a SEPP withdrawal plan on the IRA at retirement then structuring my non-retirement investments to mainly hold dividend paying stocks (current 15% tax rate), tax deferred oil and gas MLPs, plus possible rental real estate with its tax advantages. The SEPP IRA money will be taxed at far less than my current 29% marginal rate and will give us cash flow.

I think it's clear that maxing out tax-deferred retirement accounts provides the greatest tax benefit for almost everyone (roth or traditional), but the negative implication for the extremely young Mustachian retirees is not having access to a huge percentage of net worth tied up in accounts we can't access until age 59 1/2. Anyone else here considered a SEPP withdrawal plan?

[arebelspy]

I think it's clear that maxing out tax-deferred retirement accounts provides the greatest tax benefit for almost everyone (roth or traditional), but the negative implication for the extremely young Mustachian retirees is not having access to a huge percentage of net worth tied up in accounts we can't access until age 59 1/2. Anyone else here considered a SEPP withdrawal plan?

There are multiple ways to access it early, penalty free.

Here you go:
http://jlcollinsnh.com/2013/12/05/stocks-part-xx-early-retirement-withdrawal-strategies-and-roth-conversion-ladders-from-a-mad-fientist/

[MandyM]

I'll be retired before 40, so I'm not looking forward to 20+ years before I can withdraw tax free. 

You mean penalty free...

[aschearer]

Thanks for the feedback everyone. I've updated the spreadsheet a fair amount based on your and others' feedback. The main two things:

• I've switched things to present day dollars. Your wealth now increases by Growth Rate - Inflation Rate. Your target income and contribution to savings are both expressed in present day dollars.
• You can safely set income to zero. The calculator is still too aggressive with your retirement date -- it will tell you when your interest earned exceeds your target spending, but in reality retiring at that point may eat into your principal. Nevertheless you can now plug in values for your starting wealth and see if they will work.

Later I hope to adjust the color coding so that it doesn't go green until you can safely retire without touching your principal.

[arebelspy]

You won't be contributing to retirement after you retire.

So shouldn't row 23 be an if statement where if 27>29 (i.e. green), it's 0, if 27<29 (i.e. red) it's B13?

Of course someone may not retire right after they turn green, so they may want to see the effect of adding more.  But on the other hand it shows a skewed amount in the rest of line 21 and 27 if they do FIRE right away and are trying to see what assets they'll have a year or three later.

[MDM]

Later I hope to adjust the color coding so that it doesn't go green until you can safely retire without touching your principal.

That is certainly an assumption one can make.  The Trinity study, origin of the "4% rule", assumed "safely retire" if one's balance stayed above zero.  See http://en.wikipedia.org/wiki/Trinity_study: one has "succeeded if there are unspent assets at the end of the period."

Neither is right or wrong - just different.  Probably worth making that difference clear, lest a user assume something other than what the program assumes.

Or giving the user the option to choose among definitions of "safely retire".  Of course that would be making things "less simple" and thus perhaps detract from your goal for the tool....