How much money do I need to retire?

Planning budgetAre you saving enough to retire? That depends on how much you need to have when you enter retirement. Last time I showed you a spreadsheet to help one of my clients amass $2,000,000 by the time he retires. But will that be enough? We can build a simple spreadsheet that gives you a start on answering this. Let’s start by figuring that he’ll need about $50,000 a year. The rule of thumb is you’ll need 70% of what you earned while working, but I’ve talked to many retirees who say that’s not nearly enough. It depends on your retirement plans. Will you downsize or will you spend your new-found time in travel or other costly pursuits? The idea of this spreadsheet is to help you visualize the relationship between how much you spend and how long the money lasts.

He’ll want to invest cautiously to avoid losing his nice nest egg. (More about that in a later post.) Let’s say he averages 4% growth a year. He’ll also find that his spending will go up each year due to inflation. Figure 3% for that. Let’s say he retires at age 65. Of course, you can change any of these assumptions and see what happens.

Here’s the spreadsheet, retirement spending

retirement spending xlsAs you can see, this is a good spending plan. He’s not expected to run out of money until he’s 115.

Maybe he can spend more. If he spends $100,000 per year (try it), he’ll run out at age 87. It helps to play what-if with this until you get something that feels comfortable. A common rule-of-thumb is to spend 4% of your nest egg the first year. In this case, that will be $80,000 a year and it will cover him to age 92.

You’re your own financial advisor, even if you’re working with someone else. Only you can decide where you want to be in the future and how much risk you’re willing to take. A spreadsheet like this doesn’t give you a single answer; it helps you visualize your options. It often raises more questions than it answers, and leads you to the key issues. Will I need to work longer? How long might I live? Do I need to increase my retirement savings?

Am I saving enough for retirement?

save for retirement

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 for retirement xl

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?