How much do you really need to retire? Are you one of the majority that either have a large number in mind or do not know? It may not be as bad as you think. What you need is an estimate of your expenses when you retire based on what you spend today, have an understanding of where your retirement income will come from and how you will use your savings. A spreadsheet is provided that will help you and it only takes a few minutes to fill in. You can try different values for capital, interest rates, inflation rate and withdrawal amounts.Note: This post has been replaced by Estimating Savings Required for Retirement which combines this post and its spreadsheet with Estimating Capital Totals at Retirement and its spreadsheet. 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. It directly relates your savings to your salary as a percent.
Note: Because this post and the spreadsheet are one of the most viewed and downloaded, they have been redone from the original ones dated December 29, 2015. They have been changed to use 2018 amounts and to remove the dependency on the years associated with the CPP pension.
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 examination of these various sources is provide in Understanding Your Canadian Sources Of Retirement Income which also provides a spreadsheet that you can use to calculate the first year of retirement.
However, it does not answer the question of how long your assets will last. The spreadsheet provided here takes this one step further and charts your income against expenses by year.
Estimating What Income You Need In Retirement
There are two ways to estimate what income you will need when you retire. The first way is to estimate the expenses you expect in retirement and then find a way to generate enough income. The second way is to estimate how much savings and income you will have when you start retirement, determine the withdrawal rate from your savings and then use this as the income you must be prepared to live on.
Neither way is very satisfactory when you really do not know what will happen in the future. Using a combination of the two ways provides you with an iterative method of coming to a reasonable result. If you have a plan, it will be much easier to deal with changes and to adjust.
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 Making a Yearly Budget. 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.
Estimating Your Retirement Capital
While you are working you should be saving. Excluding the forced Canada Pension Plan, you might have a company sponsored RRSP (Registered Retirement Savings Plan) where they will match savings up to a certain percentage of your wages. In addition, you might be saving in your own RRSP or TFSA (Tax Free Savings Account). You might also save in stocks, GICs, mutual funds, etc.
All of these can be used to generate an income when you retire. However, how much they generate is dependent on how much you can save, the interest rate and then how much you can withdraw without depleting your capital too soon.
You can estimate the capital you will have based on your savings rate per year using the spreadsheet provided in Estimating Capital Totals at Retirement. You can adjust the interest rate and decide when you want to start and stop investing in any of the three investment vehicles included (TFSA, RRSP, Other). You can choose the age (for example 65) you want to start withdrawals using the totals shown. You can then use these totals in the spreadsheet provided by this post and described below.
Estimating Your Income versus Expenses In Retirement
If you have estimated your retirement expenses and capital as indicated above, you are ready to compare your expenses against your retirement income to see if you have enough income. A spreadsheet is provided and discussed below where you can enter your retirement income, expenses and investment assets and it will chart them over your retirement years. You can make changes and try a few scenarios to see what the effect will be. (You can also use the spreadsheet without determining your retirement income and expense in detail. Just enter values to see what happens.)
The spreadsheet will chart your withdrawals and other income against your expenses. It will be obvious if you need more income or have to reduce your expenses. The following chart is for the example used below and shows how the total expenses (the dashed black line) is mostly less than the total income (the stacked columns) except starting at age 92 when the capital has been depleted.
Click on Download Spreadsheet and open it so that you can try out your own situation. The Excel spreadsheet is loaded with data for the example given below so that you can see the type of values to be used.
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 14), 5 lines (upper-right numbered 15 to 19) for entering the current year’s values for OAS, CPP and GIS limits, a table for entering Extra values for any year (starting at row 39), and 3 charts.
The spreadsheet takes your input to calculate the following incomes:
Government Pensions: Enter the values that you expect will apply when you retire. The values you enter are inflated each year during retirement.
- OAS – Old Age Security
- CPP – Canada Pension Plan
- GIS – Guaranteed Income Supplement for low income individuals. This is automatically calculated and no values can be entered for it.
Investment Assets: Enter the Capital that you expect to have at Retirement Age (or any other age you set in line 1), the Yearly Interest and Withdrawal rate per year.
- TFSA – Tax Free Savings Accounts
- RRIF – Registered Retirement Income Funds
- Other – Other Investments for any investments that are not included in the above. This will include stocks, bonds, GICs, mutual funds.
- Company – Company pension. There is no capital amount.
Please note that the spreadsheet can only handle one person. This is because the Canada Pension Plan, Old Age Security and Guaranteed Income Supplement are for individuals only. If you are a couple that share expenses, just open another spreadsheet for the other person and allocate the total expenses to match the income for each person. The only way to deal with a couple as a unit is to use FinanceBase-Lifetime Finances, which is discussed later.
You can also use this spreadsheet if you are already retired. Just enter all of the data as indicated below with the following additions: in the OAS and CPP Starting Amount cells (F11 and F12, respectively), enter the actual values you received this year.
The example is for a 40 year-old person who expects to retire at age 65. This same example is used in Estimating Capital Totals at Retirement. The person’s income now is $45,000 and, using the calculations detailed at the end of this post, a total of $22,000 is used for the retirement expenses (entered in line 5).
If you have downloaded the spreadsheet and opened it, you can make changes to suit your situation using the description that follows. It is loaded with the example described above and at the end of the post.
1) Your Age Now will be used to determine the inflated values at retirement for OAS, CPP and GIS using the values on lines 16 to 20.
2) Retirement Age will fill the table’s Age column A starting at row 39 with ages. If you enter 65, it will show ages up to 100. If you enter a greater age, there will be ages greater than 100 or if less than 65, the ages will be reduced accordingly. All ages in the table will show on the charts.
3) Use Inflation of – The value you enter will be used to inflate OAS, CPP, GIS and Expenses from Your Age Now to the retirement years and during retirement years. Try different values to see what happens to the charts.
4) Payouts at start of year –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. The impact of using the end of the year is normally an extra year or two of when the assets go to zero as shown on the charts.
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 3.
For the example, the ages are 40 for Your Age Now and 65 for Retirement Age, 2% Inflation (which is the Bank of Canada’s target), Payouts at the start of the year and expenses of $22,000.
6) OAS – Years of Residency when Retired – You must be a resident of Canada for at least 10 years to claim OAS. 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 15 (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 F11). This will replace the equation that is there. Note: The December 29, 2015 post and spreadsheet only permitted start ages of 65 to 67.
For the example, only 35 years of residence is used and the Start Age is set to 67.
Note: The December 29, 2015 post and spreadsheet had a row for entering the number of years to used in the calculation of the CPP pension. As documented in When to Take the CPP – What is Involved?, this is only used as one the conditions (the other being the drop-outs) before the calculation can be done. It is no longer used in this spreadsheet. All that is required is the % set in line 7 and the Start Age as indicated below.
7) CPP – % of Maximum Earnings & Start – The CPP 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 2015 maximum that is $53,600. 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. If outside this range, the Starting amount will be set to 0%. Otherwise, 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 16. The Post-Retirement Benefit that can be claimed when working past 60 is not included here.
For the example, the % of Maximum Earnings is set to 84% and the Start Age is set to 65. The Starting Amount shown is the product of 84% x CPP Maximum Amount from line 16.
8) GIS – 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 39, Column Y.
Investment Asset Values at Retirement
Change any of the values in the table for lines 10 to 13 to your own circumstances. The rows are TFSA, RRSP, Other and Company Pensions. The columns are Capital at Retirement, Yearly Interest, Starting Withdrawal/Yr and Starting Age. The description below is by column.
The Capital at Retirement for lines 10 (TFSA), 11 (RRIF) and 12 (Other) are taken from the example used in Estimating Capital Totals at Retirement, rounded to the nearest $1,000. Change to whatever values you want. There is no capital for Company Pensions (line 14).
For the example, the capital is $94K for TFSA, $149K for RRIF and $74K for Other for a total of $317K.
Yearly Interest (or Investment Rate) for TFSA, RRIF and Other are the same as those on the post mentioned above. 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 1%.
For the example, the Interest rates are 3% for TFSA, 3% for RRIF and 4% for Other.
Starting Withdrawal/Yr – This is the most important value that you have to enter. You enter an amount so that the capital goes to zero at some time in the future. Watch Chart 1 for the appropriate investment and see how the solid withdrawal line and the dash capital line change. To make it easy to set a yearly amount, also refer to the Gone By Age 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. 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.
For the example, the Starting Withdrawal amount is set to each have a Gone By Age of 92. That is, the last year with any capital is age 91. These values are $3,900 for TFSA, $6,200 for RRIF and $3,480 for Other. The company pension is $5,000 each year with an inflation of 1%.
Start Age – This age is used in the tables as the start of the Withdrawals. You can set this for each investment independently.
For the example they are all set to the retirement age of 60.
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”.
For the example, all withdrawal rates are above 4% as follows: 4.1% for TFSA, 4.2% for RRIF and 4.7% for Other.
Gone By Age – This age is taken from the tables below. 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 ages are the same as shown on Chart 1 for the dashed capital line.
For the example, as indicated above, the Starting Withdrawal rate has been set so that all assets are gone by age 92.
14) Total Starting Capital is the sum of the TFSA, RRIF and Other Investments capital at retirement.
For the example, the total starting capital is $317K.
OAS, CPP and GIS Values used in the tables below – for 2018
This is in the upper-right of the window and contains the 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.
15) 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.
16) 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.
17) Upper income limit for GIS – 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”.
18) 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.
19) GIS reduction/$ earned – Each dollar of income up to the maximum amount of line 18 is reduced by 50% at which amount the GIS is not available.
You can enter any amount of a withdrawal for TFSA, RRIF or Other Investments starting at Row 39, Column B, C and D respectively, for any year.
For the examples, no Extras have been entered.
Chart 1 – Withdrawal and Capital per Year shows each of the investments for each year. The colors match those of the entry area. The solid lines are the withdrawals and they use the left vertical axis. The dashed lines are the capital and they use the right vertical axis. If there is an inflation amount, the withdrawals will increase each year until capital is gone and then they will go to 0.
Chart 2 – Total Withdrawal and Capital per Year is the addition of the TFSA, RRIF and Other investments. Only 1 solid withdrawal line and 1 dashed capital line are shown. If the Gone By Age are different, there will be steps in the lines.
Chart 3 – Yearly Retirement Income By Type and Expenses takes all of the investments government pensions and shows them as a stacked column chart. The colors correspond to those of the entry area. Also shown are the inflated expenses as a dashed line. If the expenses line lies above the top of the stacked column, there is not enough income, If below, there is enough.
For the Example, there is a short fall for ages 66 and 67 until the OAS starts. After that there is an excess of income up to age 84. After that there is a growing shortfall that reaches almost $2,791 at age 91. You can see the Expenses less Income for each year by examining Column E starting at Row 39. A positive number means that there is not enough Income and a negative number indicates that there is more income than expenses. After age 91, the only income left is OAS, CPP, Company Pension and GIS which is only about half of what is required for the expenses.
Dealing with Shortfall Years
It is very easy to enter an Extra for any of TFSA, RRIF or Other to make up the approximately $3K for ages 65 and 66. However, this will cause the Gone By Age to be reduced from the 92 desired. If you cannot accept this, then the Withdrawal amount must be reduced to get it back to 92. This may have to be done a few times to get all values correct.
A much simpler way of getting the Income column line to exactly match the Expenses line is to select all of the Expenses-Income column E starting at row 39 down to row 63 (which is age 89) and copy the selected area. Then, select one of the Extra columns at row 39 (i.e. B39, C39 or D39) and use the Edit menu’s Paste Special item to select Values. This will cause all values in the selected area to go to 0 and all of the previous values to be included in the Extra area.
For the example, this works because the sum of all ages that had excess income is greater than those ages that had more expenses. The copy and paste was done to the Other Extra column because this probably includes chequing or savings accounts making it easy to make withdrawls and deposits. As shown in the following charts, about $6.5K of total withdrawls are required in ages 65 and 66. After that there are deposits of the excess withdrawals from the other assets for a few years and then withdrawals. Because of this, the Other Capital does not go to 0 and still has over $110K at age 100. Additional withdrawals from Other can be done in later years if additional funds are needed.
Spreadsheet Shortcomings and an Alternative
The spreadsheets provided in this post and the one for Estimating Capital Totals at Retirement are the best that I can do with Excel. It does 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.
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.
To determine how much income should be used when retired, the following logic is used:
1) A total savings rate of 7% ($3,150) per year of the income was used. This may be a little high for many people, but is lower than what is often recommended (10%). The savings is broken down as follows:
- • 3% or $1,350 is saved to a TFSA.
- • 3% is used for RRSP with 3% matched by the company for a total of $2,700 for the first year.
- • 1% or $450 is saved to Other accounts. Normally this would be added to a TFSA to ensure that there are no taxes, but is used here to have amounts in all 3 methods of savings.
2) The average effective tax rate for this person as given in Impact of Tax Credits on Average Tax Rate is about 12% or $5,400.
3) Using the CPP deductions of 4.95% and Employment Insurance of 1.88% means that the take-home pay is reduced by another 6.83% or a little more than $3,000.
4) This results in deductions of about $11,550 (=1,350+1,350+450+5,400+3,000).
5) This leaves a disposable income of $33,450. Assuming it all goes to expenses, and using a 60% of this for expenses in retirement means that about $20,000 is required now for retirement. Taxes on this amount will be about 10% or $2,000. The total of $22,000 is then used for the retirement expenses as indicated below.
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.