How do I show and hide the advanced worksheets?

The advanced worksheets are hidden by default to make the spreadsheet less intimidating for new users. To show and hide the advanced worksheets use the Simple / Advanced menu item in V26.0 and beyond. For previous versions, use the Show and Hide buttons on the Start here Worksheet.

How do I account for income after retirement?

Many retirees have an income earning hobby or do part time work after retirement. Your retirement date on the Dashboard controls when your salary or self employment income stop and your RRSP contributions change to withdrawals.

Enter any part time income past retirement in the 1 Advanced (for you) or 2 Advanced (for your spouse) worksheet in the Salary & Bonus->Employer or Self-Employed columns. Enter an amount for every year where it applies and convert to future dollars using the value from the Inflation Multiplier column on the left of the worksheet.

How do I account for one time expenses?

One time payments are for special expenses like a large gift to children, replacing your roof, buying a new car and a once in a lifetime trip. Enter the expense in the appropriate year in the 1 Advanced or 2 Advanced worksheet under Other Liabilities and Payments excl Mortgage->One Time Pmts column. The expense should be converted to future dollars by multiplying by the value from the Inflation Multiplier column on the left of the worksheet.

Is there a way to minimize taxes?

Everyone wants to pay less tax, but the ultimate goal is more money in your pocket. The spreadsheet's optimizer takes a lifetime approach to maximizing the value of your estate after all taxes, including final estate taxes, are paid. This will likely have the side effect of minimizing taxes, but the focus is on maximizing estate value, not minimizing taxes.

Is there a way to see total tax paid over the entire projection?

Similar to above, the spreadsheet focuses on maximizing your estate rather than minimizing taxes. Simply looking at the total tax paid over the entire projection ignores a number of other factors including the time value of money and how money is deployed to accounts.

How can I avoid the OAS clawback?

Similar to above, the spreadsheet focuses on maximizing your estate rather than minimizing taxes. Sometimes it is impossible to avoid OAS clawback and in other cases it is better from an overall tax perspective to pay some OAS clawback. This is all taken into account by the optimizer.

Can I control which accounts the spreadsheet deposits to and withdraws from?

The spreadsheet attempts to withdraw and deposit funds in the most tax efficient way possible. 

The drawdown of accounts is done in an attempt to minimize your tax burden. The order of drawdown is as follows:

1. RRIF and LIF minimums (required by law)

2. Discretionary RRSP/RRIF and LIF withdrawals as dictated by the withdrawal strategy and suggested amounts you (or the optimizers) have selected

3. Savings in excess of your emergency fund

4. Non-registered investments

5. TFSA

6. Your emergency fund

7. Overdraft (you have gone into debt)

Excess discretionary funds (beyond your budget, charitable donations, taxes and loan payments) are allocated in the following order

1. Repaying any overdraft

2. Paying down any consumer debt

3. Making your RRSP contribution as dictated by the contribution strategy and suggested amounts you (or the optimizers) have selected

4. Establishing or topping up your emergency fund

5. Making your TFSA contribution

6. Deposits to your non-registered account

You cannot change this order but you can influence the amounts by specifying the RRSP Contribution Strategy and Target and Retirement Fund Withdrawal Strategy and Target.

Excess funds are allocated in  an attempt to equalize Net Worth between spouses. The spreadsheet may limit the amount deposited to a spouse's non-registered account to avoid potential income attribution.

On the Snapshot Worksheet, why are the tax amounts not the same?

To understand this, you must familiarize yourself with three concepts:

1. Prepaid tax is tax paid through payroll deductions, withholding tax when you make an RRSP withdrawal and instalment payments CRA may require you to make.

2. Assessed tax is the total tax due for a given tax year and is calculated on the tax return you file in April of the following year.

3. Reconciliation is the difference between your Assessed tax and Prepaid tax for a given year. It is also calculated on your tax return and results in additional tax owing or a refund.

From a cashflow perspective in any given year, you pay Prepaid Tax for that year and you receive or pay a Reconciliation for the previous year. The total of these two is shown in the Expenses column in the Income and Expenses section.

Your estimated Assessed tax for the current year is shown at the bottom of the Income and Expenses section. Some of this will be paid as Prepaid tax in the current year and some in the following year when you file your tax return.

The spreadsheet uses your Assessed tax from the previous year as an estimate for Prepaid tax in the current year. This is usually a good estimate except when your tax situation changes significantly, for example when retiring or selling an investment property with a large gain. From an overall projection perspective, this is not too significant because the spreadsheet will sort out the taxes within a year or two.

The spreadsheet is not a substitute for a tax program or accountant. Only the most common tax credits are calculated and your tax situation may be significantly different.

Why are my RRSP/RRIF withdrawals so high in the early years of my projection?

