A general rule is that you should be saving 10% of your salary for retirement. Because everyone’s situation is different, how do you know if this is too much or not enough. How does your pension, CPP and OAS affect this number? The spreadsheet combines two of the most popular posts to help answer this question.
This post combines the posts and spreadsheets of two of the most popular posts: Estimating Capital Totals at Retirement and Estimating How Much You Need In Retirement. Now you can do everything on one spreadsheet. You can view your yearly payments into and withdrawn from investment assets and the change in capital over your life time on charts. In addition, it directly relates your savings to your salary as a percent. This will help you answer if a 10% savings rate for your retirement is correct for you.
Update March 20, 2019
I found an error in the spreadsheet when calculating the Guaranteed Income Supplement (GIS) payments. Because there were changes in the 2011 and 2016 federal budgets, the use of the past reduction in the GIS payments of $1 for every $2 of income no longer applies. In addition, the guarantee to have the same combined Old Age Security (OAS) plus the GIS payments was not included when the OAS payment is not the full amount when a person has less than 40 years of Canadian residency. Details are given in Replacing GIS Payment Tables with Equations. The spreadsheet has been modified to include these changes.
A number of surveys have indicated that most people do not know how much they need for retirement. For example, the 2015 Canadian Payroll Association survey indicates that many people believe that they will have to work a number of years after age 65 before they can retire. It also indicates that responders felt they needed over $ 1 million in assets.
Many advisors recommend that you set a retirement income objective of 70% to 80% of your present expenses. There is no mention of what age or stage of life this applies to. For example, a person at age 25 has a different set of expenses than a family with children and a mortgage at age 40.
One of the best posts I have come across is from November 2014 by David Aston in Money Sense, How much you really need to retire – Here’s the cost of a typical middle-class retirement. It provides a quote from a financial planner about the above percentages being too high. Aston’s own experience is that 50% to 60% is a better rule of thumb for couples and 60% to 70% for singles.
The spreadsheet provided in this post and the comments below make it easier for you to take control of your own retirement amounts so that you do not have to rely on the many “rules of thumb” that really do not match your situation.
Where Does Your Retirement Income Come From?
During retirement you must live on income provided by government pensions and services, your company pension plan (if you are fortunate enough to have one), your own retirement pension plans and your investments. A detailed explanation of these various sources is provide in Understanding Your Canadian Sources Of Retirement Income. (These have all been included in the spreadsheet of this post.)
In Summary, for the non-government income, both the TFSA (Tax Free Savings Account) and the RRSP (Registered Retirement Savings Plan) accumulate tax-free over your lifetime. The RRSP is tax deductible when bought in a year while the TFSA is not. However, when the RRSP is converted to a RRIF (Registered Retirement Income Fund), 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 (GIC), 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.
Estimating What Income You Need In Retirement
Because every person and family is different, the best way to come to an estimate of required retirement income is to make a list of the expenses you incur now and then remove and add those that you expect when retired. Then adjust the amounts to what you think they would be, if you were retired now. These are all to be in today’s dollars. These amounts will then be increased by inflation to the year you retire.
If you do not already use a budget, a simple spreadsheet is available to help you estimate your expenses as part of Easy to Use Budget Method and Spreadsheet. Just enter the expenses that you have today that you expect to have when you retire. For example, do not include any mortgage, children’s expenses, work-related expense and add trips, etc. This will then be the income you need to have when you retire, before inflation.
Spreadsheet Shortcomings and an Alternative
Before continuing, I would like to indicate some of the missing features in the spreadsheet. The spreadsheets provided in this post and the ones mentioned above are the best that I can do with Excel. They do not include taxes, such investments as shares and dividends, spouses, expense details such as a mortgage, payroll deductions or an automatic movement of assets to match expenses.
Consequently, a few years ago I developed a comprehensive application called Lifetime Finances. 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 to such items as interest and additions to and selling of assets. You can see what it provides by clicking on this link to FinanceBase-Lifetime Finances. It can be downloaded free in a demo mode for 31 days. After that a license is required which is a very nominal amount. There is also a Lite version that sell for half the price, but with only half the features, but it does much more than the spreadsheet.
Please try it as it has a lot of features, takes less than half an hour to enter all of your data and can be updated each year or when your circumstances change. It also has an Accounts manager that helps you keep track of your accounts, gross and net worth and includes a very comprehensive share analysis feature.
Example Used in the Spreadsheet
The spreadsheet provided with this post can be used for any age, income, percent allocated to savings and assets. A download link is provided later. To illustrate the use of the spreadsheet, a specific example is used as indicated below.
If you were to invest 10% of your income each year starting at age 25 and going to a retirement age of 65, how much would you have and how much can you use as income when you retire?
For example, if you started with an average Canadian industrial wage, in 2018, of about $55,000 and it increases each year to match a 2.2% Canadian inflation rate, your 10% savings per year (5% allocated to Tax Free Saving Account, 3% to Registered Retirement Plan and 2% to Guaranteed Investment Certificate) at a conservative 3% interest rate would give you a very nice capital amount of $600K at age 65. This amount can payout of $23K per year increasing by inflation until age 94 before the capital is all gone.
When retired, besides the $23K income from the 10% savings, you also will receive CPP payments, Old Age Security and any payments from RRSPs or company pensions. Does this leave you with enough to cover your expenses when you retire some 40 years in the future?
If you assume that all of the $55K income is consumed as expenses, including savings of 10%, you need to guess at how much your expenses at age 65 will be to permit a comparison to your income when retired.
Normally, there are deductions for income taxes, CPP, EI, dues, savings and perhaps heath or life insurance. Usually, only income taxes continue into retirement. If the tax brackets stay about the same over the years, the deductions are probably as follows: 20% income taxes, 5.5% CPP (the new enhanced version, up from 4.9%), 1.7% EI, 3% other (e.g. insurance premiums, union dues) for a total of about 30%. In addition, you will not need a mortgage and the 10% savings. This means that your future expenses, in today’s dollars will be about 60% of the $55K or $33K. (This has to include any lower rate of income taxes, probably less than 10%.) For the example, the amount must be $29.9K (or 54.4%) as this is what is required to match the income available at age 65.
As you can tell this is a complicated issue because of inflation and unknowns such as whether the government continues to increase CPP and OAS payments and if you have some kind of pension. The accompanying spreadsheet lets you enter values needed to make this type of analysis to suit your circumstances and to permit you to try different options.
Click on Download Spreadsheet and open it so that you can enter your own data while following along with the example description below. The spreadsheet has all of the data present below when opened. Enter you own information starting in Section A and watch the charts change and you adapt the spreadsheet to your situation.
The spreadsheet consists of an area for entering data (along the left side, numbered 1 to 29), an area for the OAS, CPP and GIS values used (top right corner, numbered 30 to 37), 3 charts and many tables where some data can be entered below the last chart (you can scroll down to see them). The description that follows below is organized by the sections (A to K) with the line number preceding the entry text where provided.
A) Ages, Income & Expenses
2) Your Total Salary Now – Enter the total of all your salaries today, with no deductions. It is used to determine how much you will be saving up to retirement.
3) Increase Salary per year at – Normally, your salary will increase from now until retirement. Enter a % for how much you expect it to change each year. A good starting value is to use the inflation rate. If desired, you can override the Salary values in the table in section G in any age.
4) Expenses Today – This is set to default to the value of your Total Salary Now (line 2) as it is assumed that your budget is balanced. This line is not really used on the spreadsheet, but is included for compatibility with Lifetime Finances and so you can adjust it if you want. The text to the right of the entry cell shows the % of the Total Salary Now.
5) Expenses at Retirement Age, in Today’s $ – Enter what you will pay today for all of your expenses as if you were to retire today, including taxes. The spreadsheet will then inflate it to your retirement years using the amount entered in line 6.
6) Use Inflation of – The value you enter will be used to inflate Expenses from Your Age Now to the retirement years and during retirement years. Try different values to see what happens to the charts.
7) Retirement Age – Enter when you expect to retire. This age has an effect on a lot of tables and charts in the spreadsheet. Change it at any time to evaluate the impact.
8) Salary the year before Retirement Age – This value is calculated and shows the value of the salary after inflation just before you retire. It is used for the analysis in line 9.
9) Income at Retirement Age – This value is calculated and shows the value of all your income at the start of your retirement. The text to the right compares it to the amount of line 8 so you know how much your income will be decreasing when you retire.
10) Expenses at Retirement Age – This value is calculated and shows the value of your expenses entered in line 5 inflated using the rate of line 6. The text to the right compares it to the amount of line 9 so you know how if you are exceeding your income. If so, the cells are shown in red. Do not do anything to make this go away until you have entered data into the sections below.
B) Government Pensions
11) OAS – Years of Residency when Retired & Start – You must be a resident of Canada for at least 10 years to claim Old Age Security. The maximum is 40 Years. Using Your Age Now and your Retirement Age, you should be able to determine the years that apply to you. You can also enter the Start Age when the OAS is to start. You can enter any age from 65 to 70. As indicated in this Government of Canada link, Old Age Security – Eligibility, you will receive .6% per month (7.2% per year) for each year you delay taking the pension. The starting amount is equal to that in line 30 (which is in the upper-right of the window) times the number of years divided by 40 times the increase in pension for each year later than age 65. If you are already retired, enter the actual amount you received this year in the Starting Amount cell that has a dashed outline (cell F15). This will replace the equation that is there.
12) CPP – % of Maximum Earnings & Start – The Canada Pension Plan calculation requires that you estimate the percent of the maximum earnings that are covered by the CPP that you will earn over your working years. This is a complex calculation that is done for you by Revenue Canada and it cannot be done in a simple spreadsheet, such as this one. However, you can use the spreadsheet provided in When to Take the CPP – Spreadsheets. As an estimate, you might compare your income today to the 2018 maximum that is $55,900. Other years are given in the link above or in this Revenue Canada link, CPP contribution rates, maximums and exemptions. Make sure that you do not claim 100% unless you expect to consistently earn over the limit. You can also enter the Start Age. Only ages from 60 to 70 are accepted. The Starting Amount is reduced by 7.2% per year below 65 or increased 8.4% per year above 65. The amount at age 65 is given in line 31. The Post-Retirement Benefit that can be claimed when working past 60 is not included here.
13) OAS, CPP & GIS Rate Increase/year – The percent you enter will be used to increase the starting OAS, CPP and GIS amounts each year. It is set to default to the inflation rate of line 6. Do not exceed this value as these government pensions, by law, will never exceed the inflation rate, unless it goes negative.
14) GIS when OAS Started– Calculated, do not change – This is calculated by the spreadsheet for each year in retirement. Only for low income will there be a value in the Starting Amount. If you scroll down and to the right, the GIS amounts for each year start in Row 111, Column AK.
C) Investment Assets Before Retirement
In this section, there are columns for TFSA, RRSP and Other. Enter the values requested on each line discussed below. As you make changes, the Total column will change, if appropriate.
The spreadsheet does not include any checks to ensure that you do not exceed the maximum yearly amounts for TFSA and RRSP. You can check how you are doing by examining the amounts in Section G in the columns starting at G54 for TFSA and H54 for RRSP. The Government of Canada keeps track of your TFSA contribution room and has published the 2018 TFSA amount (scroll to the bottom to see the amounts) which is $5,500. RRSP contributions are set at 18% of earned income with a dollar limit of $26,500 for 2018 (which is also shown on the later link above). If you want to ensure the TFSA limits and contribution room are dealt with properly, please try the Lifetime Finances product.
If you are concerned about which investment asset should be used first, I recommend using the TFSA (up to the yearly maximum or until you have used all of your contribution room), followed by RRSP and then Other. It is always better to grow your assets tax-free, but there is a limit to how much RRSP you might want to have at retirement because you must take out a minimum each year and it is fully taxable. For other posts on this subject, click on these tags: RRSP or RRIF.
16) Start Investing at Age – For each column, enter the age at which you want to start investing in the asset. The age defaults to Your Age Now (line 1). Leave it empty if you want it to start at your age now, but the cell will become red to indicate that it is out of the range expected.
17) Percent of Salary to Invest by You – Enter a percent of the salary you will be putting aside for each asset. If desired, you can override the calculated amounts in section G by changing the Yearly value in any age.
18) Percent of Salary to Invest by Others – Enter a percent of the salary others will be paying into each asset. This should be used if your company is matching your amount. If desired, you can override the calculated amounts in section G by changing the Yearly value in any age.
19) Increase in Yearly Amount/Yr – Enter a percent that the sum of lines 18 and 19 will be increased each year. Normally this is left at 0%.
20) Yearly Interest Rate – Enter the interest rate, as a percent, you expect to earn in the future. If desired, you can override the calculated amounts in section G by changing the Interest % value in any age.
21) Amount Invested at Start Age – This is obtained from the appropriate using Yearly column in Section G. It gives you an indication of how much you are actually investing. Also examine the Total column. If it is more than you think you can afford, adjust the percent in line 17.
D) Investment Assets During Retirement – Control
22) Increase Withdrawals Per Year by – Enter a percent of how much withdrawals from assets are to increase each year. This is set to default to the Inflation rate of line 6 so that they match inflation. You might want to use 0% if you do not care about having this protection.
23) Target Age to be Gone by – You can set the withdrawal amount of lines 25 to 27 by entering an age that you no longer care if you have any of the assets left. Try a few ages and watch the charts, especially chart 3, to see if you have enough income during retirement to match your expenses. To evaluate the probability of surviving to the age you have set, please refer to Life Expectancy and Survival Probability. The age that you use is up to you depending on when you would like the capital to be exhausted. You do not have to make your capital go to zero if you have enough income to cover expected expenses. Anything left over will be an inheritance.
24) Chart Income less Expenses Line – Because it is difficult to determine how much Income and Expenses are different on Chart 3, an Income – Expenses line is shown on the chart. It uses the right axis rather than the left axis that all other items use. This cell is set to All Ages initially so that the right axis shows all values. If the withdrawals do not extend to the last age on the chart, the right axis may go from 0 to a very large negative number. To make it easier to see the details before assets are gone, enter the cell and select To Target from the menu to the right. The line will then truncate at the Gone by age and the right axis will span only a few thousand dollars. If the line is annoying or you want to see the incomes below it, select None and it will disappear.
E) Investment Assets During Retirement – Amounts
This section allows you to set the Interest rate and Withdrawal amounts per year for TFSA, RRSP, Other and Company Pensions. The columns are Capital at Retirement, Yearly Interest and Starting Withdrawal/Yr, It also includes the age the capital is gone, the Withdrawal rate and the withdrawal amount as calculated by a formula. The descriptions below are by column.
Yearly Interest for TFSA, RRIF and Other is set by default to the same values on line 20. Change if you want another interest rate. The Company value is the amount the pension will change each year. Many pensions are set to the inflation rate, but it would be prudent to leave it at 0% unless you know it will be different.
Starting Withdrawal/Yr – This is the most important value that you have to enter. It is set by default to the amount in the “By Formula” column. You can change this amount and watch the charts for the appropriate investment to see the affects. Also refer to the Gone by column that will show the age when the capital goes to 0. Any time you change other values, you may have to change the Withdrawal amounts. To reset the amount to the Formula value, enter the cell for the amount, enter the equal sign (=) and then click on the Formula cell in the same row and press enter. For example, for the TFSA, enter cell F32 and down the above will result in =J32 in the cell with the amount now equal to the formula amount.
Gone by Age – This age is taken from the tables in Section H. It has a value if the capital for the investment goes to 0. You can set the Withdrawal/Yr to show any age you want, based on when you no longer want any capital. The charts also show when the asset goes to 0.
Withdrawal Rate – This is calculated by dividing the Starting Withdrawal amount by the Capital at Retirement and shown as a percent. This column provides an actual withdrawal rate that you can then compare to the Safe Withdrawal Rate debate that continues on the internet. For example, many advisors recommend 4%. However, this number was decided on years ago when interest rates and investment returns were well over 8% per year. It also considered the exhaustion of capital to take up to 30 years. By using this spreadsheet and setting your own withdrawal rate based on the age you want capital to be depleted, you do not have to deal with this type of arbitrary “rule of thumb”.
29) Total Starting Amounts – This row shows the sum of the 3 rows of TFSA, RRIF and Other Investments for the capital at retirement and all 4 rows for the Withdrawals.
F) OAS, CPP and GIS Values Used for
This is in the upper-right of the window and contains the December 2018 values used in the tables for OAS, CPP and GIS calculations. They can be changed for future years by going to the proper Revenue Canada website for each value as indicated below.
30) Month and Year – For reference, enter the month and year of the values used in this section.
31) OAS Maximum Amount – The value for the year indicated above has been included. For the latest year’s value, refer to the following Government of Canada link: Old Age Security payment amounts. Multiply the Maximum monthly payment amount by 12 and enter the value into this line. The government changes this amount every 3 months.
32) CPP Maximum Amount – The value for the year indicated above has been included. For the latest year’s value, refer to the following Government of Canada link: Canada Pension Plan – How much could you receive. Multiply the Maximum monthly payment amount by 12 and enter the value into this line. The government changes this amount every year.
33) Upper income limit for GIS – The value for the year indicated above has been included. For the latest year’s value, use the same link as above, Old Age Security payment amounts scroll down to line 15 and the amount shown for “a single, widowed or divorced pensioner”. The government changes this amount every 3 months.
34) GIS Maximum Amount – The value for the year indicated above has been included. For the latest year’s value, use the line for line 15 and the amount shown for “a single, widowed or divorced pensioner”. Multiply the Maximum monthly payment amount by 12 and enter the value into this line. The government changes this amount every 3 months.
35) GIS 2011 Budget Amount – This is explained in the Replacing GIS Payment Tables with Equations post. The value increases by the same inflation rate as for the OAS and GIS every 3 months. The amount can be found by downloading the spreadsheet at this link. It will probably be updated every 3 months.
35) GIS 2011 Budget Reduction – This is explained in the Replacing GIS Payment Tables with Equations post. It is not clear if it increases each year by inflation, but this is what is done on this spreadsheet.
35) GIS 2016 Budget Amount – This is explained in the Replacing GIS Payment Tables with Equations post. The value increases every 3 months. The amount is not explicitly given at the 2011 Budget Amount link above but has to be calculated from the total top-up amount less the inflated 2011 budget.
There are 3 charts that summarize what is happening to your assets, the invested and withdrawal rates and the income during retirement. These will change each time there is a change in the value of one of the enterable cells.
Chart 1. Investment Assets by Age – For TFSA, RRIF and Other, these will increase each year until the Retirement Age. After that, they will decrease as withdrawals are made. The dashed Total line adds each of these assets.
Chart 2. Invested & Withdrawals/Year – Before retirement, the chart shows the amount invested. After retirement the chart is for the amount withdrawn. For the example as shown in the chart below, for TFSA, RRIF and Other, the amount invested will increase a little each year until the Retirement Age. After retirement (age 65 on the chart), they will increase significantly as withdrawals are made. The dashed Total line adds each of these lines together.
Chart 3. Yearly Retirement Income by Type and Expenses – For ages starting with the Retirement Age, income from government pensions, withdrawals form investment assets and company pensions are shown as stacked column graphs. This should be compared to the dashed black line of expenses.
The solid lime-green line shows the difference between Income and Expenses and can be adjusted using the choices indicated on line 24. The objective is to ensure that total income is greater than expenses and that the lime-green line has a value greater than 0 (the right axis).
Some things you can do if expenses are too high is to decrease them at line 5 or increase the % of Salary invested at line 17. You can also change the Target Age of line 23 or directly change the Withdrawal/Yr of lines 25 to 27. Change any of the other values until you get what you want and are comfortable with the risks involved.
G) Changes Before Retirement
This section contains a table of all the values used in the calculations and charts before retirement. You can change any of the cells that are outlined, but do not change any of the other ones or else the results will be wrong. If you want to return a changed cell back to the equations, copy a cell above it that was not charged and paste it into the changed cell.
H) Changes After Retirement
This section contains a table of all the values used in the calculations and charts after retirement. You can change any of the cells that are outlined, but do not change any of the other ones or else the results will be wrong.
I) RRIF Payout % by Age
This table is used to set the minimum withdrawal for RRIFs. For details, refer to RRIF Minimum Payout – 2015 Rates. Do not change any values.
J) Chart Values
These are used for the charts and the drop-down menu. Do not change any values.
K) Changes Before and After Retirement
To create charts 1 and 2 so that all ages are showing, this section collects data from sections G and H. Do not change any values.
The spreadsheet has its limitations as noted earlier. However, if you are not willing to try Lifetime Finances to obtain more accurate results, the spreadsheet will give you a good idea of what you can accomplish by putting away some of your salary each year. It can be used at any age, not just age 25 as per the example. However, If you have to deal with two people or want to see the impact of taxes, you should be using Lifetime Finances.
The results obtained by using Lifetime Finances are different compared to using the spreadsheet. Click on this link to view the comparison. In summary, for the example used with the spreadsheet, the financial advantages to using Lifetime Finances are as follows:
- By using the Tax Free Savings Account, the capital needed at retirement is significantly less by about 11%.
- The expenses after retirement are significantly greater by about 17%.