This is a question I get a lot. A man in one of my classes asked this recently. He was putting $2000 a month into a 401(k) and planned to work another 30 years. Would you believe that you can answer this question in Excel using only addition, subtraction and multiplication?
We can build a spreadsheet that visualizes the savings, month-by-month for any monthly amount and then find the amount that works. For now, let’s just make a guess at how much he’ll need when he retires and check whether he’ll get there. We can use the same techniques to explore how much money he’ll need when he finally retires, but that’s the subject for another blog. Let’s start with a goal of $2,000,000 when he retires. Will he get there?
We could look at this month-by-month, but it’s easier to see what’s going on if we look at it year-by-year (though this will slightly underestimate how much he will save). So let’s say he saves $24,000 a year, invested at 6%.
Here’s the spreadsheet, called save for retirement. Sure enough, the formulas in any row involve only addition, subtraction and multiplication. Each row shows how much he expects to have saved each year (the “Amount” column) and how far he is from his goal (“Shortfall”). You can build a spreadsheet like this by getting Excel to write the formulas for you for one row and then AutoFilling the rest. You can do it in just minutes.
Looking at the results, we see he’ll be pretty close in 30 years, and will make it if he waits one more year. Of course, the best thing about having a spreadsheet like this is playing “what if” with the inputs. They’re highlighted in blue, just to remind you that you can change them all you want and see what happens. What if he gets only 5% return? What if he needs $3,000,000? (Use GoalSeek to figure out how to get there.) What if he saves more each year? What if he can’t work the full 30 years? What if he increases the amount socked away each year to keep up with inflation? (This last one requires minor modifications to formulas and inputs. You might want to try it as an exercise.)
Of course, nobody gets 6% without some risk. Anything safe right now is paying less than 1%. What about the risk of fluctuations in the investment returns? That calls for a Monte Carlo analysis, and, yes, you can do that in Excel, but that’s a topic for another day.
You can use this spreadsheet to think about saving for college for a child or grandchild or saving for a down payment on a house. How will you use it? What other considerations would you add?