Spreadsheet Wizards: 6 Retirement Assumptions You’re Missing

Attention engineers, accounting majors, IT professionals, and others who love all things Excel: I’ve seen some fantastic homemade retirement spreadsheet wizardry from you lately. But, I have yet to see one that isn’t missing some critical pieces. I need to let you know…relying on your spreadsheet to plan the last 30 – 40 years of your life could be dangerous. 

Consider these 6 common assumptions: 

1) No inflation. This is critical. Anyone with gifted math skills? You know the power of exponents and compounding. If you are trying to predict what lunch, or a doctor visit, will cost you over the next 30 – 35 years, even .5% inflation will have a huge impact. Some clients try to account for inflation by using a larger expense number than they need. This doesn’t work for three reasons – 

a) If you overestimate your needs, what happens to the excess in the early years? (Trying to account for this will put you in a loop, because you have to assume more income on the excess. Keep it simple and be accurate about your expenses to begin with.)

b) It can cause you to decide to work longer, when you might not have to.

c) The “cushion” you would need in the beginning to make up for inflation would be so large it would look unreal. Try running an inflation scenario using a more realistic initial expense and see how small your initial “cushion” really is compared to what you would need. 

2) Flat withdrawal rates. The “safe withdrawal rate.” It’s an easy concept to understand and to model, but this thinking is so 1990’s. Since the concept was first introduced (see Bill Bengen, 1994), research has shown a) low interest rates and a bad sequence of returns have blown previous assumptions (namely, 4%); and b) dynamic/variable withdrawal rates have higher success. Today’s computers can vary withdrawal rates.  

3) Forgetting Social Security. If you are older than 50, your Social Security is not going anywhere. Yes, you can count on it. Yes, you can put it in the spreadsheet. Try waiting until 70 if you don’t have a health issue that limits your life expectancy to younger than 80. Remember Social Security has a cost-of-living (i.e., inflation) adjustment. Look up your benefit at age 70 compared to full retirement age (66 or 67). Take both, ride them out for 15 years and see how much better your income looks at 85 by waiting until 70. Math question: What is the present value of a lifetime stream of payments of $2000/month which increase 2% a year beginning at age 70?That’s the value of Social Security. Fill in your own data, take that answer, and add it to your net worth today. Write me if you don’t feel better in the morning. 

4) Flat returns. Every spreadsheet I have seen uses a flat average return assumption, every year, for 30-35 years. The problem with this, as anyone who retired (or tried to) in 2000 or 2007 can tell you, is the sequence of returns. Once you are living off the portfolio, average return is irrelevant. Actual returns, and their sequence, matter. Which brings us to the question, “How do you model sequence of returns and what do you do with that information?” Answer: Monte Carlo simulation. You look at your probability of failure, and keep tweaking until you get it to a reasonable number (which will never be zero). That’s all I can put in a blog post on it. 

5) Forgetting long-term care costs. There are conflicting stats, but if a married couple both make it to their 65th birthdays, there’s about a 25% chance that one of them will make it to their 100th. It is likely many Baby Boomers will need several months, or years, of help with Activities of Daily Living (ADL’s). Either a long term care insurance policy, or a six-figure chunk of money, needs to be in the plan if you want your last years lived in comfort. 

6) Asking the wrong question. Most Baby Boomers who have been good savers approach a retirement plan with, “Here is this pile of money – my life’s work. How much income can I get from it?” To me, this is disempowering, letting the portfolio determine your life. Why not assert yourself? “Here’s what it will cost for my have-to-have’s and my nice-to-have’s. I am willing, to a point, to be flexible.  How can I minimize the chance of running out of money? What sacrifices, or risks, if any, might I have to make and take to improve that chance?” Then you decide if you are willing to make those tradeoffs, or not. What if it turned out that replacing your car every 7 years instead of every 5 meant you could retire at 62 instead of 66? Can your current spreadsheet tell you that? 

Other problems I see are with health care assumptions, emergency funds, taxes, and asset allocations. Addressing these issues requires the full power of Excel – a spreadsheet wizard’s magic wand. Many financial professionals gave up on Excel a long time ago, and outsourced to professional IT wizards who provide accurate, intuitive software solutions.  

Either way, the computing power is here. It’s up to you how, or if, you want to harness it.


Holly Donaldson

Holly Donaldson, CFP® runs an hourly and fee-for-service financial planning practice virtually from her Tampa Bay, Florida office. She also works with clients throughout the U.S. (except Texas) interested in retirement and tax planning advice without product sales or investment management. Holly is the author of The Mindful Money Mentality: How to Find Balance in Your Financial Future (Porchview Publishing, 2013) and publisher of the award-winning monthly e-letter, "The View From the Porch."

Leave a Reply