When you retire, or if you are already retired, and you have TFSA and RRSP/RRIF, you will want to set your withdrawals to match your income needs. You should also manage how your capital is being depleted. The spreadsheet provided offers options for doing both.
To use the spreadsheet you should either have developed a savings plan that will provide you with Tax Free Savings Account and Registered Retirement Saving Plan funds by the time you have retired, such as provided by Calculating TFSA and RRSP Totals at Retirement, or you have already retired and have these funds. Now you need to determine how much you can withdraw from your TFSA and RRSP and how long your capital will last.
This is not particularly a simple task as you need to take into account all of your other source of income (see Understanding Your Canadian Sources Of Retirement Income for details). You should also understand how you will live in retirement, what expenditures you must save for and what extras you would like to be able to spend on. For a comprehensive solution, I strongly urge you to use the lifetime cash flow calculator provided by FinanceBase-Lifetime Finances.
However, if you only want to get a handle on the TFSA and RRSP/RRIF (Registered Retirement Income Fund) part of your income, continue reading.
Click on Download Spreadsheet and open it so that you can follow along with the example below and then customize it to your circumstances.
The spreadsheet consists of an area for entering data (upper-left, numbered 1 to 9), 3 charts (TFSA, RRIF and TFSA + RRSP Withdrawals and Capital by Year) for viewing the results and a where all the calculations are performed.
Start Withdrawals at Age (#1) will accept any age between 71 and 99. If you are not retired yet, use 71. If you are already past 71, enter your age. Any other age will cause the charts to be empty.
Use Inflation of (#2) – You can set the withdrawals to increase by whatever inflation percent you enter. The only exception is that if the RRIF Minimum Withdrawal/Yr (#9) is less than the required Minimum Payout.
Payout at start of year (#3) – You can ask your financial institution to provide amounts at any time throughout a year. Not knowing what works best for you, the spreadsheet provides two options: at the beginning of the year and at the end of the year. To use the end of the year, enter No and watch the charts. They withdrawals typically extend to at least one year longer.
TFSA Capital at age above (#4) – Enter the capital that you have in all of your TFSAs at the start of the year for the age entered. This will show on Chart 1 as the dotted line that uses the right axis.
TFSA Yearly Interest (#5) – Enter the percent that you expect the TFSAs to earn. Use the average of all your TFSAs now. If you expect to increase over the next few years, use the expected number. There is no way to change the rate for any specific year.
TFSA Minimum Withdrawal/Yr (#6) – Enter the amount you want withdrawn each year from your accounts. Again, there is no way to change the amount for any specific year.
There are the same three values for RRSPs in #7, #8, and #9. Enter the appropriate amounts for each. The results will appear in Chart 2.
Withdrawal and Capital Charts
There are separate charts for TFSA and RRIF. Each has lines for the Withdrawal and Capital remaining for each year. The Withdrawal line is solid and uses the left axis and the Capital is dotted and uses the right axis.
Change the Inflation, Interest rates and Withdrawals to see what happens. You need to decide when you want your capital go to zero and adjust these values to make it happen. The charts go to an age of 100, but it is most unlikely that you will live that long. You may which to leave an estate, so take that into account when deciding how your capital is to be depleted.
Taking withdrawals that are inflated by the amount in #2 has a significant effect on how long withdrawals last. Try using 0% and then an expected 2% to see the difference.
If the RRIF Minimum Withdrawal/Yr is set below the mandatory payout, Chart 2 will show the mandatory payout until it falls below the inflated Minimum Withdrawal. Setting the Withdrawal to 0 will ensure that only the mandatory payout will be used. If you do this you will find that no matter what the inflation rate, you will have a lot of capital left at age 94. This is probably not what you want, so experiment with Withdrawal amounts to reduce the capital.
Hints: If you download and open the spreadsheet, you can view the actual value of each pointon any of the lines on the graphs by hovering over the line. Excel will then show the Series “name”, the Point “age” and Value “$”. If you want to see the data used in the charts and the formulas used, scroll down to see the tables. If you click on any line on a chart, the areas used will be highlighted in the tables.
Cumulative Withdrawals Chart
Chart 4 shows the cumulative withdrawals by year for TFSA and RRIF. This chart is provided to make the analysis discussed in You Need to Look at Your RRIF Withdrawals easier to do: deciding if it is better to use a fixed withdrawal for a RRIF to deplete the capital as some age or leave capital as an inheritance.
All of the calculations for the charts are performed in the table below the charts. Do not make any changes. For details for any age, just scroll down and view the values in each column. In particular, the Capital and Cumulative Withdrawal columns give you the exact value.