It may be tempting to leave funds in your RRSP/RRIF to grow tax free, however, that money must be withdrawn at some point - either as RRIF minimum withdrawals - starting no later than age 72 - or worse - the entire amount at once when the single person or last surviving spouse passes away. In the estate case, a large RRSP/RRIF balance can push the taxpayer's income into the highest tax bracket resulting in about half of the RRSP/RRIF being paid in tax.

The estate optimizer attempts to draw down your RRSP/RRIF balances to zero before death to avoid this large tax hit.

If the total return for your non-registered account is higher than your RRSP/RRIF, the optimizer may determine that it is more advantageous from an Estate perspective to draw the funds out from your RRSP/RRIF early because the tax deferred growth is offset by the better returns in your non-registered account. Experiment by adjusting the Withdrawal Strategy Target amount and see if you can improve on the Estate value in the Projection Summary.

Account returns can have a significant effect on the draw down pattern. Try setting the total return for all accounts (except savings) to be the same and re-optimize to see if the drawdown pattern changes.

Should bonds and GICs be included in the ACB total for non-registered accounts?

The adjusted cost base (ACB) is sometimes called Book Value and it represents the total you paid for your investments. Usually your financial institution will track this for you and you can find the total ACB/Book Value for each account on your investment statements or online. It is used to calculate capital gains or losses when you sell your investments.

Bonds also have an ACB/Book Value. Let's say you purchase a 5 year bond giving 5% annual interest for its face value of $1,000. Your ACB is $1,000, you collect interest of 5% per year (and pay tax on that) and at the end of 5 years your bond is redeemed for $1,000. In this case there is no capital gain or loss.

The gain or loss comes in when you buy and/or sell a bond during its term. Take the earlier example: a 5 year bond, $1,000 face value, 5% annual interest or $50/year. Let's say after one year the prevailing interest rate increases to 10%. The value of the bond will drop to $500 so it has a yield of 10% (50/500 = 10%). You buy the bond at this point for $500 (ACB = $500) and hold it to maturity when you are paid the face value of $1000. At that point you have a capital gain of $500 ($1000 - $500 ACB = $500).

It is actually a little more complicated than that as you need to take into account the maturity date (yield to maturity or YTM) and any accrued interest when the bond is purchased.

For GIC's, the initial purchase value of the GIC should be included in the ACB.

Cash should also be included at face value in the ACB.

How is income from a corporation entered?

Think of your corporation as a separate investment that generates different types of income. Corporations typically generate eligible dividends, ineligible dividends and capital dividends (non-taxable). You may take draws from your corporation during the year, but at tax time, your accountant allocates those draws to the three types of income I listed above.

Show the advanced worksheets by clicking the "Show" button on the "Start Here" worksheet. In the "Other Income..." section on the "1 Advanced" worksheet ("2 Advanced" for a spouse) you can enter Eligible Dividends, Ineligible Dividends and Capital Dividends. Use the "Non-Taxable" column for Capital Dividends. Enter the amounts for each year they apply. Your accountant should be able to help estimate these income streams.

Any entries in the "1 Advanced" or "2 Advanced" worksheets must be converted to future dollars. Use the value in the Inflation Multiplier column to convert to future dollars.

If you are winding down your corporation, there may be a tax advantage to do it before age 72 when RRIF minimum withdrawals kick in. Using the "Smooth Taxes" withdrawal method, the spreadsheet will try to optimize around the income from your corporation.

If your corporation will outlive your projection and you want to include the value in your Estate you have two options.

1. Include the after tax wind-down value of your corporation in each year in the "Other Assets...->Non-Taxable->Value" column, or

 2. Include the value of your corporation before tax for each year in the "Other Assets...->Taxable->Value" column and the Adjusted Cost Base in the "Other Assets...->Taxable->ACB" column. To get the Estate value of your corporation, capital gains tax is applied to the difference between its value and ACB in any given year.

If you are receiving a salary from your corporation, include the gross amount less any CPP premiums that you pay personally in the "Other Income...->Other" column.

Income from your corporation will show up as "Other Income" on the Cashflow chart. If you include the corporation value in the advanced worksheets it will get added to yours and your spouse's Net Worth.

Is the Guaranteed Income Supplement (GIS) supported?

No, the spreadsheet does not support the GIS. If you are using the spreadsheet it is likely that you have sufficient means to not need the GIS.

There is a tax strategy whereby people lower their taxable income from ages 65-71 by deferring CPP and OAS and paying expenses from their non-registered funds and TFSAs in order to qualify for the GIS. The GIS is intended for low income seniors and the ethics are questionable when someone with means accesses the program. The government may close this loophole in the future and it is unwise to plan for it.