Retirement Planning and Forecasting

Retirement Planning and Forecast Spreadsheet (for Canada)

Note that this version of the spreadsheet is now out of date. Go to my post on the new version here.

Updated to version 1.12 on May 7th, 2015 for update to TFSA limit and RRIF minimum withdrawal percentages. Download new version here.  See bottom of post for a list of enhancements and fixes. 

This spreadsheet allows you to make an accurate financial plan for your retirement, starting at any time in your life.  Unlike many retirement tools, this spreadsheet shows a cash flow forecasts vs time and identifies which account money is saved into (during employment) and which account or source the retirement income is withdrawn from.  It handles the different account types (RRSP, LIRA, TFSA and non-tax free account), the limits imposed on them and does income tax calculations for any of the provinces.  

The picture below shows the main page of the spreadsheet which contains all the inputs you need (in yellow) and provides a cash flow forecast and how the savings accounts increase and decline.  

I hope you find the tool useful and I am always willing to hear of suggestions to improve it. 

What it does

• It allows you to plan for you retirement, starting at any time in your life.
• Based on your income and what percentage you plan on saving, the spreadsheet calculates the growth in your various savings accounts (RRSP, LIRA, TFSA and non-tax free account) 
• You set a target income you want when you retire and the spreadsheet withdraws the amounts from the accounts to give you the after tax income you desired.  
• It accounts for a DB pension you may have, CPP and OAS payments and does tax calculations for all the provinces.  
• The way the spreadsheet is structured, it allows you to easily try many different scenarios such as different retirement ages, different savings percentages, and which account to withdraw money from first.  
• The results of the spreadsheet is a detailed income forecast and from what source, or account, the income is sourced.  The spreadsheet also shows the balance in your savings, investment and retirement accounts vs time.  

How To Use The Spreadsheet

• Download the spreadsheet from the link below.  
• When you open the spreadsheet make sure to enable macros. There is a function macro and also a macro to calculate the tax rates iteratively.
• This spreadsheet can handle the retirement planning for a couple.
• All the inputs and a summary of the results are shown on the "Inputs_Summary" sheet. 
• Enter the particulars for one person under the "Person 1" column and a second person (spouse) under the "Person 2" column.
• Person1 should pre-decease Person2 if you want to use the function where the estate of Person1 passes to Person2.  
• To set the Person2 to inactive set the on/off swith next to the start year to Off.
• After you change any inputs, click the "Iterate Tax Rates" button on the Inputs_Summary sheet.  This process iterates to find the required before tax retirement income for a given required after tax income. 
• In the cells to the right of the yellow input cells there is a description of each input.
• If you need to enter one-time (non-recurring) income, gifts or expenses, you can enter these on the Person1 or Person2 sheets in the section titled "Annual Inputs".  A summary total of what you have entered here is shown in the Summary section of the "Inputs_Summary" sheet.  
• On the "Inputs_Summary" sheet it gives a summary of results at the top, including whether you can meet your retirement income goals and how much money you have left.
• Also on the "Input_Summary" sheet, charts of income and account balances are shown for Person 1 and either Person 2 or Both people (summed together).
• The charts at the bottom of the "Inputs_Summary" sheet can be changed from Person 2 to Both by changing the yellow cell just above these charts.  
• On the Person1 and Person2 sheets you can enter some values annually. The description of these inputs is contained in the first row above each column. 

How The Spreadsheet Works

• During the employment phase:
• Employment income is specified and can be grown at a different rate than CPI.
• A savings percentage is specified and the amount to be saved is put to your RRSP, TFSA and Investment accounts.  
• DC pension contribution percentage is specified and put into a LIRA account.   
• Income tax due on interest and dividend income in the Investment account is funded by negative contributions to the Investment account.  This allows the net income to still be accurately represented.  
• Income tax is calculated based on your province of residence and your net income is calculated after subtracting savings.  
• Since the net income is after savings, then you can compare this to the net income during retirement if you will maintain the same lifestyle.  
• During the retirement phase:
• The amount of DB pension, CPP and OAS is calculated first.  
• OAS clawback is calculated based on previous years income.  
• Required net income is specified and the gross income is estimated from an initial guess of effective tax rate.  
• Any amount above DB, CPP and OAS that is required is withdrawn from the savings accounts (RRSP, TFSA, LIRA and Investment).  
• Weightings for withdrawal from the 4 accounts can be changed to prefer one account over the other.  
• RRSP/RRIF and LIRA/LIF withdrawals are calculated first.  The mandated minimum and maximum withdrawals are imposed.  If the minimum wthdrawals are greater than what you require then the extra will go to TFSA or Investment accounts.  
• If additional income is still required, then withdrawals from the Investment account and then the TFSA account will be made.  
• If you have chosen to fund TFSA after retirement, then additional money will be withdrawn from the Investment account to put to the TFSA account.  
• Income tax calculation is done next.  The "Iterate Tax Rate" macro button copies this calculated tax rate back to the initial guess of the effective tax rate.  The macro does this iteration at least 5 times.  
• You may pass the estate from Person1 to Person2.
• Sheets
• Inputs_Summary: All inputs and basic summary plots are here.  
• Person1 and Person2 sheets is where the calculation is performed.  These sheets are almost identical.
• For the calculation columns there is a description on the first row of what the column is and how it is calculated.  
• The Both sheet sums up the results for the two individuals.  
• Charts
• The P1_Inc chart shows the income for Person1 as a stacked line for all possible sources of income.  The Net income, after income taxes and money to savings, is shown as a green line. 
• The P1_Accts shows the balance of the 4 types of savings accounts as a stacked line chart. 
• The Both charts are for Person1 and Person2 summed together.  

