Message Font: Serif | Sans-Serif
No. of Recommendations: 23
As I mentioned, I am very green in this area and would absolutely love it if you were to walk through an example with specific figures. I agree that it will help others, myself included, see how to calculate the numbers as specifically as possible. Thanks for the offer to do this for those of us that are still learning!

Okay… I'll go ahead and walk through a set of calculations from start to finish in order to demonstrate how I use inflation adjusted rates of return in order to determine how much I need to have in my portfolio on the day I retire, and how much I need to squirrel away now to get me there. Please note, I'm trying to demonstrate how using inflation adjusted rates work, so will keep the calculations and assumptions simple. Your own situation will vary for a number of reasons, and I'll try to point out along the way some of the other things you might want to consider, but I'm certainly not going to be able to address all of them in just one post.

The place I usually like to start with is, “How much do I need when the day to retire comes?” Again… we can make a lot of assumptions about Social Security, whether one will work during retirement, whether one's retirement funds will be tax free or not (are they in a traditional IRA? A Roth IRA? Fully taxable account?) etc. For the sake of simplicity let's assume that we desire to retire at age 65, expect to live until age 95, and want to leave $100,000 to our heirs – and that we want an income of $50k per year while in retirement… and that's $50k in today's dollars – we'll simply ignore all of these other issues for now. Other assumptions we need to make are – how much interest will my retirement portfolio earn? What will the rate of inflation be? We'll change our old assumptions a little and assume that the retirement portfolio will earn 8% and inflation will be at 3%.

Okay… the first calculation method we went over was to treat retirement income as a perpetuity, in other words, how much money do I need if I want to get $50k in income, forever, without ever having to touch the principal – while accounting for inflation? Please note: all of the 'Need' calculations that follow are in today's dollars, just as the $50k income we want is in today's dollars. That, after all, is the beauty of using inflation adjusted rates in every single calculation we make - it automatically converts everything into today's dollars.

That one was as follows:

'Need' = Desired income / (nominal interest rate – inflation rate)

'Need' = $50k / (8% - 3%)

'Need' = $1 million.

Alternately, we can use the 'accepted' SWR rate at 4%, and that calculation would look like this:

'Need' = $50k / 4%

'Need' = $1.25 million

The first calculation doesn't exactly suit our needs because it would leave $1 million to our heirs, and not the $100,000 we wanted to leave – while our heirs might not complain, we'd have to save more money to get there – and we really want to take that trip to Bermuda next fall, so we'd rather not save more than we half to so long as our main goals are accomplished. The second calculation just uses an 'accepted' figure – we really don't know whether this is appropriate for our situation or not.

Unfortunately, the calculation we really need to make is an awful lot more complex. I'll try to provide an example as best I can, and if I lose you, feel free to reply with questions.

Okay… some basic ground rules. I know I'll be in retirement for 30 years (retire at 65 and live to 95) and want to leave $100k behind. I know I want to withdraw $50k per year for each of those 30 years in today's dollars, so how do I get there? I personally like using Excel, so I'll use it here. If you don't have Excel or aren't familiar with it, there are online calculators available that will make the calculation for you – but I'd suggest using the inflation adjusted rate as outlined before.

So… we'll make my 7th grade algebra teacher proud and call that unknown, lump sum retirement 'need' X. I know that my nominal interest rate is 8%, and inflation is 3%. I also know that I'm going to withdraw $50K. So what does year one look like?

Balance at beginning of year 1 = X

Balance at end of year 1 (beginning of year 2) = (X - $50k) x (1 + (8% - 3%))

Note: the above calculation assumes that the $50k is withdrawn at the beginning of the year and that interest is compounded annually at the end of the year. Changing these assumptions will change the answer, but not significantly, so I think we're reasonably safe in making them.

And we keep making this calculation over and over again…. Year 2 looks like:

Balance at end of year 2 = (Balance at end of year 1 (from above) - $50k) x (1 + (8% - 3%))

And so on and so on and so on… finally we get to year 30… now calculating this by hand is near impossible, because we'd have 30 of these calculations all strung together on one side of the = sign, and $100k on the other (the amount we want left at the end of year 30, beginning of year 31) – even my 7th grade algebra teacher would have trouble solving for X! So I use Excel.

For those using Excel, and who want to follow along at home, I put the 'Need' in cell A1. I started with $1 million just as a placeholder. Using the above formula, I put the following Excel formula in cell A2:


To the right of cell A1 (in cell B1) I just wrote the word 'Need', and to the right of cell A2 (in cell B2) I wrote the words 'End of Year 1'. My little spreadsheet, so far, looks like this:

$ 1,000,000 'Need'
$ 997,500 End of year 1

Now all I have to do is copy and paste the formula in B2 all the way down, and copy and paste End of year 1 all the way down (making it end of year 2 and 3, etc) until I get to end of year 30. My spreadsheet now looks like this:

