This post describes a spreadsheet that you can download that shows how you can use RRSPs and TFSAs to generate a fixed retirement income each year. You will probably have a number of both RRSPs and TFSAs due to the restrictive amount of money you can invest in the TFSA. Knowing what they can provide as income before the capital is exhausted is critical to retirement planning.
Previous Posts of Interest
This is the fourth in a series of posts and their associated Excel spreadsheets that deal with RRSP/RRIF and TFSA. They were created to provide background to this post which pulls together many of the concepts presented. The previous posts are as follows:
1) Canadian Marginal Tax Rates – 2104 which provides the combined Federal and Provincial tax rates.
2) RRIF Minimum Payouts which shows the minimum payouts mandated by the Federal government and the impact of the taxes and the payouts on remaining capital.
3) TFSA or RRSP – The Impact of Reinvesting the Tax Refund shows that even when all of the tax refunds are reinvested into the RRSP, the TFSA is the better retirement payout option up to age 90.
At any time while reading this post some additional details are required, return to this section and click on the above links to open them in a new page.
Reason for this Post
The purpose of saving for retirement is to ensure that you have enough income to provide you with an acceptable standard of living. Each person and family has a different view of what this should be. Some will want to travel, others will want to spend time with far-flung family, etc. It is almost impossible to determine what you want your retirement to be when you are young, but you still need to save for it. There are a lot of rules-of-thumb as to how much income and/or you need when you retire, but none of them can be relied upon on all situations.
Your best bet is to inform yourself about the options available and gain a better understand about the underlying principles of finances. For example, the power of compounding interest is a key to growing assets to fund retirement and the longer the period the better the results; the effect of taxes and the marginal tax rates are a liability that must be taken into account; managing cash flow and it impact on capital.
This post provides a view of an important financial aspect: creating a fixed cash flow during retirement. It starts with a description of the entry area of the Excel spreadsheet and moves on to the charts that describe the retirement status.
Please click on Download Spreadsheet now and open it if you want to use it and to follow along and make the changes indicated below.
The spreadsheet has an entry area and four charts. The Entry area is almost the same as that given in TFSA or RRSP – The Impact of Reinvesting the Tax Refund except that values for both the RRSP and TFSA can be entered and there is a section for Existing Amounts. An explanation of the lines in the entry area as shown on the figure below follows. The lines that have a border and are bold can be changed to new values from those given.
Enter any values into the Yearly Amount (1). There is no check if it exceeds the statutory limits, so be careful. RRSP of $2,000 and TFSA of $2,000 have been used for this example. These values where chosen to maximize the TFSA and provide for some RRSP. The RRSP is assumed to be half by an employer while the TFSA is personal for a total of $3,000 personal investment. As the median individual income is around $32K, this represents just under the 10% many advisors recommend as a savings objective. Higher values can be entered for those individuals that exceed the median.
The marginal tax rate (2) can be whatever value is desired. Refer to the Canadian Marginal Tax Rates – 2014 to determine your rates. The combined Federal and Ontario rate of 20.05% has been used consistent with using the median income level. The tax refund (3) will be $401. The tax refund is 100% reinvested (4) as recommended in TFSA or RRSP – The Impact of Reinvesting the Tax Refund so that the Amount invested (6) is different for the RRSP and the TFSA. For a yearly interest rate (7) of 5%, over 30 years (8), the TFSA and the RRSP will have different End Amounts as shown in (9).
You can enter any values into the Existing Amount (10) that you may already have. For this example, the RRSP has $10K and the TFSA has $5K. Using the same interest rate in (7), the Amount at the end of period (11) for these Existing Amounts for the 30 years is shown. Line (12), Total End Amount, adds lines (9) and (11) to provide the starting amounts for the payout period discussed below.
The Total Out of Pocket amount invested is shown in (13) and the total of all the yearly tax refunds is shown in (14) which in this case is 0.
Change any of these values on the spreadsheet to your situation, or try out different scenarios.
Payout Period – After Age 71
To determine the proper tax bracket, Other Income after age 71/yr (15) of $15,000 is used. The type of income to be included will be the Canada Pension Plan payments, company pension, interest on other investments, but not the Old Age Security. The Yearly Interest rate (16) can be changed from the 4% to whatever is desired. The Minimum Fixed Payout (17) is set to $19,000. This is higher than the minimum required RRIF payout of $15,550. It was intentionally used so that the RRIF and TFSA capital both go to zero before age 100.
Notice that there is no marginal tax rate as it is automatically calculated using the table on the spreadsheet which is for Ontario. The rates can be changed by opening the Canadian Marginal Tax Rates – 2014 post or associated spreadsheet and copying and then pasting (values only) the From and Fed+Prov columns to the two columns on the spreadsheet for this post.
Chart 1 shows the Payout and Available Amount per year for the RRIF, TFSA and the total. During Payout, the RRIF is used first until it is no longer able to provide the desired Available funds. After that the TFSA is used. This is not particularly tax efficient but is good enough to show what can be done.
The dotted purple line is the actual payout from the RRIF which is always higher than that Available because of the taxes (the solid black line). The RRIF Avail line only goes to age 80 when the RRIF cannot supply the $19,000 fixed available amount desired (as entered in Line 17). After that, the TFSA is used to make up the difference as shown in the dotted orange line. It also goes to zero but at age 99 when the TFSA capital is all gone. The solid purple line is the sum of the RRIF Avail and TFSA Avail amounts and is flat at $19,000 until the TFSA has been exhausted.
Changing the Minimum Fixed Available amount to, for example, $22K, will cause the RRIF Available line to start to drop at age 79 and the TFSA to exhaust at age 92.
While recent statistics for life expectancy in Canada is 80 years of age for men and 84 for women, the probability of living to age 90 is above 40% for people born in the mid-1960s. Thus, it would be prudent to plan for income and capital after age 90 and not use too high a Minimum Fixed Available amount.
Remaining Capital Chart
Chart 2 shows the Remaining Capital for the RRIF, TFSA, the total and the total after taxes.
The solid red line is the remaining TFSA capital. Notice it goes to zero near age 98 (actually at age 99 but it is hard to see). The solid blue line is for the RRIF remaining capital before taxes. The dotted black line is what you would receive from the RRIF if was liquidated and the marginal taxes were paid. Note that it is significantly below the actual RRIF capital, because the taxes are over 40%. The Net Worth purple line show how much the remaining capital less the tax liability is for each year. Because the TFSA has no tax liability, the Net Worth and TFSA End lines are the same after the RRIF has been exhausted.
For the example used, the total capital saved over 30 years is over $370K. This can supply $19K of retirement income, which, when added to the $15K of other income yields a total of $34K. This is over the median income level used in this example. It shows how a relatively small investment each year can result in a substantial income in retirement. You just have to disciplined to keep saving, even if it hurts!
However, none of these amounts take into account the effect of inflation, which will cause the income to be less in purchasing power 30 years in the future.
More Comprehensive Solution
I found that dealing with the impact of inflation is beyond the ability of Excel spreadsheets to handle. Also, dealing with all of the different types of income, assets and taxes requires much more that a spreadsheet. Consequently, a few years ago I developed a comprehensive application called RetireBase. It uses your assets, income & expenses during your lifetime to project whether you will outlive your money. It can be used at any age and provides a yearly cash flow (income less expenses, including taxes) from now to 45 years after retirement. If desired, changes can be made in any year. You can see what it provides by clicking on this link to RetireBase Overview. It can be downloaded free in a demo mode for 31 days. After that a license is required which is a very nominal amount.