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?

How much faster will I pay off my mortgage if I add $100 a month?

house moneyCan you believe that you can answer this question using nothing but simple arithmetic in Excel? Addition, subtraction, multiplication. That’s all. No functions, and Excel writes the formulas for you. Yes, I know, you think you have to do some complicated formulas that nobody knows. Seriously, here is all you need to know:

  • The interest payment is the interest rate multiplied by the amount owing.
  • Whatever is left over from your payment after the interest is paid goes to pay off the amount you owe.
  • The monthly interest rate is 1/12 the yearly interest rate.

Let’s say you just got a 30-year home loan for $300,000 at 4% with a payment of $1432 a month. Here’s the house payment spreadsheet to answer that question.house payment

We start with the assumptions at the top. Create names for them so they play nice. I made them blue to remind you that you can change them.

Now we’ll look at what happens month-by-month. This is much more enlightening than just getting an answer from some formula. Each month you’ll see how much you’ll owe, how much interest you’ll pay that month, and how much you’ll put toward paying down the loan that month. Once you get the first two rows, you can AutoFill the whole thing down for the full 360 months of the loan.

The amount owing is the full amount the first month. After that, you’ll owe whatever you owed the previous month minus the amount you paid down the previous month. Get Excel to write the formula for you by typing “=” and then clicking on the numbers you want to use with a “-“ in between. To get the amount of interest paid that month, multiply the amount owing that month by the monthly interest rate, which is the interest rate divided by 12. The paydown is just your monthly payment plus the additional payment minus the interest paid. That’s all there is to it.

You see that in March of 2041, the amount owing becomes red. That means it’s negative; you’ve paid it all off and then some, 3 ½ years early. It turns out that you save over $61,000 by doing this, at a cost (at $100 a month) of about $31,000, a net savings of $30,000. Try $200 a month or more and see what that does.

Bonus: Set the additional payment to zero and the spreadsheet turns into a nice payment table for your loan. See, it really does go to 0 in September 2044. In fact, if you want to figure out the monthly payment on a loan, you can use Goal Seek to find the monthly payment value that makes the last amount owing equal to 0. And you thought you needed some complicated formula to do that!

 

What nine things should everyone know how to do with a spreadsheet?

Businessman holding a laptopMacworld recently ran an article, “Nine things everyone should know how to do with a spreadsheet,” by Rob Griffiths. Here’s the list:

  1. Format numbers
  2. Merge cells
  3. Use functions
  4. Distinguish between relative and absolute references
  5. Name cell references
  6. Extract data from ranges
  7. Perform logical tests
  8. Mix text and formula results
  9. Use conditional formatting

I thought about my own top nine before I read the article, or even looked at the list. My focus is on visualizing your financial future and exploring your options. Even so, I was surprised how different my list was. I’ve used everything on Griffith’s list and certainly found it all valuable, but here’s my list for those wishing to explore their financial future.

 

  1. Play what-if

This is barely a skill. Griffiths is assuming that you know how to do this. It’s just entering a number in a cell. The key is to see what that does to the answer. This hardly seems worth mentioning, except that so many people seem to think that all you do with Excel is to dump a bunch of numbers into it and produce a static chart or slide. Explore! See what drives the outcome. See how to get where you want to be.

  1. Write formulas

This is also implicit in Griffith’s list. Even if the word “formula” brings up scary images of high school algebra, everyone should be able to get Excel to write a formula for them by typing “=” and clicking on the cells you want to use in the calculation. It’s amazing what you can do without ever entering a cell reference (if you name your inputs). Learn how to build more sophisticated formulas and you’ll be unstoppable.

  1. Name cells

Griffiths has this one on his list as a means to keep formulas easier to read. That’s nice, but it’s on my list for a very different reason. If you name your inputs they automatically become absolute references and you don’t have to bother with all the dollar signs. This makes writing formulas to AutoFill a cinch. Also, I’m lazy, so I prefer to use Create Names, which uses the input labels. By the way, Griffiths looked at how to do everything on his list in Apple’s Numbers as well as Google’s Sheets. Numbers doesn’t support naming cells. Too bad. I’m a Mac user, but that’s a show-stopper for me.

  1. AutoFill formulas

The best part of writing formulas is being able to replicate them to fill an entire spreadsheet. Construct simple formulas that build on each other, AutoFill them down the page and get complex results. The secret to doing this is to name your inputs to make them play nice and then click on cells in the same of previous row to create a formula. Grab the corner and pull it down to fill in everything else. This is the technique that makes students’ jaws drop in my classes. Some big Excel reference books don’t even mention it.

  1. Use Goal Seek

Work the problem backwards. Your projected savings won’t get you to your goal? Tell Goal Seek to find the amount you need to save to get there.

  1. Use functions

This definitely belongs on any list, and it’s in both of ours. Whatever you want to do—statistics, finance, text handling, and more—Excel has a function for you. Griffiths mentions IF, HLOOKUP, and VLOOKUP. The IF function is especially powerful. It lets you make what happens on the spreadsheet respond to whatever is happening anywhere else on the spreadsheet. If you’re doing loan payments, check out PMT, IPMT, and PPMT.

  1. Create charts

You don’t need to know all the kooky chart options, but sometimes a simple chart helps you see what’s going on, year-by-year.

  1. Sort and filter

In most of the problems I deal with, you don’t have a lot of data (eg. How can I save enough for an $80,000 college education in 18 years?). But sometimes you’re keeping track of a lot of information and you need to make sense of it. Make a table of it, and then use the tabs at the top of each column to reorder or focus it all, to figure out what you have and what matters.

  1. Build data tables

This is a somewhat advanced, somewhat confusing technique, but it’s great for sensitivity analysis. What really matters to the answer? You can also use it to turn any spreadsheet into a Monte Carlo model for risk assessment.

 

 

This is clearly a very subjective list. What are your nine favorite Excel techniques?