A 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.

What 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:

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?