Calculating TFSA and RRSP Totals at Retirement


Calculating TFSA and RRSP Totals at Retirement 3If you have been putting money into an RRSP and/or TFSA over the years, have you ever projected how much you will have when you retire? If you are just starting, what will you have if you save some money each year? This is important because it will determine what is available for a yearly income when you retire. The spreadsheet provided lets you try out different options. The results may surprise you as even a small amount of savings each year combined with compounding can really add up.

Background

You have probably been advised to save as much as possible for retirement as the government provided Canada Pension Plan and the Old Age Security will not be enough. If you are fortunate enough to have a company pension, this will also add to your retirement income. Please refer to Understanding Your Canadian Sources Of Retirement Income for details and a spreadsheet for estimating what you will have. Two sources of income are included but not calculated in the post. These are the Registered Retirement Saving Plan (which must be converted to a Registered Retirement Income Fund by age 71) and the Tax Free Savings Account. These are dealt with in this post.

Both the TFSA and the RRSP accumulate tax-free over your lifetime. The RRSP is tax deductible when bought in a year while the TFSA is not. However, when converted to a RRIF, withdrawals are completely taxed as income. On the other hand, TFSA withdrawals are not taxed. You may find the following two posts of interest as they provide background on both types of investments: TFSA or RRSP – Impact of Reinvesting the Tax Refund Using 2015 Rates and TFSA and RRSP – Providing a Fixed Retirement Income Using 2015 Rates.

Spreadsheet Format

The above posts do not really answer the original question of how much will you have in your TFSA and RRSP when you retire. Click on Download Spreadsheet and open it so that you can try out a few options that will help you answer this question.

The spreadsheet consists of an area for entering data (upper-left, numbered 1 to 10), a summary of the totals (#11 to 13), a table for adjusting Percent, Yearly and Extra values for any year (starting at row 22) and 3 charts (TFSA, RRSP and TFSA + RRSP by Age) for viewing the results.

The key to calculating the totals at age 71 is to decide how you will save for your retirement. If you will save the same amount each year, enter the values in the upper-left area. If you expect to do it once in a while, select a few years and enter the amounts into the Extras column.

Entering Data

The best way to explain the spreadsheet is use an example. You can then adjust it to your own circumstances. You can also try out different options, such as interest rates to see how the total changes. One very useful analysis is to use a 0% interest rate and then a realistic interest rate to see how much compounding interest increases the totals. The impact can be eye-openingCalculating TFSA and RRSP Totals at Retirement Entry.Your Age Now (#1) will fill the table’s Age column A starting at row 24 with ages. If you enter 18, it will show ages up to 71. This number of ages has been chosen because you must be 18 to own an RRSP or TFSA and after 71 you must convert the RRSP to an annuity or a RRIF. If you enter a greater age, there will be ages greater than 71 but they will not be used in the calculations. They will show on the charts, but they will have a 0 value. An age of 30 is used in the example.

Stop Investing at Age (#2) is required in case you want to stop investing before age 71. You can enter any age and the numbers in the table and the charts will change. If you leave it empty all Yearly values will be 0. An age of 65 is used in the example.

TFSA Start Investing at Age (#3) can be set to any age. Leave it empty if you want it to start at your age now. The TFSA Yearly column will have values starting at the age entered.

TFSA Capital at Your Age Now (#4) – Enter the present value of all your TFSAs.

TFSA Yearly Amount Invested (#5) – Enter the amount you want to save each year in TFSAs. The value will appear in the TFSA Yearly column starting at the age you entered in #3. You can change the amount for any specific year in the table if desired.

TFSA Yearly Interest Rate (#6) – Enter the interest rate, as a percent, you expect to earn in the future. The rate will appear in the TFSA Interest % column starting at the age you entered in #1. You can change the rate for any specific year in the table if desired.

There are the same four values for RRSPs in #7, #8, #9 and #10. Enter the appropriate amounts for each.Calculating TFSA and RRSP Totals at Retirement Totals

TFSA Total Capital at Age 71 (#11) – This is calculated in the tables below and is summarized here so you can see what you will have at age 71.

RRSP Total Capital at Age 71 (#12) – This is calculated in the tables below and is summarized here so you can see what you will have at age 71.

Total Capital at Age 71 (#13) – This is the addition of #11 and #12.

Changing Data in any Year

The table below the entry area has columns for TFSA Interest %, TFSA Yearly, TFSA Extras, RRSP Interest %, RRSP Yearly and RRSP Extras. You can override the defaults by entering values as desired. If you want to restore the default, select a cell that has not been changed and copy it to the cell that was changed. This will restore the formulas.Calculating TFSA and RRSP Totals at Retirement Table

Note that Extras have been added for 2 years. These also show in the charts as a step on the charts.

Calculations

The calculation are done is the last 9 columns and are used for the charts. Do not change anything in these columns.

Capital Charts

There are separate charts for TFSA and RRSP. Each has lines for the Starting capital plus any Extras, the Yearly capital and the Total. The ages shown are for the complete table, so it starts at your age now and goes for another 54 years. All lines go to zero after age 71, so the lines change abruptly. There may be a macro that can be designed to only chart ages up to 71, but I have not been able to design one. Any suggestions?Calculating TFSA and RRSP Totals at Retirement 1&2

To make it easy to see the sum of the above charts, a third chart shows TFSA + RRSP.Calculating TFSA and RRSP Totals at Retirement 3

How Will You Use the Total Capital?

Now that you have a projected value of your TFSAs and your RRSPs, what can you do with them? Click on Setting TFSA and RRIF Withdrawals to determine how you can withdraw income and how it affects your capital.

More Comprehensive Solution

You can achieve the same results as provide by this spreadsheet associated with this post by using FinanceBase-Lifetime Finances which is more robust. It uses all of your assets (not just TFSAs and RRSPs), income & expenses during your lifetime to project whether you will outlive your money. It shows you how your TFSAs and RRSPs will grow each year. 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. It can be downloaded free in a demo mode for 31 days. After that a license is required which is a very nominal amount.