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. This blog is meant for everyone, but in particular for high net worth individuals and owners of private corporations. My posts are blunt, opinionated and even have a twist of humour/sarcasm. You've been warned. Please note the blog posts are time sensitive and subject to changes in legislation or law.

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.

14 comments:

  1. 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
    Replies
    1. I thought it was very nice of him. Glad it is of use to you

      Delete
  2. Mark, I second J.K. above. Many thx to your reader for sharing, this is very helpful.

    ReplyDelete
  3. 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!

    And in case you're not aware, here's a link to mine from 2 years ago with a similar approach.

    ReplyDelete
    Replies
    1. 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
  4. 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.

    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

    ReplyDelete
    Replies
    1. thx, people now have a multiple of choices to choose from

      Delete
  5. I have published a pretty complete retirment forecast spreadsheet on which I have received positive feedback. Available here: http://pabroon.blogspot.ca/

    ReplyDelete
  6. Its wonderful to find this spreadsheet tool. i started to build one and it was growing ever more complicated. This has it all!

    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?

    ReplyDelete
    Replies
    1. Hi Martha

      It is all based on your marginal tax rate and province, so I cant answer

      Delete
  7. 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
  8. Is there a way of accessing the spreadsheet? I clicked the link and requested access, but haven't heard back. Thanks very much

    ReplyDelete
  9. 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