Comparing Savings Spreadsheet with Lifetime Finances


A previous post provided a spreadsheet for estimating the savings required for retirement. When compared to using FinanceBase-Lifetime Finances which takes into account income taxes, for the example provided, Lifetime Finances requires less savings and has provides higher after retirement expenses for the same age when assets go to zero.

The spreadsheet that can be downloaded from the Estimating Savings Required for Retirement post uses charts to show what happens to selected investment assets from your age today to any age up to 99. You can specify your salary now and in the future, expenses now and after retirement, inflation and interest rates, what you want to invest yearly in 3 asset types (as a percent of your salary) and how much you want to withdraw each year when retired. The spreadsheet makes it easy to adjust these values to ensure that you have enough income (including government pensions) to match your expenses to a specified age. As noted in the post, it does not include income taxes. This has an affect on which asset is the best to use.

Also mentioned in the above post was the more comprehensive FinanceBase-Lifetime Finances application. Compared to the spreadsheet, it does 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.  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.

This post compares the results using Lifetime Finances to the spreadsheet with emphasis on the impact of taxes and which investment is the best. It includes the tasks that were done to make Lifetime Finances match the spreadsheet and the options it provides to improve the results.

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%.

If you want a more accurate estimate with a lot more flexibility, I recommend that you download Lifetime Finances and see what it can do. Try as a Demo for 31 days and if it suits your needs, order a License.

Configuring Lifetime Finances for the Example

Lifetime Finances can be configured to match the example used on the spreadsheet. If you want to duplicate the results, do the following:

Entry page

  • – Select Country as Canada and Ontario as the Province to get the same results as presented here.
  • – Set the Year to 2019 which uses the 2018 tax information.
  • – Enter the Year of Birth as 1994 so that the charts will start at age 25.
  • – Enter expenses Now – All Other as 49,618 which is the salary of $55,000 less the $5,500 savings given below plus $118 to ensure that all income at the end of the year is matched with expenses.
  • – Set Retired – All Other expenses as $29,900, which is what is used on the spreadsheet.
  • – Set all inflation rates to 2.20.
  • – Enter CPP Start Age of 65 and $/Year at the maximum of 13,610.
  • – Set the OAS Start Age to 65 and the $/Year to 40 years which is 7,210.
  • Enter Salaries $Amount = 55,000 and $Inc = 2.2.
  • – Set all Assets %Rate to 3%.
  • – Set the Number of Retirement years to 32 so that the charts stop at age 96.

Here a screenshot of the Entry page.

Assets Per Year Page

  • – Select this from the Page drop-down menu at the top of the window.
  • – Then click on the age 25 line of the TFSA column.
  • – In the window that appears, enter 2750 for the value, 64 for the age, 2.2 for the increase and click on the “Use as a Percent” check box. as shown in the window below. The 2750 is from the spreadsheet which is 5% of the $55,000 salary for the first year and the inflation rate is 2.2%.
  • – Repeat for the Cash column with 1100, 64 and 2.2
  • – Repeat for the RRSP column with 1650, 64 and 2.2, with the “Use as a Percent” check box on.

Taxes Page – Select this from the Page drop-down menu at the top of the window. Near the bottom, click on the “Exclude Taxes” check box.

Comparison of Results

Once all of the above is done, we now have a result that compares with the spreadsheet because it does not include taxes as below:

  • – It has the same total assets of $600K at the end of age 64.
  • – However, there is $68K of assets left at age 94.
  • – This is due to how Lifetime Finances uses assets after retirement compared to the spreadsheet. The spreadsheet takes the amount specified in section E for each year for each asset. Lifetime Finances takes from assets that reduce the taxes as well as increase the possibility of receive a Guaranteed Income Supplement (GIS).
  • – In particular, is takes from the Cash asset from age 65 to 76 until gone and then takes from the TFSA asset for another 10 years before using up the remaining RRIF. It also does not take any mandatory RRIF payment until age 72.
  • – This results in receiving GIS payments from ages 64 to 94. (This contrasts to the spreadsheet where GIS only is received after age 74 and later and is much less than in Lifetime Finances.)

Including Income Taxes

The strength of Lifetime Finances is that it includes key income tax items. To adjust the above example to do this, go to the Taxes page and click off the “Exclude Taxes” checkbox. This results in taxes at age 25 of $10,180 and at age 65 of $6,276. Chart 2 of the Charts Window now shows nothing but Shorts. This is easily solved by reducing the Expenses on the Entry page by the taxes as follows: Now-All Other from $49,618 to $39,438 and Retired-All Other from $29,900 to $23,633.

This produced in an unexpected result:

  • – The total assets at the end of age 64 went down to $544K from $600K but there is over $343K of assets left at age 94.
  • – This means that the Retired-All Other Expenses can be increased to $25,820 to force the assets down to zero at age 94. This is an increase of $2,187 (or 9.2%) and the income taxes are paid!

Let Lifetime Finances Do the Work

However, there is a better way to use Lifetime Finances: let it automatically assign assets during the years before retirement and take from assets after retirement so as to reduce the income taxes.

This is done by using the Defaults on the Calculation page and making sure that the % values are set to 100% for the TFSA when adding. Also, there cannot be any Per Year assets or else it defeats the above. To remove them, on the Assets Per Year page, click on the age 25 year cell in the TFSA Change column and, in the Request window, click on the “Fill Down All Ages” checkbox and click on the OK button. Do the same for Cash and RRSP. This will clear all entries in the Change columns and remove the amounts from the Entry page.

The impact is really significant. The application assigned all excess income not needed for expenses to the TFSA.

  • – This resulted in $5,724 at age 25 being added to the TFSA going to $9,153 at age 64.
  • – The total assets at age 64 have dropped from $600K to $534K or 11% less.
  • – However, the total assets at age 94 has increase a lot and are now at $324K.
  • – This means that the Retired-Other Expenses can be increased to $27,800.
  • – Compared to the spreadsheet, the Retirement expenses after taxes can be increased by more than $4,000 per year at age 65 (from $23,633 to $27,800) or 17.6% more.

This shows the advantage of using Lifetime Finances compared to the spreadsheet. By letting it do the assignment to assets, the same percent of the salary can be saved, and the retirement expenses can be increased by about 15%. This is possible because the TFSA is used exclusively with its income tax advantage and there is GIS income after age 65 because the taxable income is low enough.

The following are a few of charts available in Lifetime Finances for the final result using TFSA. Notice the Shorts in Chart 2 starting at age 94 (as per the plan) and comment 4) below it.

You might ask if the same is true with the spreadsheet. I tried it with the full 10% assigned to TFSA. The Expenses at Retirement can be increased from $29,900 to $31,650 but the taxes are still about $6,200 which means that the actual expenses that can be supported are about $25,450. This is still about $2.4K less than that obtained with Lifetime Finances.