My name is Mark Goodfield. Welcome to The Blunt Bean Counter ™, a blog that shares my thoughts on income taxes, finance and the psychology of money. I am a Chartered Professional Accountant and a partner with a National Accounting Firm in Toronto. This blog is meant for everyone, but in particular for high net worth individuals and owners of private corporations. The views and opinions expressed in this blog are written solely in my personal capacity and cannot be attributed to the accounting firm with which I am affiliated. My posts are blunt, opinionated and even have a twist of humor/sarcasm. You've been warned.

Wednesday, April 2, 2014

Retirement Planning Spreadsheet

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
- income tax rates remained the same, but brackets adjusted for inflation

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.