Retirement Calculator – Part 2 of 3 – Details

July 3rd, 2012 by Potato

Be sure to read the first post on the Retirement Calculator.

Here you’ll find some details on how I designed the retirement calculator. If you’re industrious, you’ll probably see some areas where it can be tweaked and improved. It is, in my opinion, a good and handy tool to have, but I know that the future is so uncertain that having a decent approximation is probably as good as you can expect to rely on, so there are definitely diminishing returns to trying to squeeze out more exact calculations.

The calculations basically assume that you’ll have some level of after-tax spending needs that you’ll be paying for out of a combination of pension, CPP, and OAS income as well as your savings/investments across non-registered, RRSP, and TFSA accounts. Those spending needs will increase year-by-year with inflation, and your investments will grow with stock and fixed income returns. Everything on the calculations page is run in real dollars, but if you’re just using the front user-interface sheet, you won’t notice those (the exception is taxes, which are paid on nominal returns).

A certain amount of cash will be kept on hand in non-registered fixed income to cover spending needs: at least enough to cover the following year’s spending needs. The RRSP is assumed to be in a mix of fixed income and equities, with that ratio being set as a multiple of spending needs (by default, 5 years of spending needs in fixed income) — you could adjust it to use one of the age-based asset allocation rules-of-thumb if you like. Regular withdrawals will be made from the RRSP into the non-registered accounts, whether they’re needed or not, with more being withdrawn if it’s needed for spending. From age 71 onward, it is a rule of the RRIF that a certain percentage must be withdrawn — the spreadsheet incorporates this, but also assumes smaller percentage withdrawals starting immediately upon retirement. The TFSA and non-registered equities are assumed to be in 100% equities, and will be used up before additional RRSP withdrawals are made.

The spreadsheet is fairly generalized: you can use it as a single person, or combine your expenses and income with your spouse, it shouldn’t matter either way.

In detail:

Spending budget: Simply, how much you’ll want to spend each year. If you don’t really know what you’re going to need to spend, then you can use a rule of thumb, such as a percentage of your spending pre-retirment (e.g.: 70%). I recommend starting with a full budget to see where the money is going, which would also help you give a better idea of how inflation might affect those spending requirements later.

Spending 5-Year Bonus Amount: Budgets are rarely smooth, in my experience. Plus when drawing up your budget for an average year, you may forget rare large expenses, such as buying a new car, or having a major house repair bill. This factor will build in a “bonus” spending amount every 5 years in the amount you specify. For example, if you put in $20,000, that might represent a new ~$20k car every 10 years, and a renovation/repair project every 10 years, with the two alternating every 5.

Pension Income: Few people are lucky enough to have a defined benefit pension, but if you managed to accrue a few years of service in one, you can put in your annual pension income here. Many pension plans include a top-up for those who retire at 60 to partially cover CPP that isn’t activated until age 65. If you want to fiddle with retirement age, you’ll likely need a copy of your pension statement or a pension calculator to figure out how to adjust the amounts for different ages. Any pension income is assumed to be inflation-indexed to the CPI rate.

CPP: Your annual Canada Pension Plan earnings: if you’re close to retirement and want to be precise, you can contact Service Canada to get a statement of what your CPP payout will be. They get put in at age 65, though it is possible to claim them early or defer them. Not hard to change directly on the calculations sheet if you’re so inclined.

OAS: Old Age Security payments. I think I got the default right for a couple starting at age 65. Note that the spreadsheet will not calculate clawbacks (if you make more than a certain threshold — near ~$60k/year I believe — your OAS payments will be reduced). If you will have higher income, you may want to adjust down the OAS, or build in a check for yourself in the calculations page.

Inflation will be an important factor, particularly over the length of time you’ll be retired. Officially, inflation is measured by the consumer price index (CPI), and that’s the metric many things like pensions are adjusted to. On the calculations side, it’s also the figure used to keep everything in real dollars.

However, the basket of goods used to calculate CPI may be different than the inflation rate your spending actually increases by. For instance, if you’re retired and spending most of your money on food, entertainment, energy, and utilities, then you could find your experienced inflation higher than the CPI one if those items increase in price faster than the consumer goods, clothing, and alcohol you’re not buying. Or, if you expect your spending patterns may change over time at a rate different than that of CPI: maybe as you age you’ll spend less on parties and traveling, or perhaps your medical care costs will march ever upwards. Enter your anticipated personal inflation rate in the appropriate box.

Return Rates: The rate of return for equities and fixed income is pretty self-explanatory. For equities in particular, you’ll hardly ever see the actual return exactly match the average you use here. So be sure to use a reasonable assumption for long-term returns, and don’t get swayed too much by recent events (either too high or too low).

Average Tax Rate: The tax calculation is extremely simple: just enter an average tax rate. A fudge factor is used on non-registered equity investments to indicate that they’re more tax-efficient than regular income (I simplistically used a 75% inclusion rate). The taxes are charged on and paid out of income — so if you’re burning through your TFSA or savings, the spreadsheet won’t be taking tax off that. It’s just an approximation to try to account for the fact that you’ll need after-tax income to pay your expenses, and the tax rate is user-entered and flat across all incomes and ages: it should be close enough (and doesn’t seem to be a deciding factor in any of the scenarios I’ve tinkered with). Plus, it’s a lot of bother to try to squeeze more false precision out of it. Just remember that your average tax rate is not the same as your marginal tax bracket.

The amounts in each pool of money should be self-explanatory. The number of years to keep in fixed income is for the RRSP: the tool assumes that asset allocation will be all equities except for X number of years worth of expenses kept in fixed income. For the other accounts, it’s assumed the TFSA is all equities (at the moment it’s so small I figured it was a safe assumption, but that will age the tool in a few more years), and there’s no asset allocation calculation for the non-registered accounts: it just lets you set how much goes into each, and runs from there.

All those inputs work together to give you one short output: the age you’ll be when the money runs out.

The answer to “can I retire now?” will depend on a number of factors included in the calculation, your own personal criteria, and whether that output number comes out as older or younger than your criteria figure (e.g.: your maximum expected lifespan).

Stay tuned for the next part, discussing how to use this spreadsheet to run multiple scenarios.

Comments are closed.