FREE NEWSLETTER
IN SUMMER 2012, MY boss of 17 years announced he planned to retire the following year. I had enjoyed working for him and considered him both a mentor and a friend, and I had some trepidation about working for a new manager at this late stage in my career. While I enjoyed my job and was good at it, and I liked most of the people I worked with, it was a stressful, demanding position, particularly when budget cuts necessitated layoffs. I looked forward to not having to deal with those issues anymore. My wife and I started thinking seriously about when we might retire.
I was age 58 then and I had two more years to work before I could retire without taking a reduced pension from the State of North Carolina. The penalties for retiring early were fairly steep, so I didn’t want to quit work any earlier than age 60.
Medical insurance was also a consideration. Fortunately, North Carolina has good and inexpensive medical benefits for state retirees. My wife was working as a private practice clinical social worker, and she’d been covered by my state medical plan. I found out that I could continue to cover her once I retired, and it would cost even less once she went on Medicare.
I tried various online retirement calculators and those gave promising results, even with very conservative estimates of market performance. But the calculators I tried all assumed a constant level of spending over time and they didn’t factor in taxes. This left me with the sense I wasn’t getting the full picture.
By 2012, most of our money was at Vanguard Group, which would provide some limited free advice if you had a certain amount invested there. Several times, I spoke with a Certified Financial Planner for an hour or so. The Vanguard planner plugged our numbers into his own models, and he assured us that we were in good shape. He also suggested we might shift to a more conservative portfolio, since we had enough for our expected needs and might want to reduce risk—something I’d also been thinking about.
To feel comfortable, I wanted to see the numbers in more detail. Since spreadsheets are my forte, I worked up a large, complicated spreadsheet to calculate how our financial situation would change over time. With the spreadsheet, I could change spending and return assumptions to see how these would affect the outcomes. After reading Rick Ferri’s All About Asset Allocation, I looked up his 30-year market forecast. Using his estimates, I came up with a conservative expected after-inflation annual return of 2.4%, based on our planned retirement allocation of 40% stocks and 60% bonds.
I also read The Bogleheads’ Guide to Retirement Planning, which had a lot of useful suggestions. For example, to estimate the amount we would spend in retirement, I took our current expenses, and then subtracted those that would no longer apply in retirement, while also adding in new costs, such as increased travel, hobbies and other leisure activities.
My spreadsheet allowed me to factor in Roth conversions, large occasional expenditures like a new car or replacing the roof, and onetime sources of cash, such as from the sale of real estate. It also computed state and federal income taxes, as well as required minimum distributions. I lowered our estimated spending needs as our retirement progressed, on the assumption that we’d travel less and that our long-term-care insurance would handle most of the increase in medical costs.
It took months to tweak the spreadsheet just the way I wanted it. But after completing the first iteration, my wife and I were convinced we could retire comfortably in summer 2014. So that’s what we did.
Brian White retired from the University of North Carolina, where he worked as a systems programmer and then director of information technology in the computer science department. His previous articles were Limited Selection, Lesson Well Learned and Rookie Mistakes. Brian likes hiking with his wife in a nearby forest, dancing to rocking blues music, camping with friends and stamp collecting. He also enjoys doing Volunteer Income Tax Assistance (VITA) work in the Chapel Hill senior center.
Want to receive our weekly newsletter? Sign up now. How about our daily alert about the site's latest posts? Join the list.
Any chance we could get a copy of your spreadsheet template? Thanks.
I don’t actually have a template. I just have one big, complex spreadsheet that is very specific to my own circumstances. It is not something that will make much sense without including my actual numbers, which I am not comfortable doing. Sorry about that.
I agree with Gregg. I have a similar spreadsheet and would like to see your template. Also, you mentioned a conservative 2.4% annualized return but what inflation number did you use?
My assumption was that the annualized return was 2.4% after inflation. I did not factor in inflation in any other way, so the whole spreadsheet is in today’s dollars.
Retiring at age 60 is a luxury, especially with a pension. Because you worked for a state, do you not have Social Security? Just curious, what was the percentage of your income goal were you trying to fill with investments after considering your pension?
All teachers in NC and 29 other states have state pensions and are also covered by Social Security.
I have a state pension from NC and my wife and I will both have Social Security. In 5-10 years we plan to moveinto a Continuing Care Retirement Community (CCRC), and the pension and SS will cover about 70% of the income we need to cover all our expenses. I knew that over time our tax situation would change and our spending would change, and I wanted to be sure we could really afford the CCRC and all the traveling we want to do.
The spreadsheet allows me to plan amounts of Roth conversions and withdrawals from Roth IRAs, traditional IRAs, my NC 457, taxable accounts, and cash accounts, to minimize the taxes I have to pay. I think I have mentioned in previous articles that I am a tax geek, and I want to pay every penny I owe and no more. One place where the spreadsheet helps is in computing the actual cost of the CCRC, since a sizable chunk of the initial investment and the monthly fees can be counted toward medical expenses, enough to make it worthwhile to itemize, even without property taxes.
I have to change the spreadsheet every year to account for changes in tax law, and I update our beginning of year account balances to show what we actually have. I admit that I enjoy doing this as a hobby. I don’t know how much money this will save us over the years, but it makes me feel more comfortable that we’ll have enough.
There is one thing that fascinates me about detailed planning like this, why?
I’m not being sarcastic, but if a person is making $x and they know pension income will be $Y and Social Security will be $Z they know that’s what they will live on and must cover taxes, large emergency bills, etc. just like while working. Perhaps saving and payroll taxes will disappear giving an extra income boost.
So, isn’t the unknown just the gap between working income, pension and SS? and the question being can investments get me the extra X% of gross income to reach 100% of working income. For that difference wouldn’t the simple 4% guidance provide the answer and there would be two sources of COLAs.
Of course the picture is much different if investments must generate all or most income.
Brian, I could certainly utilize a spreadsheet like you mentioned in this article! Like Gregg and Kevin requested, any chance you would provide your template? Thanks for your consideration, Brian.