Data Entry Worksheet

The Data Entry Worksheet and the Dashboard are where you enter the data needed for the Spreadsheet calculate your projection. The Data Entry Worksheet typically contains information that you cannot change such as your name, birthdate and account balances at the beginning of the year, while the Dashboard allows you to experiment with the choices you make such as retirement age, retirement fund contributions and withdrawals and downsizing of your principal residence.

The Data Entry Worksheet contains two columns, one for each person. Enter your data in the white cells. If you are a single person, leave the second column empty. Replace any default data in the Data Entry worksheet with your own. A description of the fields follows:

Personal Info

Name: Enter each person’s first name. This identifies the person in on other worksheets within the Spreadsheet. For a single person, make sure the second name is empty.

Birthdate: Enter each person's birthdate in the format specified. This allows the Spreadsheet to determine the person's age in any given year, when to start pensions and which tax benefits and credits to apply.

Province/Territory: Use the province and territory drop-down to specify where you live. This is used to calculate provincial or territorial taxes. Taxes for all provinces and territories are supported. If you are a couple, the Spreadsheet assumes that both of you live in the same province for tax purposes.

Date of marriage: For a couple, enter your date of marriage or common law in the format specified. The Spreadsheet needs this for CPP/QPP sharing calculations. The date is ignored for single people.

Employment Income

If you are still working, you need to enter some information in the Employment Income and Deductions section.

Salary: Enter your gross annual salary in the Salary cell. This amount should include your pay, any bonuses and taxable benefits including any employer contributions towards a group RRSP.

Employer group RRSP contributions: Enter the annual amount of any employer group RRSP contributions. This amount should also be included in your salary.

Self-employment income: Enter the annual amount of any self-employment income. For home businesses, this is your annual net profit – the taxable portion of your income – what you pocket after expenses are paid.

Deductions from Employment Income

Employee group RRSP contributions: Enter the annual amount of any employee contributions to a group RRSP.

Employee pension contributions: Enter the annual amount of any employee contributions to an employer-sponsored pension plan.

Pension adjustment: If you have an employer sponsored pension plan, there will be a pension adjustment (PA) amount in box 52 of your T4 slip or box 034 of your T4A slip for the previous tax year. Enter that amount here. Your pension adjustment limits your RRSP contribution room.

Other deductions: The Spreadsheet automatically calculates CPP/QPP and EI deductions for employment and self-employment income but if you have any other deductions such as union dues, enter the annual amount here.

Pension Income

Canada Pension Plan (CPP)

Monthly CPP amount and at age: In your My Service Canada account, the government will give you an estimate of your monthly CPP benefit at a given age – typically 65. This amount is given in current year dollars. Enter the amount and age they give you here. If you are already receiving CPP benefits, enter the monthly amount you are currently receiving and your current age. If you don’t have access to your My Service Canada account and want to make a rough estimate, Google maximum and average CPP benefit for the current year and make an estimate for your own situation.

*Caution: My Service Canada estimates may be too generous if you are planning to retire early and don't have enough "drop out" years to cover until age 65. In this case you may want to use a pension consulting service to get a better estimate.

Quebec Pension Plan (QPP)

Monthly QPP amount and at age: In your My Retraite Quebec account, the government will give you an estimate of your monthly QPP benefit at a given age – typically 65. This amount is given in current year dollars. Enter the amount and age they give you here. If you are already receiving QPP benefits, enter the monthly amount you are currently receiving and your current age. If you don’t have access to your My Retraite Quebec account and want to make a rough estimate, Google maximum and average QPP benefit for the current year and make an estimate for your own situation.

*Caution: My Retraite Quebec estimates may be too generous if you are planning to retire early and don't have enough "drop out" years to cover until age 65. In this case you may want to use a pension consulting service to get a better estimate.

Old Age Security (OAS)

OAS years: Enter the number of years you have lived or will have lived in Canada between the ages of 18 and 65. OAS requires you to live for at least 40 years in Canada to receive the full amount, otherwise it is prorated.

Defined Benefit Pension

Monthly pension and Monthly bridge benefit: If you are fortunate enough to have a defined benefit pension plan, enter your monthly pension amount. This can be specified in current or future year dollars. Under Monthly Bridge benefit, enter the top-up amount you will receive if you take your pension prior to age 65. Again this can be specified in current or future year dollars.

Pre-retirement pension indexing: Pre-retirement indexing lets the spreadsheet know how to convert your monthly pension amount and monthly bridge benefit into future year dollars. If there's a percentage specified on your pension statement you can enter it here. If you have already entered future year dollars enter zero percent or leave empty to use the general inflation rate you enter on the Dashboard worksheet.

Post-retirement pension indexing: Similarly, after you start receiving your pension the spreadsheet needs to know how to index that pension. Specify zero percent for no indexing, enter a specific percentage if specified on your statement or leave empty to use the general inflation rate.

Survivor benefit: The Survivor benefit is specified as a percentage and is the amount that a surviving spouse would receive in the event that the pensioner passes away.

RRSP/RIF and LIRA/LIF

Withdrawals from your RRSP/RIF and LIRA/LIF are entered on the Dashboard worksheet. You can use the Optimizers to help calculate the most tax efficient withdrawals.

Income Tax

The Spreadsheet needs to know some information from your previous year’s tax return so it can calculate future taxes correctly. Most, if not all of these amounts can be found on your Notice of Assessment from the CRA. If you have not filed you taxes for the previous year yet, estimates are sufficient. To be conservative, you can leave all the carry forward amounts blank.

