Estimating Capital Totals at Retirement


Estimating Capital Totals at RetirementChart4If you have been putting money into an RRSP, TFSA or other investments 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.
A previous post, Calculating TFSA and RRSP Totals at Retirement, only dealt with the Tax Free Savings Account (TFSA) and the Registered Retirement Savings Plan (RRSP). This post adds all Other investments (such as Mutual funds, GICs, Stocks). It uses a different format and has many different values that can be entered. It is also color coded to match those of Estimating How Much You Need In Retirement.

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 Estimating How Much You Need In Retirement for details and a spreadsheet for estimating what you will have. Three 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, RRIF, by age 71) and the Tax Free Savings Account and all Other investments. These are dealt with in this post.

Both the TFSA and the RRSP accumulate tax-free over your lifetime. The RRSP is tax deductible while the TFSA is not. However, when the RRSP is 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. All Other investments such as Stocks, Mutual Funds, Guaranteed Investment Certificates, etc., that are not held in a TFSA or RRSP are taxable. Some, such as the GIC, have their interest taxed yearly, while stocks are only taxed on the capital gains when sold, unless they issue dividends that are taxed in the year they are issued.

Spreadsheet Format

The above posts do not really answer the original question of how much will you have in your TFSA, RRSP and Other accounts 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 is loaded with data for the example given below so that you can see the type of values to be used. Change any values to those that meet your situation.

To clear any cell, just click on it and press the Delete key and then the Tab key or click on another cell. You can also delete multiple cells by selecting then, pressing on the Delete key and then pressing together the Return and Command (for Mac) or cntl (for Windows) keys. (Only delete cells that are outlined or else you will remove the equations that are used for calculations.)

All cells with enterable data can be cleared by clicking on the Clear Data button. This button uses a simple macro to clear the cells. When you open the spreadsheet, and you are using a Mac, you will be asked if you want to enable the macro. Click on the Enable Macros button to activate the Clear Data button. If you are using Windows, there are different options depending on the version of Excel you are using. Usually, there is a Security Warning with an Enable Content button. Just click on it to activate the Clear Data button.

The spreadsheet consists of an area for entering data (upper-left, numbered 1 to 7), a summary of the totals for a few years close to retirement (#8 to 15), a table for adjusting Percent, Yearly and Extra values for any year (starting at row 38) and 4 charts (TFSA, RRSP, Other and Total by Age) for viewing the results.

The key to calculating the totals 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 Extra columns.

Example Used

The example is for a 40 year-old person who expects to retire at age 65. This same example is used in Estimating How Much You Need In Retirement. The person’s income now is $45,000, which is above the median of $32,020 as indicated in StatsCan – How Much Do Individual Canadians Make? This income is also below the maximum Canada Pension Plan income for 2015 of $53,600.

A savings rate of 3% is used for RRSP with 3% matched by the company for a total of $2,700 for the first year. Another 3% of $1,350 is saved to a TFSA. Finally, 1% is saved to Other investments for a total savings rate of 7%. This continues for each year until retirement, except as noted below.

The value of the capital at age 65 (about $306,000) along with other income is enough to provide an income of over $36,000 per year that should cover expenses expected during retirement, as is shown in Estimating How Much You Need In Retirement. As shown below, the interest rates used are very conservative. This capital is significantly less than the $1M that at least 50% of responders to the survey discussed in Survey Shows Canadians Are Not Prepared For Retirement think they need.

If this person is part of a “Couple family” as shown on the StatsCan website, Family income, by family type (Couple families), the median total income for 2013 was $84,080. This means that the person contributes a little more than half of this median income.

Entering Data

The best way to explain the spreadsheet is use the example given above. 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 opening.Estimating Capital Totals at Retirement1-7

Your Age Now (#1) will fill the table’s Age column A starting at row 38 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 and all will show on the charts.

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 in the table will be 0.

Start Investing at Age (#3) can be set to any age for each of the three columns to the right (TFSA, RRSP and Other). If you leave it empty it will to start at your age now.

Capital at Your Age Now (#4) – Enter the present value of all your TFSA, RRSP and Other accounts in the appropriate column.

Yearly Amount Invested (#5) – Enter the amount you want to save each year in each of the accounts. The value will appear in the Yearly column for each account in the table that starts at row 38, starting at the age you entered in #3. You can change the amount for any specific year in the table if desired.

Yearly Interest Rate (#7) – Enter the interest rate, as a percent, you expect to earn in the future for each of the accounts. The rate will appear in the Interest % column for each age. You can change the rate for any specific year in the table if desired.Estimating Capital Totals at Retirement8-16

Total Capital at Age (#8 to 15) – You can enter any age you want into the cell at row 13, column B (#8). The cells below will increase by 1 year. The columns to the right are the totals for each account at the age given and come from the tables below.

Total Capital at Age (#16) – This is the age of the last entry in the table. It is also the last age of each of the charts. It is provide to give you an indication of what the totals would be for all the ages that are in the table. Do not use these amounts as they normally are for an age that is much greater than your retirement age.

Total Column (#4 to 16) – These totals are in column F. They are the sum of the 3 columns to the left, except for Increase in Yearly Amount/Yr (#6) and Yearly Interest Rate (#7), which are the average.

Changing Data in any Year

The table below the entry area has columns for TFSA Interest %, TFSA Yearly, TFSA Extra, RRSP Interest %, RRSP Yearly, RRSP Extra, Other Interest %, Other Yearly and Other Extra. You can override the defaults in the Interest and Yearly columns 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.Estimating Capital Totals at RetirementTable

Note that an Extra has been added for 1 year to TFSA. This also shows in the TFSA chart as a step on the chart.

Calculations

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

Capital Charts

There are separate charts for TFSA, RRSP and Other. 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.Estimating Capital Totals at RetirementChart1-3

To make it easy to see the sum of the above charts, a fourth chart shows the Total (TFSA + RRSP + Other).Estimating Capital Totals at RetirementChart4

How Will You Use the Total Capital?

Now that you have a projected value of your TFSAs, RRSP and Other Investments, what can you do with them? Click on Estimating How Much You Need In Retirement 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 the spreadsheet associated with this post by using Lifetime Finances which is more robust. It uses all of your assets, income & expenses during your lifetime to project whether you will outlive your money. It shows you how your assets 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.