Investment returns are uncertain. How much savings do I really need to retire?

money growthA couple posts back, I looked at how much you could spend each year if you retired with 2 million dollars invested at 4%. (Answer: About $80,000, increasing each year for inflation). But how do you know you can get 4% year-after-year for 30 or 40 years?

You don’t. Any investment has good years and not-so-good years, and in general, the greater the return, the higher the uncertainty. One way of measuring this volatility is the standard deviation. For example, a bond fund paying 4% per year had a standard deviation of 2.5%. That means about 68% of the time your return will be with 2.5 percentage points of the average 4%, or between 1.5% and 6.5%. Here’s a spreadsheet, retirement with volatility, that gives you a sample of what might happen to your investment over the years.

retirement variabilityWhat you see when you open the spreadsheet won’t be the same as this. The reason is that it’s using random draws to represent how well the fund will do that year. We can’t predict it, but we know what might happen, so for each year it randomly picks something within the realm of possibility. The Random number is selected between 0 and 100 percent, and the Growth is calculated based on it. Every time you change something in the spreadsheet you got a completely new list of random numbers and a new outcome. Try entering something, anything, in some unused cell to get another view of how it might turn out. There’s even a tiny chance that the money won’t last the full 25 years:

retirement bad variability

The Excel secret to making all this work, if you’re interested, is the RAND function. This also uses NORMINV to calculate the growth rates. This goes beyond the basic techniques used to build most of the spreadsheets here, but you can investigate these functions yourself.

If you’ve tried a few cases, you’re probably satisfied that this investment and withdrawal plan is sound. But what if the investment were more volatile? Try 8% or 10% standard deviation. Are you comfortable with that?

Simple spreadsheets for complex questions

magic bar graphIsn’t it hard to build a spreadsheet to analyze financial issues? Don’t you need to be a math whiz? Don’t you need to write complicated formulas into all those cells? No, no, and no!

You can do a lot with just addition, subtraction, and multiplication, and Excel will do all the arithmetic for you. The formulas are actually quite simple, and Excel will write them for you. One good formula and you can fill an entire column, or even the whole spreadsheet.

As an example, some recent posts included spreadsheets for thinking about some complex financial questions including:

  • How much money do I need for retirement?
  • Am I saving enough for retirement (or any other goal)?
  • How much faster will I pay off my mortgage if I add $100 a month?

Even though these are complex questions, there are only a few formulas in each of these—the rest of the cells are just copies. The formulas use no more than addition, subtraction, and multiplication. Best of all, you just tell Excel what you want it to do and it writes the formulas for you.

Take a look at the spreadsheet for retirement spending.


There are formulas in the Spending column to predict your spending each year as it rises with inflation. Take a look at one of them: =B8 + B8*Inflation. This is where people get scared off. Come back! Excel formulas are much easier to write than they are to read. You get Excel to write this just by thinking through what you want it to do. First type “=” to tell Excel to write a formula. Then think about how much you’ll spend that second year when you add in inflation, “I’ll spend the same $50,000 I spent last year, plus the inflation amount, which is 4% of the $50,000 I spent last year.” When you think “$50,000 I spent last year,” click on the $50,000 under spending. When you think “plus” type +. Follow this with the inflation amount, 4% (click on it in the inputs) of the (type * for multiplication, since you take percentages by multiplying) $50,000 I spent last year (click on it under Spending). When you enter it, you should get the answer. The same thinking will get you the funds growth, and then you just need to ask Excel to subtract the spending amount in the same row.

Here’s where the magic happens. AutoFill these two formulas down the column to complete the spreadsheet in seconds. That’s all there is to it. All of these spreadsheets are built this same way. Just be sure to name your inputs. To learn more about these Excel techniques, see my posts from September to November, 2012.

Do you have any financial issues you’d like considered? Let me know and I’ll feature them in future posts.

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?

Pay me now or pay me later

solarMy nephew is looking to lease a solar system for his house. He’s narrowed it down to two options that offer equivalent systems. The question is the pricing structure. One has a low starting monthly payment, which increases each year, and the other has a fixed monthly payment. Which to choose?

Here’s a spreadsheet that compares the annual cost of each, year-by-year: pay me now. Option 1, with the lower initial cost, is less expensive for the first 7 years, but after that Option 2 costs less. Clearly, Option 1 is the choice if he stays in the house less than seven years.

pay me now annual

But he plans to live there more than seven years. Will he save enough on Option 2 in the later years to make it worthwhile to give up the early year savings offered by Option 1?

The simplest way to look at this is to calculate the cumulative costs for each year. Option 1 is less up through 2027, but if he stays in the house beyond that, Option 2 costs less. A more realistic way to look at this is called present value, and it’s based on the fact that money now is worth more than money later. For example, with inflation at 3%, a dollar next year is worth only about 97 cents this year (calculated as 1/(1+.03)). We figure the present value of a dollar each year in the future and see that by 2028 a dollar will buy only two-thirds of what it does now, assuming the 3% inflation rate continues. We use those numbers to get the inflation-discounted cost each year, add it all up, and get the cumulative present cost of each option.