$ 1,000,000 'Need'
$ 997,500 End of year 1
$ 994,875 End of year 2
$ 992,119 End of year 3
$ 989,225 End of year 4
$ 986,186 End of year 5
$ 982,995 End of year 6
$ 979,645 End of year 7
$ 976,127 End of year 8
$ 972,434 End of year 9
$ 968,555 End of year 10
$ 964,483 End of year 11
$ 960,207 End of year 12
$ 955,718 End of year 13
$ 951,003 End of year 14
$ 946,054 End of year 15
$ 940,856 End of year 16
$ 935,399 End of year 17
$ 929,669 End of year 18
$ 923,652 End of year 19
$ 917,335 End of year 20
$ 910,702 End of year 21
$ 903,737 End of year 22
$ 896,424 End of year 23
$ 888,745 End of year 24
$ 880,682 End of year 25
$ 872,216 End of year 26
$ 863,327 End of year 27
$ 853,994 End of year 28
$ 844,193 End of year 29
$ 833,903 End of year 30

Wow! I'm leaving $834k to my heirs… too much.

Side note: Some Fools are probably saying "Hey! the first calculation you made with these assumptions said we'd leave $1 million to our heirs, and now we're only leaving $834k what gives?" The difference here is simply one of timing. The first, simple calculation assumed that the $50k, under this scenario, was withdrawn at the end of each year and not the beginning. If we change the longer calculation's formula to reflect that assumption, we'd have $1 million at the end, just like the first example. See what I meant about certain calculation methods making assumptions for you that you didn't even realize you were making? Okay... side note over... onward!

Now it depends on what version of Excel you're using, but under the 'Tools' menu you'll probably find a function called 'goal seek' or 'solver'. This function will do the algebra for you. It may ask you for a target cell, a value, and what cell you want to change. In this case our 'target' cell is cell A31 – this is the cell we want to equal $100k, not $834k. The value is our $100k, and the cell we want to change is A1, or our 'Need'. So basically we're telling Excel “Tell me what 'Need' needs to be so that 'End of year 30' = $100k. When I do that, my spreadsheet looks like this:

$ 830,191 'Need'
$ 819,201 End of year 1
$ 807,661 End of year 2
$ 795,544 End of year 3
$ 782,821 End of year 4
$ 769,462 End of year 5
$ 755,435 End of year 6
$ 740,707 End of year 7
$ 725,243 End of year 8
$ 709,005 End of year 9
$ 691,955 End of year 10
$ 674,053 End of year 11
$ 655,255 End of year 12
$ 635,518 End of year 13
$ 614,794 End of year 14
$ 593,034 End of year 15
$ 570,185 End of year 16
$ 546,195 End of year 17
$ 521,004 End of year 18
$ 494,555 End of year 19
$ 466,782 End of year 20
$ 437,622 End of year 21
$ 407,003 End of year 22
$ 374,853 End of year 23
$ 341,095 End of year 24
$ 305,650 End of year 25
$ 268,433 End of year 26
$ 229,354 End of year 27
$ 188,322 End of year 28
$ 145,238 End of year 29
$ 100,000 End of year 30

Voila! Our need isn't the $1 million that the first calculation gave us, nor the $1.25 million our second calculation, but only $830,000. We might be able to afford that trip to Bermuda after all! And again, if you're using one of those online retirement calculators, just use the same assumptions - you'll live for 30 years in retirement, withdraw $50k per year, leave $100k to your heirs, and the interest rate your funds will earn in retirement is 5% (remember: we're using the inflation adjusted rate which is the nominal interest rate of 8% minus the inflation rate of 3%) and you should get a very similar result. Again, it might not be exactly the same depending on how the calculator works (assumes annual compounding of interest or monthly, for example), but it should be pretty darn close.

Another side note: Here's where the point of using the inflation adjusted rate instead of the nominal rate makes a big difference. If I make the exact same calculation as above, but use the nominal rate of 8% instead of the inflation adjusted rate of 5%, my 'Need' changes to $618k... a bit short of where we really wanted to be. Yes, you'll still get to withdraw $50k per year for 30 years and leave $100k, but that'll be $50k per year in those dollars, not inflation adjusted dollars, so while you'll still get the same $50k in year 30 as in year one, that $50k won't buy nearly as much as it used to.

Okay… rather than immediately answering the question, “How much do I need to save to get $830,000 in today's dollars in my account come retirement day” I'm going to take a little break. Two reasons… this post is pretty long as it is… and it'll give you a chance to digest and ask questions before we move on.

Feel free to reply with any questions/comments – or just to say, “Got it! Go ahead when ready!” or maybe even say, “You're a crackpot, don't bother going on 'cause I'm not gonna listen anyway.” ;-)

Okay… temporarily signing off…



Print the post  


When Life Gives You Lemons
We all have had hardships and made poor decisions. The important thing is how we respond and grow. Read the story of a Fool who started from nothing, and looks to gain everything.
Contact Us
Contact Customer Service and other Fool departments here.
Work for Fools?
Winner of the Washingtonian great places to work, and Glassdoor #1 Company to Work For 2015! Have access to all of TMF's online and email products for FREE, and be paid for your contributions to TMF! Click the link and start your Fool career.