Data Entry Worksheet
The Data Entry Worksheet and the Dashboard are where you enter the data needed for the Spreadsheet to 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.
Important: the Spreadsheet makes a distinction between blank cells and cells containing 0 (zero). If the instructions ask you to leave the cell blank, make sure there is nothing, not even a 0 (zero) in the cell.
Each field has a description to the right. Take time to read and understand what goes in each cell. A mistake here can greatly affect your projection. Rather than repeating the descriptions from the spreadsheet, general descriptions of are provided in addition to details for some of the more difficult entries.
Personal Info
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 name blank. Replace any default data in the Data Entry worksheet with your own. One advantage of a spreadsheet vs. a web application is that your data remains private and totally under your control. The Spreadsheet collects only the information necessary to create your projections.
Employment Income
If you are still working, complete the information in the Employment Income and Deductions section. These amounts are indexed for inflation and stop when you retire. The spreadsheet assumes you retire at the end of the month when you reach your retirement age. Your retirement year is prorated to the number of months worked.
Salary: Enter your gross annual salary in the Salary cell. This amount should include your pay, any bonuses and taxable benefits excluding any employer contributions towards a group RRSP which are entered on the next row.
Employer group RRSP contributions: Enter the annual amount of any employer group RRSP contributions. Do not include this amount 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 and before income taxes 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
If you are not already receiving CPP, QPP and/or OAS, your starting age is entered on the Dashboard worksheet. CPP and QPP can be started early for a reduced monthly payment and CPP, QPP and OAS can be started late for an increased monthly payment. The Spreadsheet's optimizers will help you decide the optimal age to start these pensions.
Canada Pension Plan (CPP)
Current monthly CPP amount: If you are currently receiving CPP, enter the monthly amount. Leave blank if you are not yet receiving CPP.
OR
Future 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)
Current monthly QPP amount: If you are currently receiving QPP, enter the monthly amount. Leave blank if you are not yet receiving QPP.
OR
Future 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)
Current monthly OAS amount: If you are currently receiving OAS, enter the monthly amount. Leave blank if you are not yet receiving OAS.
OR
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
The Defined Benefit Pension section may also be used if you have purchased an annuity.
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 blank 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 blank 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.
Start age: Leave blank to start your pension at the retirement age specified in the Dashboard or enter a specific age to start your pension. This may be a younger age if you are already receiving your pension or a future age.
RRSP/RIF and LIF
Withdrawals from your RRSP/RIF and LIF are entered on the Dashboard worksheet. You can use the Optimizers to help calculate the most tax efficient withdrawals for your situation.
Income Tax
The Spreadsheet needs to know some information from your previous year’s tax return so it can calculate future taxes and tax payments correctly. Most, if not all of these amounts can be found on your Notice of Assessment from the CRA. If you have not filed your taxes for the previous year yet, estimates are sufficient. To be conservative, you can leave all the carry forward amounts blank.
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. Check out FP Canada's guidelines for long term estimates of returns for various asset classes.
Most accounts require you to enter the balance at the start of the year and total return as a percentage. Assuming you have not made any deposits to or withdrawals from the account, the total return can be calculated as follows: (year end balance - year start balance) / year start balance x 100.
Your non-registered investment accounts require a bit more information so taxes can be calculated properly. Enter the totals and expected returns for each of the asset types listed: Canadian Equities, Foreign Equities, Interest Bearing Investments and Cash (non-interest bearing). Confirm that the total return and balance for the entire account are reasonable. 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.
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 loans 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. To reduce the number of loans quickly enter the loans in order of increasing balance (i.e. the loan with the smallest balance is loan 1). This is referred to as the "snowball" method.
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.