Retirement Planning Calculator

by Kevin on February 22, 2008

Yesterday I Googled “retirement calculator” so I could run some numbers. I wanted to pick an amount that we could contribute to our Roth IRAs each year — say, $3,000 each — and discover where we would stand in retirement. As you might expect, there were plenty of calculators to be had. The one I enjoyed using the most came from CNN Money.

I liked this calculator the most primarily because:

  • it asks for a lot of data
  • it shows you where your portfolio would stand at differing options (10% chance of knocking the ball out of the park with incredible returns, 25% of nice returns, 50% of this actually happening, etc.)

We have little saved for retirement — and that’s okay. With roughly $1,400 in a 401k (3% contribution), $3040 in a Roth IRA, and my wife’s retirement account with the state (5% contributed), we’re just getting started at ages 23 and 22. I put our ages in at 24 and 23 in the calculator because it was taking it off of what age we would turn this year. (Our birthday’s are in April and May.)

When input our data and add $6,000 in total Roth IRA contributions per year, we get some very favorable results. The end result is we should be able to have 70% of our pre-retirement income if our investments net a return of only 3.3%. This is extremely encouraging.

Do It In Excel (or Other Spreadsheet Software)

There is a way to do some of these calculations in Excel that I want to show you. You can calculate the future value of present dollars, plus any additional payments. It won’t account for inflation, at least not easily. Open up Excel (or OpenOffice Spreadsheet) and click on a cell. Type everything but the quotes at the beginning and end: “=FV(8%,42,-3000,0,0)”. What you are telling Excel you want to know is what is the future value (FV) of a constant return (8%) for the next 42 years (66-24=42) with constant payments of $3,000. The 0’s at the end are saying there is essentially no money in the account yet. And yes, you do put -3,000 in the formula or you end up with a negative number.

What do you come up with? $912,730.57. Not too shabby for only $3,000 per year.

There is a second way to estimate the future value in Excel. Essentially you calculate the gain for each individual year and run the calculation until X year of retirement. You can download the Excel file I did the work in, too. (It also shows the future value formula at the bottom.)

For the second method, you are going to calculate the beginning balance, contribution (payment), return, and ending balance for every year. Once you do it for the first two years, you can drag the results down to auto-complete for the next 20, 30, or 40 years.

roth ira calc

This results in a slightly different end number that the future value formula: $1,067,848. I’m not sure why there is a difference, so maybe someone who reads this can explain. However, they are pretty close.

The Catch:

  • This will show you what the future value of today’s dollars would be. It does not account for inflation. Essentially you are assuming that you will contribute the equivalent of $3,000 of today’s dollars for all of the years. So fifteen or twenty years from now, $3,000 of today’s dollars could be $4,800 or $5,500.
  • This assumes a constant rate of return: 8%. Some years you may earn 15%, others you may lose 20%. It could average out to 8%, but that would affect the calculations. This is where the CNN Money calculator can help.
  • The younger you are when you start, the better. If I waited 10 years until I was 34, then the end result is only worth $472,880. A difference of almost $600,000. So get out of debt, and get started.
  • Waiting longer to retire is also extremely beneficial. If you look at the Excel file I put up, the last year the investment gain is $79,000! If I wait one more year, I’ll earn another $85,000+. There is some serious money at the end.

I hope this helps dissect some of the numbers for you. Additionally, now you know there are at least three different ways to guesstimate at your retirement nest egg (and you can do two yourself).


DanL February 22, 2008 at 8:47 am

I love determining retirement planning–something I help out my first-time home buyers with. Another interesting way to look at the retirement figures is to determine what people spending on “discretionary” items, such as coffee, manicures, pedicures, gym memberships, etc.

Take the figures they save by NOT paying for 1 or multiple discretionary items and invest it towards retirement, its amazing to see how much these luxuries ACTUALLY cost over time as opposed to if you invest those funds.

Philip February 26, 2008 at 11:55 am

I really like the calculator, but one thing I have never been able to find a calculator to do, not exactly sure how either, is to take in donating based on percentage, say 10% of salary, but also to account that you will be getting pay raises, I hope that my pay rate grows faster than inflation and therefore doing 10% would be better in 10 years that what it is at the moment.

I may try to play with excel and add some rows to change calcutions for 3% inflation with 5% raise and see what happens. BTW, the FV calc vs excel comes out with about a 5% error on the number I am running now!

Russ May 28, 2008 at 11:25 am

There were two reasons your spreadsheet got a different result than the FV() function of Excel.

First reason is, in “=FV(8%,42,-3000,0,0)” the FIRST zero at the end says there is no money in the account yet (present value), but the SECOND zero says add money at the END of each period. Change that to a one and you say add money at the BEGINNING of each period. The FV() will give $985,749.02 if you use “=FV(8%,42,-3000,0,1)” changing that last digit. (Good lesson, it makes a lot of difference contributing at start of year, doesn’t it.)

Second reason is, you added a year to your spreadsheet, you decided to make your last contribution when you were 66 which would be 43 contributions. In the 42nd year of your spreadsheet (when you haven’t turned 66 yet) you should show the $985,749.02 that FV() calculates, and with the 43rd contribution and that extra year of earning, you’ll get the $1,067,848.94 total.

Either way… it makes me wish I was still young. 🙂

Comments on this entry are closed.