Investment returns are uncertain. How much savings do I really need to retire?

money growthA couple posts back, I looked at how much you could spend each year if you retired with 2 million dollars invested at 4%. (Answer: About $80,000, increasing each year for inflation). But how do you know you can get 4% year-after-year for 30 or 40 years?

You don’t. Any investment has good years and not-so-good years, and in general, the greater the return, the higher the uncertainty. One way of measuring this volatility is the standard deviation. For example, a bond fund paying 4% per year had a standard deviation of 2.5%. That means about 68% of the time your return will be with 2.5 percentage points of the average 4%, or between 1.5% and 6.5%. Here’s a spreadsheet, retirement with volatility, that gives you a sample of what might happen to your investment over the years.

retirement variabilityWhat you see when you open the spreadsheet won’t be the same as this. The reason is that it’s using random draws to represent how well the fund will do that year. We can’t predict it, but we know what might happen, so for each year it randomly picks something within the realm of possibility. The Random number is selected between 0 and 100 percent, and the Growth is calculated based on it. Every time you change something in the spreadsheet you got a completely new list of random numbers and a new outcome. Try entering something, anything, in some unused cell to get another view of how it might turn out. There’s even a tiny chance that the money won’t last the full 25 years:

retirement bad variability

The Excel secret to making all this work, if you’re interested, is the RAND function. This also uses NORMINV to calculate the growth rates. This goes beyond the basic techniques used to build most of the spreadsheets here, but you can investigate these functions yourself.

If you’ve tried a few cases, you’re probably satisfied that this investment and withdrawal plan is sound. But what if the investment were more volatile? Try 8% or 10% standard deviation. Are you comfortable with that?

Simple spreadsheets for complex questions

magic bar graphIsn’t it hard to build a spreadsheet to analyze financial issues? Don’t you need to be a math whiz? Don’t you need to write complicated formulas into all those cells? No, no, and no!

You can do a lot with just addition, subtraction, and multiplication, and Excel will do all the arithmetic for you. The formulas are actually quite simple, and Excel will write them for you. One good formula and you can fill an entire column, or even the whole spreadsheet.

As an example, some recent posts included spreadsheets for thinking about some complex financial questions including:

  • How much money do I need for retirement?
  • Am I saving enough for retirement (or any other goal)?
  • How much faster will I pay off my mortgage if I add $100 a month?

Even though these are complex questions, there are only a few formulas in each of these—the rest of the cells are just copies. The formulas use no more than addition, subtraction, and multiplication. Best of all, you just tell Excel what you want it to do and it writes the formulas for you.

Take a look at the spreadsheet for retirement spending.


There are formulas in the Spending column to predict your spending each year as it rises with inflation. Take a look at one of them: =B8 + B8*Inflation. This is where people get scared off. Come back! Excel formulas are much easier to write than they are to read. You get Excel to write this just by thinking through what you want it to do. First type “=” to tell Excel to write a formula. Then think about how much you’ll spend that second year when you add in inflation, “I’ll spend the same $50,000 I spent last year, plus the inflation amount, which is 4% of the $50,000 I spent last year.” When you think “$50,000 I spent last year,” click on the $50,000 under spending. When you think “plus” type +. Follow this with the inflation amount, 4% (click on it in the inputs) of the (type * for multiplication, since you take percentages by multiplying) $50,000 I spent last year (click on it under Spending). When you enter it, you should get the answer. The same thinking will get you the funds growth, and then you just need to ask Excel to subtract the spending amount in the same row.

Here’s where the magic happens. AutoFill these two formulas down the column to complete the spreadsheet in seconds. That’s all there is to it. All of these spreadsheets are built this same way. Just be sure to name your inputs. To learn more about these Excel techniques, see my posts from September to November, 2012.

Do you have any financial issues you’d like considered? Let me know and I’ll feature them in future posts.