Retirement Calculator

Unprotect the sheet (no password) and unhide some columns to see the calculations.

Update: Changed download file to round to thousands, show the amortization schedule, and change the PMT function (make your life expectancy one more than your years to retirement in the old file and you’ll see why).

Posted in Uncategorized

7 thoughts on “Retirement Calculator”

1. dermot says:

Dick, having built some of these, I have a few suggestions for you:
* contributions are usually made monthly, so you should include half a year’s interest on each contribution, when it is made
* the same goes for pension payments (PV function assumes annual in arrear, as I recall)
* what about expenses and taxation and social security? (I’m not being picky, they can have a huge impact). Unless this is a toy model, they may be important.
*the pension in retirement doesn’t increase with inflation, which it should do. The easiest way to allow for this is to use the PV function with a rate equal to rate of return less inflation rate, ie 2% in your example
* the results can be misleading because they are all in future dollars. \$25,000 per year may sound OK, but in today’s dollars it’s about half that. I’d suggest always showing results in today \$
* life expectancy isn’t just a number, it’s a set of mortality rates, which are not hard to include if you want to make it more realistic.
* as far as assumptions are concerned, 9% would be a very high long term rate of return here in Australia – we would probably use something like 7%- and 3% salary increase would be a bit low – we assume 1% higher than inflation, or about 4% currently.
* it’s useful to show people how much the results depend on the assumptions, and give the results using (say) a different rate of return. I would also round the results to ‘000, so people see clearly that they are just very rough estimates.
* from a design viewpoint, I’d put the table below the assumptions, because if someone adds an assumption row in the middle, it will break the table (which I wouldn’t hide, either – some people find the details interesting)

Sorry about all the comments – I’m not trying to criticise, but these calculators can be surprisingly difficult to get right!

2. This is just depressing! ;-).
I was thinking the other day that I have another 40 years to work, after which I’ll almost certianly die within 5 years!
All that saving, and as soon a you pop your clogs it’s gone, what a stich up!

3. All good points dermot. Now let me tell you why you’re wrong. :)

I think compounding more frequently than a year implies some precision that isn’t there. Beginning of period, end of period, I don’t think impacts it significantly. So why am I showing pennies? Great point, I’m changing that to round to the thousands.

Expenses like expenses of the investment? That’s in the RoR. Taxes and social security? No way. I don’t know what it’s like down there, but in the US modeling the tax code takes seven Cray supercomputers. There are just too many variables for that to be meaningful and I think the user has to take these results into a model that’s more personalized. Heck I can’t predict what my tax rate will be in 30 years. And I expect my SS benefits to be literally zero, although others aren’t that pessimistic.

So you think I should take my \$25k per year and discount that for inflation between now and retirement? What will that tell me? That I’ll have the purchasing power at retirement equal to having \$15k per year today? I guess that’s OK. It seems overly complex. People know that stuff costs more in the future and this introduces yet another estimate (inflation) into the mix. Someone sell me on this point because I’m on the fence.

Mortality rates are great for insurance companies, but not so much for individuals. Because I’ve reached the age of 39, they say I should last until I’m 76. But if they knew my grandparents, they’d know I’ll be dragging those averages down, not up. I think it’s best to let the individual determine life expectancy. Besides, they’ll always guess high and it will make them save more than the need to.

Putting the table below is the right move and I think I’ll change that. I actually made this for a specific person (who doesn’t find the details interesting), but it’s a good point that many would.

4. dermot says:

> Now let me tell you why you’re wrong

Actually, Dick, you didn’t show anything I said was wrong, only that you chose to oversimplify. This is fine for yourself, but when you blog it for others to see, you should qualify the accuracy. As it stands, the calculator gives very misleading answers.

It is not “overly complex” to allow for inflation, when it *halves* the actual value of the result. That is not trivial. It also doesn’t necessarily introduce another element, because you could instead show the result as X.X times final salary (based on the salary growth rate), which is just as meaningful as adjusting for inflation. It’s also much less complicated than the PV function you already used.

(And there was no need to be rude, I was trying to help).

5. Actually, I appreciated your suggestions. The smiley icon meant I was kidding. I didn’t mean any offense.

6. […] How much money do you need in your retirement? […]

7. NJSteve says:

Actually I agree dermot had a lot of good points. As far as showing the result in today’s dollars, why not compromise and add a second box labeled something like ‘Value in today’s dollars at retirement.’

Also you might want to consider adding a variable box labeled ‘average inflation rate’ that can be adjusted to vary the results and change ‘project annual salary increase’ to ‘project annual salary increase difference from inflation’

Just my 2 cents

Posting code? Use <pre> tags for VBA and <code> tags for inline.