If he plans to keep the house and the solar system at least through 2028, he’s better off with Option 2.

pay me now pv

The choice of the interest rate matters. Businesses will generally use larger rates (called discount rates) to calculate present value to account for risk. If you use a 10% rate, it will be a few more years until Option 2 becomes the less expensive choice.

This isn’t the whole story, of course. Any analysis of a lease needs to include what happens when the house is sold. Is the solar system lease transferable to the new owner? If not, what are the costs to pay off the lease? Since these costs may be far in the future, it makes sense to use present value to consider them.

By the way, if you’re in Southern California on July 10, please join me in Torrance for a free workshop, Instant Insights with Microsoft Excel.

What’s with the dollar signs in Excel formulas?

dollar signEver noticed cell identifiers with dollar signs in them? Things like $A$1. Sometimes they’re in formulas. Sometimes you select a cell for a dialog box and it shows you something with dollar signs. What’s that all about?

You might try the formulas =A1, =$A1, =A$1, and =$A$1 and find they all give you the same answer, the contents of cell A1. Where you see the difference is when you copy or AutoFill the formula. Excel is smart enough to adjust your formula to its new location, a very convenient feature. But sometimes you don’t want it to do that. That’s what the $ does. It tells Excel “Don’t change the following letter or number when you copy this formula.” So, for example, $A1 will always keep the A, but the 1 may change when copied somewhere else.

Let’s take an example. Here’s a spreadsheet, dollar signs, that calculates total cost of some items, including sales tax.

dollar signs spreadsheet

Everything in columns C through F starting in row 4 is calculated using a formula. In fact, formulas were entered only in row 4, and the rest were copied down the columns using AutoFill. The formula in D4 is =B4+C4. Add the price and the tax to get the total. No dollar signs, so when it gets copied to the other rows it will change accordingly. In D5, for example, the formula is =B5+C5, which is just what you want. This is what Excel does normally, and usually it gives you exactly what you want and saves you a ton of work.

Now look at C4. The formula is =B4*B1, multiply the price by the tax rate. That works here, but it we try to copy it down the column, we’ll get garbage (try it), since it will try to use B2, B3, and so on, instead of B1. We want to make sure we always use the tax rate that is in B1, so we write it as $B$1, and the formula that works for copying is =B4*$B$1.

Check out the formula in E4, that multiplies the total cost of one item by the quantity in E3. It’s =E$3*$D4. See if you can figure out why this works not only when you copy it down the column, but also when you copy it across to column F.

By the way, the references that change (without the $) are called relative references and the ones that don’t change (with the $) are called absolute references. Knowing when to use them will really help you create some big, useful spreadsheets from just a few formulas.

Marry a Millionaire!

Rich elderly man with gold-digger companion or wifeTiffany has met a millionaire looking for a trophy wife. She could quit her job and live in luxury the rest of her life! Should she marry him?

Here’s a case where Excel can help to visualize the future. How much can they (she) spend each year?

I’ll start building a mathematical model to predict where they’ll be financially each year with what I know and what I assume. The nice thing about Excel is that you can change the assumptions all you want and instantly see the outcome. I’ll start by assuming that they’ll spend $100,000 a year, a modest life style for a rich couple. I know they’ll have assets of $1,000,000, well invested, and that she’s 22 years old. I’ll assume that their spending will increase with inflation, which I’ll take to be 3%. I’ll assume that the assets will grow at a rate to 8% per year. I’ll enter all these as inputs to the model and name them.

millionaire inputs
Now let’s see what happens over the years. Here’s the spreadsheet, called millionaire. It uses simple calculations to predict how they’re doing each year. Click on any cells in the second row if you want to see the calculations; these were AutoFilled to figure the subsequent years.

millionaire results


Under the assumptions I made, they run out of money before she’s 35! Millionaires just aren’t what they used to be. Try changing some of the assumptions and see what happens. You could use Goal Seek to answer the following questions:

  • How much can they spend each year and not run out until she’s 85? (Answer: $48,637)
  • How much money should her next boyfriend have? (Answer: $2,056,208)

Tiffany breaks up with him and goes back to her crummy job.

This is an example of using a simple spreadsheet to peer into the future and get answers to tough questions. You can apply this spreadsheet to other, less frivolous questions, such as, “Can I afford to retire?” Have you used techniques like this? Do you have questions to answer that might be helped by techniques like this?

Were you surprised at the answer? I recall a show a few years back in which women competed for the hand of a “millionaire.” The gimmick was that he was actually just a regular working stiff. A better joke seems to be to check up, a few years later, on someone who married a real millionaire and see how she’s doing.