Well, yes. Spending during retirement, or saving for retirement (or anything else, for that matter) is fraught with uncertainty. In my last post I featured a spreadsheet that randomly shows you possible year-by-year outcomes of a spending plan. If you invest $2 million at 4% with a standard deviation of 2.5%, it seems you can spend $80,000 a year, adjusted for inflation, over the next 25 years. But how certain is that? And what if you live for 30 years? You can get the answer using a technique called Monte Carlo analysis. This is a sophisticated technique that financial analysts use to assess risk. It involves running a randomized financial model hundreds or thousands of times and collecting statistics on the outcomes. Surprise! You can do it in Excel using data tables.

I put together a Monte Carlo spreadsheet looking at this spending plan over 40 years. This goes through the whole 40-year period with random investment returns, records the number of years the money would last in that situation and how much is left (or owing if it’s red) at the end of 30 years. It then repeats the whole thing 300 times.

If you look at the spreadsheet you’ll see different numbers from this, because it’s, well, random. In any case, when you look at all 300 outcomes, you’ll see that the money may last for 31 years, or it might run out in only 24. You’ll also see that you’d probably be in the hole after 30 years, possibly over a million dollars. In fact the probability of the money lasting 30 years is only about 30%. Here’s a graph that I did from the results, showing the probability of the money lasting some number of years.

The probability (vertical axis) of the money lasting for at least 20 years (horizontal axis) is 100% or pretty close to it, it’s more than 90% certain to last 25 years, but beyond that, things get iffy. Beyond 30 years, the probabilities get really low, and being able to hold on for 40 years is next to impossible. Will this spending plan work for you? It depends. How many years do you expect to live past retirement? (It could be 30 or more.) How comfortable are you with risk?

A few comments on Monte Carlo models. If you’d like to make your own Monte Carlo models, see my earlier post to learn how to use data tables. The trick then is to use an empty cell for the input. There’s more detail in Chapter 14 and Case Study 19 in Simple Spreadsheets for Hard Decisions. By the way, the 300 iterations here are not enough for statistical significance. You may have seen that you didn’t get the same probability each time for the money lasting 30 years. No matter, the idea is to give you a feel for what could happen as a result of your spending plan. Whether the probability is 25% or 35%, it’s going to make you nervous.

The next post I’ll play “what-if” with this model. You may want to try it yourself. If you decrease spending can you make it 30 years? (Unfortunately, Goal Seek doesn’t work for Monte Carlo models.) What if you try a more aggressive investment?