Skip to main content

Forget about how much money you need for retirement and let's focus instead on how much you actually have.

Some key questions you need to answer include: How long will your money last? How many extra years might you stretch your income if you lived more frugally? What if you invested more conservatively so as to lessen the risk of a stock market crash? What impact does inflation have on all of this? What if you live to 100?

For answers, let's use a retirement cash flow calculator produced for this column by Dr. Ben Barkow, a behavioural psychologist who studies industrial design. Dr. Barkow, president of the Behavioural Team Inc., is a computer whiz who one day got the idea of applying the cash flow tracking he did for his business to retirement. "You can't run a business without a cash flow analysis," Dr. Barkow said. "Likewise, your retirement."

Story continues below advertisement

Dr. Barkow's retirement cash flow spreadsheet is available for you to try on the Globe website. Download the excel file here. What follows is an instruction manual on how to get the most of this tool.

Materials to have in hand: Year-end account statements for all your retirement accounts, be they registered retirement savings plans, tax-free savings accounts or non-registered accounts. Also, estimates of what your annual Canada Pension Plan, Old Age Security and company pension benefits will be, if any. If you need help figuring out how much OAS and CPP you're in line to receive then a federal government online calculator may help. Companies typically mail out pension updates once per year – if you don't have the latest, consult your firm's human resources department.

Introductory instructions: Build an estimate of what your monthly income will be from CPP, OAS and any company pensions. Then, add up the assets in your various retirement savings accounts and decide how much you will want to remove every year for additional income. The spreadsheet's job is to tell you how feasible that plan is.

Now, fire up the spreadsheet and familiarize yourself with how it works: Column A: That's where you add your estimate of the inflation rate, which Dr. Barkow set at 2.3 per cent. You can access the central bank's inflation calculator at bankofcanada.ca/rates/related/inflation-calculator/.

Column B: Add the year you'll leave the workforce. After you plug in your year, the next numbers in sequence should automatically align.

Column C: Counts your years in retirement.

Column D: Your age on Dec. 31; 60 is the default number set here by Dr. Barkow – adjust as required by your situation.

Story continues below advertisement

Column E: Start of year portfolio values, as based on estimated data or your actual investment returns and withdrawal amounts.

Column F: Your investment gains for the year. To start, the gains will be estimated based on your projected rate of return and withdrawals. Then, as you add your annual results, the data will become reflective of your actual experience.

Column G: Your annual withdrawal of money for living expenses, adjusted higher each year by the projected rate of inflation. Note that this column is titled Net Account Transactions for the Year – the reason is that if you had a windfall of some sort, you may end up putting money into your account on a net basis rather than making a withdrawal.

Column H: Shows your year-end balance, factoring in investment gains and the amount withdrawn.

Column I: Shows your investment growth based on either your hypothetical numbers or your actual returns. Hypothetical returns are averaged, so they don't show big ups and downs in particular years.

Column J: Your average cumulative growth rate.

Story continues below advertisement

Extra features

This spreadsheet allows you to factor one-time financial events into your analysis, such as an inheritance or the sale of a home.

Using the numbers pre-loaded into the spreadsheet, let's say you sell your home in 2018 for a net $500,000 and right away buy a condo for a total $400,000. Then, you decide to add the remaining $100,000 to your investments, minus the usual withdrawal of $22,924 for the year.

Before you add the positive number to the appropriate cell in column G, copy down the regular 2019 withdraw amount on a piece of paper. You need to do this because the spreadsheet may start assuming that annual deposits of $77,076 will be repeated. Bring things back to reality by typing the normal withdrawal number in to the proper slot for 2019, which in this example is $23,451. Doing this will bring all future withdrawals back in line. Much the same procedure would apply if you wanted to take extra money out of your savings one year. Remember to resume your normal withdrawal in the next year.

Yearly maintenance

As you live a few years with your spreadsheet, you'll start to get a better idea of how accurate your inflation and return expectations are. Your experience of inflation as a retiree might be different than 2.3 per cent – change that number as required. Your returns expectations can be adjusted as well. In fact, the spreadsheet is a great way to see how long your money would last if you settled for returns of 3 or 4 per cent in order to tamp down risk in your portfolios.

Finally, you'll want to keep track of what financial market ups and downs are doing to your expected retirement income flow. "Once you start putting your life into the spreadsheet, one thing you look for is, at what age do you die broke?" Dr. Barkow said. "With the big decline in the market in 2008, I was going to die broke at age 89," he said. "Normally, I'm at 97-98."

Report an error Editorial code of conduct
Tickers mentioned in this story
Unchecking box will stop auto data updates
Comments

Welcome to The Globe and Mail’s comment community. This is a space where subscribers can engage with each other and Globe staff. Non-subscribers can read and sort comments but will not be able to engage with them in any way. Click here to subscribe.

If you would like to write a letter to the editor, please forward it to letters@globeandmail.com. Readers can also interact with The Globe on Facebook and Twitter .

Welcome to The Globe and Mail’s comment community. This is a space where subscribers can engage with each other and Globe staff. Non-subscribers can read and sort comments but will not be able to engage with them in any way. Click here to subscribe.

If you would like to write a letter to the editor, please forward it to letters@globeandmail.com. Readers can also interact with The Globe on Facebook and Twitter .

Welcome to The Globe and Mail’s comment community. This is a space where subscribers can engage with each other and Globe staff.

We aim to create a safe and valuable space for discussion and debate. That means:

  • All comments will be reviewed by one or more moderators before being posted to the site. This should only take a few moments.
  • Treat others as you wish to be treated
  • Criticize ideas, not people
  • Stay on topic
  • Avoid the use of toxic and offensive language
  • Flag bad behaviour

Comments that violate our community guidelines will be removed. Commenters who repeatedly violate community guidelines may be suspended, causing them to temporarily lose their ability to engage with comments.

Read our community guidelines here

Discussion loading ...

Due to technical reasons, we have temporarily removed commenting from our articles. We hope to have this fixed soon. Thank you for your patience. If you are looking to give feedback on our new site, please send it along to feedback@globeandmail.com. If you want to write a letter to the editor, please forward to letters@globeandmail.com.
Cannabis pro newsletter