What if you could visualize your financial future for the coming year?

Hiker woman looking forward in the snowy mountainHappy new year! This is a time for looking back and for looking ahead. How did you do financially this past year? Was it what you expected? What do you expect in the coming year, and the years ahead? If you could magically produce a spreadsheet that could help you look ahead, what would you ask it? Here are some of the types of questions you might want answered:

  • How can I get out of debt?
  • How much do I need to save to reach my goal?
  • Can I pay off my house faster?
  • Should I refinance my house?
  • How much money do I need to retire?
  • How much can I spend each year in retirement?

You can probably think of others. The good news is that you can easily build spreadsheets to explore questions like these. Of course, there are no certainties in financial planning. That’s why you may want to play “what if” and maybe do some Monte Carlo analysis. Best wishes for a new year filled with fun and no unpleasant surprises.

Volatility is not your friend

Businessman and declining shareWhen you can visualize your financial future using one of the spreadsheets I’ve included recently, it’s fun to put in some really high rate of return and watch your future self get rich. Before the great recession a few years ago, there were a lot of people touting 18% returns, and a lot more people running the numbers and thinking they were going to be gazillionaires. We all know how that turned out. Yes, if you could guarantee 18% year-after-year, go for it. But, if fact, you may get less, a lot less, maybe more, or the whole thing could go bust. That’s volatility (on top of the risk of losing your original investment). If you want to gamble, go to Vegas. But when you’re talking about your financial future, you want some certainty.

Volatility, the uncertainty of returns, hits you even in some relatively safe investments. Take the example of the man retiring with $2,000,000 invested at 4%. A few posts ago I built a simple retirement spending spreadsheet that showed he could spend $80,000 a year and not run out for 27 years, when he’s 92.Retirement til 92

But now if you throw in some modest volatility and do a Monte Carlo analysis of it with this spreadsheet, the certainty goes away. With a standard deviation (a common measure of volatility) of only 2.5%, there are no guarantees. retirement basic mc graphThis money will last those 27 years only with a probability somewhere between 75 and 80%. He’d need to cut his spending down to $70,000 a year to get near-certainty that it would last.

Try higher volatility (standard deviation) numbers to see how much harder it is to get near-certainty in your financial future.

Welcome to Monte Carlo. Is retirement a gamble?

retirement gambleWell, 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.

retirement basic mc

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.

retirement basic mc graph

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?