I often receive insightful and enlightening comments from my readers. I received one such comment in respect of Part 6 of my series on “How Much Money do I Need to Retire? Heck if I Know or Anyone Else Does!".
When you read the comment (reproduced below), you will note the thought and detail provided. In the last paragraph of the comment, the reader discusses a retirement spreadsheet he created and he suggests that “there ought to be one in the public domain”.
I thought to myself, a reader who appeared so detailed and thorough, must have a pretty good spreadsheet, and thus, I asked him if he would be willing to share his work with others in the public domain.
The reader has agreed to share his retirement spreadsheet so others may benefit from it. I am providing a link at the bottom of this post to his spreadsheet. Please keep in mind that I have only played with the spreadsheet and have not rigorously tested it and any use of the spreadsheet is undertaken at your own risk.
The Reader's Retirement Experience
The comment as initially emailed to me and then subsequently posted on my blog is copied in full below; I find it quite insightful:
At Marks' request, I am posting my previous email to him. I would like to share my experience:
Ten years ago I figured that I could retire. I created a spreadsheet that calculated, year after year, investment return, expense adjusted for inflation, income tax, and remaining capital. The assumptions I used were:
- 2% inflation
- 4% investment return net of management and trading fees; the entire return fully taxed
My registered accounts were fairly large, so I was careful to calculate each year the minimum withdrawal and the resulting income tax. In fact, one main reason I created this spreadsheet was so that I could play around with registered account withdrawals before minimum withdrawals kick in, to see which strategy would work best.
I did not factor in OAS or CPP (there is always that sticky OAS clawback, and even the Service Canada website cannot give you an accurate CPP amount in a future year when you actually start drawing on it). And I did not factor in my house. Bloggers note: The reader provides for OAS and CPP in his "other income" column.
The expense number I used to start was simply my previous year’s actual expense. In the model, I just ran with this expense, adjusted for inflation. Starting from age 50, it turned out that my withdrawal rate was 3% (excluding income tax), and the money ran out after 45 years. That was how I concluded I could retire.
(It turned out the best withdrawal strategy from registered accounts is to leave the money there as late as possible. The tax savings and resulting returns from these earlier years seem to offset higher income tax during the requisite withdrawal years.)
Here is my experience so far:
- My actual income tax has been lower than calculated, probably because of dividends and unrealized capital gains in my unregistered accounts.
- My actual return on capital yearly had been: 6.62, 6.82, 3.79, -7.27, 11.31, 8.32, 6.12, 6.20 and 7.78%
- My actual expenses (excluding taxes) had fluctuated from year to year, but over 10 years, the actual total expenses are 4% less than the projected total.
As a result, I am ahead of the model I that ran 10 years ago. Every year I update the spreadsheet with actual numbers, so that I can compare where things differ, and also see how the projections change. (I don’t bother updating the tax brackets or the expense projections.) Currently, the projections show that there will be quite a bit of money left after year 47.
Just for curiosity, I plug in a hypothetical withdrawal rate of 4% at age 65 into the spreadsheet, and let it run. Money will run out at age 95. So a 4% withdrawal rate excluding income tax is probably OK but I would be cautious of it.
On the other hand, I did not include OAS and CPP. Also, my situation is a two-income family.
This spreadsheet has been one of my most important financial management tools in the past 10 years. It is not that hard to create a spreadsheet like this where one can fill in one’s numbers and assumptions and see the projections; there ought to be one in the public domain.
Retirement Planning Spreadsheet
Here is the link to the Retirement Spreadsheet (Note: The screen will look haphazard, just go to the top left corner, click file and then click download). My reader has provided instructions on page one. I thank this reader for his selflessness in providing the spreadsheet to others.
The above spreadsheet is for informational purposes only. Both I and the creator of the spreadsheet make no representations and warranties as to its accuracy, completeness or use and you use the spreadsheet at your own risk.
This spd/sht is just what I've been looking for. I was actually in the process of trying to create my own, but this will save me the effort. Thanks to the reader for sharing. It's much appreciated.ReplyDelete
I thought it was very nice of him. Glad it is of use to youDelete
Mark, I second J.K. above. Many thx to your reader for sharing, this is very helpful.ReplyDelete
Thx, "old guy"Delete
Interesting spreadsheet, looks to go into much more detail on taxes (even provides for two separate people!) and less on the investments and spending. And those collapsible sections -- I've never seen anything like that in Excel before. I'm going to have to figure out what he did there!ReplyDelete
And in case you're not aware, here's a link to mine from 2 years ago with a similar approach.
Thx Potato, I was not aware of your spreadsheet, I am glad people now have a couple spreadsheets to work from. Thanks for the link.Delete
Coincidentally, I recently posted a financial planning spreadsheet that I was hoping to get some feedback on. It's more for general planning & less tax-centric. If anyone takes a look, let me know what they think, thanks.ReplyDelete
It's at the Financial Wisdom board in the "Financial Planning and Building Portfolios" under the post "Lifetime cashflow spreadsheet... comments?" here: financialwisdomforum.org/forum/viewtopic.php?f=29&t=116961#p520259
thx, people now have a multiple of choices to choose fromDelete
I have published a pretty complete retirment forecast spreadsheet on which I have received positive feedback. Available here: http://pabroon.blogspot.ca/ReplyDelete
Its wonderful to find this spreadsheet tool. i started to build one and it was growing ever more complicated. This has it all!ReplyDelete
I have a question; Under Inputs, how to determine the investment income tax burden. My investments are all dividend-bearing (no interest) but of course can appreciate in value. What % tax burden do you suggest in this situation?
It is all based on your marginal tax rate and province, so I cant answer
Thank you so much to you and your reader for making this available! My hubby and I track our monthly financial situation (both still working) and this will be a very welcome addition to our tracking as we are getting close to a decision on retirement.ReplyDelete
Is there a way of accessing the spreadsheet? I clicked the link and requested access, but haven't heard back. Thanks very muchReplyDelete
Rixter, not sure why it is not opening without permission. This is from 2014 and wherever it was stored on the internet, must have changed the access. Sorry, but you will have to google for another free retirement spreadsheet.ReplyDelete