Key Features

• The summary section and the plots can be shown in actual $ or in current $.  
• You can specify any of the 13 provinces or territories for calculating income tax.  
• From DB pension plan Pension Amount and and DC pension plan contributions, calculates RRSP room each year.  
• RRSP limits, TFSA limits, Tax brackets and Non refundable tax credit amounts are indexed to CPI.
• From the specified savings amount (income times percentage savings) the spreadsheet automatically puts savings into the RRSP, TFSA and investment accounts. 
• If you specify a mortgage amount, this is subtracted from the available savings until the mortgage is paid off (then savings increase)
• You can specify a % of RRSP contributions that go to your Spouse.
• Saving into TFSA and RRSP accounts can be turned off (if you want to determine if these are actually useful)
• DC pension contributions are put into the LIRA account.  
• For the Investment account:
• You can specify what percentage of the growth in the account is capital gains and what percentage is dividend income, the remainder is interest income.  
• Handles the tax treatment differences for interest income, capital gains and dividends.
• Any income tax payable on income from the account is debited from contributions to the account during employment and paid from withdrawals from the account when retired.  
• You can convert a portion of a LIRA to RRSP upon retirement.  Legislation allows 50% in Alberta if at the same time the LIRA is also converted to a LIF.  
• You can convert a LIRA to a LIF at any time between 50 and 71.  The minimum and maximum LIF withdrawal amounts are imposed by the spreadsheet.  
• You can convert an RRSP to an RRIF any time before 71.  The minimum withdrawals rates are imposed.  
• Withdrawals from each of the 4 different accounts can be changed by changing the account withdrawal weightings.  Normally these are set to 1, but if you want the RRSP withdrawn first set the RRSP weighting higher (maybe 2 or 10).
• You can continue to contribute to a TFSA (from a taxable savings account) after retirement.  
• Can specify DB pension amounts, how it is indexed and clawed back for CPP or OAS amounts.
• Can split DB pension with spouse and specify survivor benefit amount.
• Accurate income tax model.  
• On death of Person1, the funds can be tranfered to Person 2 assuming as a beneficiary.  Investments are sold at market, income tax paid and funds tranfered to Person2.  RRSP, LIRA and TFSA accounts are transferred.  
• Calculates estate values.   Investments are sold at market, income tax paid.  RRSP and LIRA are cashed.  TFSA is non-taxable.  Estate value is stated after tax paid.  
• Can specify CPP survivor benefitr amount.  

Compromises

• OAS clawback based on previous years income (to eliminate circular references).
• Same investment growth % for all accounts.
• During employment the tax owing on interest income in the investment account is funded by negative withdrawals.  This isn't quite right as there may be capital gains in the redemptions to pay the taxes. 
• Pension tax credit of between $1000 to $2000 not included.
• EI and CPP are not deducted and therefore are not considered a tax credit.
• Tax calculation does not include special provincial tax amounts (ON health premium, NT/NU refundable cost of living).
• Tax calculation does not include Federal and YT employment amount for NRTC.

Download

Version 1.12 is the latest.  


Upgrades for version 1.12

• Added ability to change asset mix in Investment account through time. Change rate of return, % of return that is capital gains and dividends by year.  This is done on Person1 and Person2 sheets.
• Added ability to specify 1) a Taxable benefit, 2) Additional Federal Tax Credits and 3) Additional Provincial tax credits by year.  This is done on Person1 and Person2 sheets.
• This facility can be used to model special dividend income, both canadian eligible and non-eligible. Contact the author for details. 
• Updates for 2015 Federal budget.
• Updated RRIF minimum withdrawal table.
• Updated TFSA contribution limit to $10,000 from 2015 forward.
• RRSP contribution limit is a function of just employment income, and now does not include other income.

Upgrades for version 1.11

• 2015 Income tax rates and credits.  

Upgrades for version 1.10

• Added Net Income line to the Income charts (grey line).  The green line is the same as in previous versions and is Net Income less Savings contributions.
• Re-ordered the columns and calculations in the Savings section to better facilitate a conversion to be able to handle US calculations.

Upgrades for version 1.9

• Updated OAS and CPP amounts in comments.
• Updated OAS clawback threshold income to 2014 value.
• Updated comments for OAS to estimate benefit.  
• Allowed input of unused contribution room from previous years for both RRSP and TFSA.
• Updated the listing of compromises

Upgrades for version 1.8

• Added the capability to turn Person2 off with a single on/off switch setting.  
• Updated tax tables, NRTC amounts, dividend tax credits, dividend gross up, RRSP and TFSA amounts for 2014.

Upgrades for version 1.7

• Updated descriptions for many of the inputs.  
• TFSA yearly limit is now rounded to nearest $500.
• RRSP limit rounded to nearest $10.
• TFSA unused room now includes  amounts that were withdrawn from TFSA in previous years.
• You may now change the order of RRSP or TFSA savings.  If you don't save enough to fund both, this allows you to do a sensitivity of which one is best.  
• Can make a spousal RRSP contribution.  This is done by specifying what % of your RRSP contribution goes to the spouse's RRSP.
• Allow designation of a mortgage payment for a specific period of time.  The mortgage is deducted from the savings amounts (income x savings %).  Once the mortgage payment stops, then that amount become available for savings.  

Screenshots

All inputs and summary of results are on one page.

 All the inputs are located in a single section of this summary sheet.

Each of the inputs has a detailed description of what it is and what typical values might be.

The spreadsheet calculates an income forecast during employment and then during retirement.

The balance in accounts is shown over time.