Friday, 24 March 2017

Retirement Planning and Forecasting Spreadsheet 2.0 (for Canada)


Retirement Forecaster 2.7

Updated to version 2.7 on March 24th, 2017. 

In this version I have updated the tax tables to include the 2017 federal and provincial tax brackets and rates. There was nothing in the March 22nd budget that affected the calculations.

Download new version here.  

A complete list of upgrades by version is included in the Instructions sheet in the workbook.

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 forecast 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 a taxable investment 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 expenses, the spreadsheet calculates taxes and the difference is savings which is placed into your accounts.  
  • The spreadsheet calculates the growth in your various savings accounts (RRSP, LIRA, TFSA and non-tax free account) 
  • You set your expenses (income required) during retirement 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 expenses amounts, 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.
  • If you want to use the function where the estate of Person1 passes to Person2 then Person1 should pre-decease Person2.
  • To set the Person2 to inactive set the on/off switch 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 income for a given after tax income.  
  • In the cells to the right of the yellow input cells there is a description of each input.
  • 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 in the "Annual Inputs" section. A description of each of these inputs is contained in the first row above each column. Note these are in current $.
How The Spreadsheet Works
  • During the employment phase:
    • Employment income is specified and can be grown at a different rate other than CPI.
    • Expenses during employment is specified, taxes are calculated and the remainder (Income - Taxes - Expenses) is saved. 
    • Income tax due on interest and dividend income in the Investment account is also calculated as part of tax payable.
    • Income tax is calculated based on your province of residence.
    • The amount saved is put to your RRSP, TFSA and Investment accounts depending on room and settings you specify..  
    • DC pension contribution percentage is specified and put into a LIRA account.   
    • Net Income after tax and Net Income after tax and savings are shown on the plots.
  • During the retirement phase:
    • The amount of DB pension, CPP and OAS is calculated first.  
    • OAS clawback is calculated based on previous years income.  
    • Expenses (required 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 to meet expenses 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 result in more income than expenses 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, including RRSP and TFSA amounts as a survivor.
  • 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 Income, DB pension plan Pension Amount and and DC pension plan contributions, calculates RRSP room each year.  
    • RRSP limits, Tax brackets and Non refundable tax credit amounts are indexed to CPI.
    • TFSA limits are no longer indexed to CPI.
    • The spreadsheet automatically puts savings into the RRSP, TFSA and investment accounts.  
    • 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.
      • Income tax payable on income from the account is payable from employment income 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.
    • Can split DB pension with spouse and specify survivor benefit amount.
    • Accurate income tax model.  
    • On death of Person1, the funds can be transferred to Person 2 assuming as a beneficiary.  Investments are sold at market, income tax paid and funds transferred 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 benefit amount.  
    Compromises
    • OAS clawback based on previous years income.
    • Same investment growth % for all accounts.
    • Pension tax credit of between $1000 to $2000 not included.
    • 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
    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.


    In the Person1 and Person2 sheets you can enter some values for individual years, such as one time expenses or extra income or gifts. 



    On the summary page some results are provided such as whether you run out of funds in retirement and what your account balance and estate values will be at your life expectancy. 



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


    The balance in accounts is shown over time.  




    27 comments:

    1. Excellent work; I will use this to plan for our retirement.

      ReplyDelete
    2. This Blog and your spreadsheets look to be very useful to me as I am recently retired at 56 and must now decide how to draw done income from my cash, TFSA, RRSP and LIRA accounts.

      Many thanks :)

      ReplyDelete
    3. Unable to run in numbers or Libra. Quess that I will need MS office

      ReplyDelete
      Replies
      1. There is a macro in the spreadsheet that is used to determine the before tax withdrawals to achieve an after tax income (during retirement). Other spreadsheet tools won't run the visual basic macro. The macro is needed as this is an iterative process - you don't know what the tax rate will be until you determine from what sources the income is coming from.

        Delete
    4. Thank you for the update!

      ReplyDelete
    5. Fantastic spreadsheet ... possible future enhancements (if you get bored) :)

      1) The ability to update the existing spreadsheet(s) yearly with actuals, plus the new tax rate information for coming years? ... would make this a great tool for ongoing planning.

      2) The ability to press a button and "buy you a beer" for providing such a useful tool :) Thanks again.

      ReplyDelete
      Replies
      1. There is no facility in the spreadsheet to track actuals. What I do is plot my current and all previous forecasts against actuals (of total account value) to see how the acutuals are doing against previous forecasts. I plan on doing a blog post on this soon.

        Delete
    6. Hi Thanks so much for offering this spreadsheet to others. One quick question. My pension has a higher payout until 60 and then drops down. Then another drop at 65. Is there any way to incorporate the different payouts into the spreadsheet?

      Thanks so much

      ReplyDelete
      Replies
      1. Herb, in the Person1 sheet you can put income values in for each year. Put your post 65 pension amount in the DB pension amount in the Inputs sheet. Put annual income values in the Person1 sheet to increase the income pre 60 and pre 65. You can also email me questions at Steven.brown.111@hotmail.com

        Delete
      2. perfect! Of course! Now why didn't I see that. Much much appreciated!

        Delete
    7. This is excellent, thanks very much for producing this. What inputs would I make to minimize my (and my spouse's) total taxes paid after retirement?

      ReplyDelete
      Replies
      1. I just answered my own question. I added a SUM field at the bottom of the Income Tax column on the BOTH tab. I followed your example of changing inputs to simulate different final estate values...and even though the estate value was highest with a lower initial RRSP withdrawal, the total amount of tax paid over retirement was almost 20% lower by taking larger RRSP withdrawals (~$50K each) early and waiting until later to dip into my non-registered (tax already paid) investments and TFSAs.

        Delete
      2. I'm certainly not a tax expert, so I won't make any specific recommendations to you. You can use the spreadsheet to test different strategies. I use the "Estate Value", and trying to maximize that value, to determine which strategies are best. It is similar to minimizing taxes paid. The types of things you may want to look at are 1) should I keep contributing to TFSA after I retire, 2) How much should I withdraw from RRSP vs TFSA vs Taxable account, 3) Should my taxable account focus on dividend generating income vs capital gains generation. See my blog post on RRSP withdrawal strategies for examples on how to use the spreadsheet to make these types of decisions. http://pabroon.blogspot.ca/2015/08/financial-studies-1-rrsp-withdrawal.html

        Delete
      3. What you did by summing the column is the correct way to do what you wanted to do. In my earlier reply I misunderstood your question.

        Delete
      4. You would have to ask yourself if you want to pay less total tax or have more money left at the end. These are not inconsistent. The higher early RRSP withdrawals do reduce total tax paid, but since the RRSP grows tax free, taking it out early reduces the overall growth which leaves you with less money at the end.

        Delete
    8. This comment has been removed by the author.

      ReplyDelete
    9. Hi Steven, great worksheet! Quick question about Version 2p6, Inputs_Summary!D29 - the text in B29 describes it as "Additional Widowed _Income_" but the text in E29 says "Additional _expenses_ for Person2 after Person1 is deceased." This seems contradictory to me -- how is this number to be used? Is it Income or Expense?

      ReplyDelete
      Replies
      1. Good catch on the wording. It should say expenses in cell B29. The spreadsheet makes the income equal the expenses, if an income source is available, so sometimes I forget about the distinction. I will fix this in the next version.

        Delete
    10. This is PHENOMENAL! Great timing for my husband and I to work through. The only improvement I could think of is to be able to indicate a lower income in the future. For example, I am 57. At age 60, I would like to start working half-time, thereby reducing my income until age 65.

      ReplyDelete
      Replies
      1. In the Person1 and Person2 sheets you can enter some data by year. To reduce your income for the 5 years from 60 to 65, put a negative value in column F (Other Income) for those years.

        Delete
      2. Great, thanks. I will try that! I am just so impressed by all your work!

        Delete
      3. Not sure if my last post worked but I just wanted to thank you for the response.

        Delete
    11. Thank you very much! Great job!
      tdw

      ReplyDelete
    12. This is great but I really wish you could input the exact amount currently being invested in RRSPs and TFSAs (and other investments). Having it default to any savings (income less expenses) going into RRSPs, TFSAs and Savings Investments isn't, imo, realistic for many. Often, Savings/Income get spent on more frivolous Expenses that seem to make themselves available but if you budget a certain amount for RRSPs, TFSAs and/or Investments you can set yourself up to make adjustments to better meet your goals.

      Perhaps I am overlooking something?

      Thanks, Dan

      ReplyDelete
      Replies
      1. Hi Dan,
        If you are interested I can add this ability to the spreadsheet. I would make a switch that you could change so that you could specify the amount saved instead of the expenses. Won't be too difficult. Send me an email at the address in the Contact section above and I will let you know when it is ready, otherwise I can post here when it is done.
        Steve

        Delete
      2. Thank you Steve! Email sent.

        Delete