Skip to main content
portfolio strategy

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."

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.

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.

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."

Follow related authors and topics

Authors and topics you follow will be added to your personal news feed in Following.

Interact with The Globe