Income tax from previous year: Enter the income tax that was assessed for the previous year. This is not necessarily the tax you paid on filing but it’s the total tax due which may be different if you paid tax installments or tax was deducted from your paycheck. For the purposes of its calculations, the Spreadsheet pays income tax assessed from the previous year in the current year (i.e. income tax from 2023 is paid in 2024).

Net capital loss carryforward: Enter your Net Capital Loss carry forward. A Net Capital Loss arises when you sell an investment at a loss and the carry forward happens when you cannot apply it against current or past capital gains. Again, this amount, if any, should be shown on your Notice of Assessment.

TFSA contribution room and RRSP contribution room: Enter any TFSA and RRSP contribution room carry forwards in the respective cells. Do not include additional contribution room for the current year as the Spreadsheet will add this automatically.

Donations carryforward: If you have any charitable donations that you have reported but not claimed, enter the total amount here.

Accounts

Enter the balances for your various accounts at the beginning of the year. If you have joint accounts, you can split the balance between spouses. You are also asked to provide information about the expected returns from your accounts. The spreadsheet uses these percentages to run projections over the next 50 years so they should be representative of the long term. Choose wisely and conservatively.

Savings Accounts: Enter your savings account balances and the interest rate you receive on your deposits.

Non-Registered Investment Accounts: Your non-registered investment accounts require a bit more information so taxes can be calculated properly. Enter your non-registered investment account balances. ACB stands for Adjusted Cost Base. It is also called Book Value and can be found on your investment statements. Knowing the ACB allows the spreadsheet to calculate the amount of capital gains when investments are sold. Enter the total ACB for each account. The next three rows relate to returns. Enter the Capital Appreciation Rate, Canadian Eligible Dividend Rate, Foreign Dividend Rate and Interest Rate. Percentages specified here apply to the account balance as a whole. For example if your non-registered account has a balance of $100,000 at the beginning of the year and you're expecting $2,000 in capital appreciation and $3,000 in dividends you would specify a capital appreciation rate of two percent and a dividend of three percent. The same applies to the interest rate.

RRSP/RRIFs: Enter the balances and expected total returns for your RRSP/RRIF accounts.

LIRAs/LIFs: LIRA/LIF stands for Locked In Retirement Account and Life Income Fund. These accounts are created when you exit a pension fund from a previous employer. Enter the balances, expected total returns and the jurisdiction that governs the fun. The jurisdiction determines the maximum and minimum withdrawal amounts and when funds become unlocked.

Overdraft Account: The Overdraft Account is used when your savings account is depleted - when there's not enough money in your savings account to meet expenses and you go into debt. In this case the spreadsheet charges interest to your account. Use large percentages here (up to 25%) if you want to discourage the Optimizers from acquiring debt.

Consumer Debt (Loans)

Enter the details for up to three consumer loans. The spreadsheet pays the minimum monthly payments then allocates any extra funds to the loans in the order specified (loan 1, loan 2, then loan 3). For the fastest repayment enter the loand in order of decreasing interest rate (i.e. the loan with the largest interest rate is loan 1). This is referred to as the "avalanche" method.

Name: Enter a name to identify your loan.

Balance: Enter the total balance of the loan at the beginning of the year.

Interest Rate (%): Enter the annual interest rate payable on the loan.

Payments per Year: Enter the number of loan payments you make in a year. (12=monthly, 24=semi-monthly, 26=bi-weekly, 52=weekly)

Minimum Payment Amount: Enter the required minimum amount of each loan payment.

Real Estate

Home (Principal Residence)

Value: Enter an estimate of the market value of your home if you were to sell it today.

Mortgage: Enter the total amount of any outstanding mortgages on your principal residence at the beginning of the year.

Mortgage payment and the mortgage interest rate are entered on the Dashboard worksheet.

Other Real Estate

Enter the details for up to three income or non-income generating properties.

Name: Enter a name to identify your property.

Ownership (%): Enter the percentage ownership for each person. For a single person enter 100%.

Value: Enter the estimated market value of your property at the beginning of the year.

Income: Enter the annual income for your property. For a  property that does not earn income, enter 0.

Expenses: Enter the annual expenses for your property EXCLUDING mortgage payments and capital improvements.

Mortgage: Enter the total amount of any mortgage balances on your property at the beginning of the year.

Interest Rate (%): Enter the annual interest rate payable on your mortgage.

Payments per Year: Enter the number of mortgage payments you make in a year.  (12=monthly, 24=semi-monthly, 26=bi-weekly, 52=weekly)

Payment Amount: Enter the amount of each mortgage payment.

Land Value at Purchase: For an income generating property, enter the estimated land value for your property when you purchased it, otherwise enter 0.

UCC/ACB: For an income generating property, enter the Undepreciated Capital Cost (UCC) or any building(s) on your property at the beginning of the year, otherwise enter the Adjusted Cost Base (ACB). The UCC should be available from your tax return and the ACB is the purchase price of the property plus the cost of any capital improvements you have made since you purchased it.

CCA Rate (%): For an income generating property, enter the capital cost allowance (depreciation) rate applicable to your building(s) per Revenue Canada, otherwise enter 0%.

Capital Improvements: Enter the annual budget for capital improvements to your property. This should be an average for all the years the property is owned.

Sale Year: Enter the year you plan to sell your property. Leave empty if you do not plan to sell.

Closing Costs: Enter an estimate of closing costs for selling your property in current year dollars. Leave empty if you do not plan to sell.

Life Insurance

Enter the Death Benefit and Cash Value for any individually held and jointly held life insurance policies. The cash value is the amount you would receive if you surrendered the policy prior to death. For some policies, the cash value and death benefit increase over time. This is supported by the Spreadsheet but is an advanced feature.