The downloadable Excel spreadsheets described in this post can be used to determine when to take your Canada Pension Plan retirement pension. Once you copy your CPP Statement of Contributions into the table provided, a macro can be run that fills in the drop-outs and removals. Yearly payments, break-even ages and cumulative payments at various ages plus many charts will give you an insight as to when to take the CPP for your circumstances. You can also set inflation and marginal tax rates.
This post is one in a series to help you decide when to take the CPP pension. Click on Download Zipped Excel Spreadsheets which all of these posts refer to and that you can customize for your situation. Because each person’s circumstances are different it is hoped that these posts and the spreadsheets help you make a decision. (The posts below will be adjusted later or replaced when the enhanced CPP, due to be implemented starting in 2019, has been fully documented by the government.)
|1. What is Involved||2. The Rules||3. Factors Involved|
|4. Analysis||5. Spreadsheets|
Download the Spreadsheets
The accompanying Excel spreadsheets, which you can download by clicking on Download Zipped Excel Spreadsheets, are described below. Once downloaded, double-click on the file to unzip it and then open the two spreadsheets. The main spreadsheet is called “When To Take The CPP.xlms” and the comparison spreadsheet is called “When To Take The CPP 1vs2.xlms”.
The main spreadsheet has three pages, a number of sections and some lines are numbered. Open it now so you can follow along. There is a link on the spreadsheet back to this post so that you can return to this description, if desired at a later time. Only enter values into the cells that are outlined.
Please ensure that Macros are enabled when opening the spreadsheets.
The main spreadsheet is initially protected so that only the enterable cells are available. This makes it easy to tab to each cell that needs your input and it also stops any inadvertent change to the other cells that have formula.
You do not need to know much about Excel to make the main spreadsheet work for you. Except for a few dates and ages, plus the Statement of Contents, everything else is automatic and run by macros. However, working through the choices you have and the options available may take some time and attention to detail. It has been designed to support as may circumstances that seem reasonable, but I am sure you can find some that have not been considered. If it does not do what you want, you find any errors or have suggestions, use the email form on the Contact page.
Because this post is very long and references more than one spreadsheet and two pages, to go to any of the key sections, click on the link below:
- Options Overview
- Using the Spreadsheet
- Spreadsheet Description
- C, D, E, and F Columns
- Statement of Contributions
- Options Page
- 1vs2 Spreadsheet
The spreadsheet provides a number of ways to evaluate your options for when to take the CPP pension. The options are as follows:
Option 1. Stop working before or at age 60 and either take the pension at 60 or delay up to age 70.
Option 2. Stop working after age 60 and either take the pension when work stops, delay up to age 70 or use the Post-Retirement Benefit to take it before you stop working.
When you evaluate these options you need to consider the following:
- • When you want to stop working.
- • When you want to take the pension.
- • Yearly payments for each option.
- • Break-even age if you delay.
- • Cumulative payments.
To help in the evaluation, the spreadsheet has four columns that can have the age set to whatever you which to evaluate. They are as follows:
- “C – If Taken At” or “C – If PRB At” a specified age.
- “D – WorkTo/Take At” a specified age.
- “E – Delay To 1” to a specified age.
- “F – Delay To 2” to a specified age.
Using these columns, the following options can be evaluated and compared in detail:
Option 1. Stop working before or at 60:
- 1a) Take the pension at 60. This uses “C – If Taken At“.
- 1b) Delay taking the pension up to 65. This uses “E – Delay to 1“.
- 1c) Delay taking the pension up to 70. This uses “F – Delay to 2“.
Option 2. Stop working after 60:
- 2a) Take the pension at 60 using the Post-Retirement Benefit (PRB). This uses “C- If Taken At” which changes to “C – If PRB At” and the “If PRB” values.
- 2b) Take the pension when work stops up to 70. This uses “D – Work To/Take At“.
- 2c) Delay taking the pension up to 70. This uses “F – Delay to 2“.
Note that only 3 of the four columns are used for each option with C not used for Option 1 and D not used for Option 2.
The spreadsheet supports using any ages and not just the ones discussed here. For example, in option 1, work could stop at 62. In option 2, the pension could start at 63 but work could continue to 67. Try any variations you want that suit your circumstances.
The table on the Entry page provides all the numbers needed, but it can be a little difficult to put it into perspective. For this reason, the Options page is provided which summarizes this data in both a narrative and a table that adds additional information. If Option 1 and Option 2 as discussed in the Options page below are to be compared, a separate spreadsheet is provided that can be linked to this spreadsheet. This is also discussed later,
When the spreadsheet is first opened, it only has values for some lines and nothing in the “Statement of Contributions” section. As a result, there is a lot of cells with #Div/0! and most of the charts are empty.
To get started, follow these steps on the spreadsheet (see the screenshots below):
- 1) Enter your Age Now on line 2. Do not change line 1. Change the Inflation and Marginal taxes rates of lines 3 and 4, if you want. Change the Ages on line 5, if you want. It is best to leave them this way as it is the Option 1 discussed in the Options post.
- 2) Click on the “Go To SOC” button on the first line.
- 3) Enter “Your Birth Date” that is highlighted. This will fill the “Your birthday in the year” column. The removals columns to the right will become red.
- 4) Enter your Oldest and Youngest Child Birth Date, if you intend to use the Child-rearing Drop-outs. If there are children, the child-rearing years will be highlighted in orange.
- 5) For each line or group of lines in your Statement of Contributions, copy and Paste Special (Values) it/them into the corresponding row on the spreadsheet. If the last year from the SOC is not for 2017, then guess at values for each year up to 2017 and enter them. Make sure you enter values for “Your Year” and “Your Pensionable Earnings”.
- 6) Click on the “Load Sort, Child-rearing and Removals“ button that is above columns 8 and 9. This will fill columns 8 to 14 of the table.
- 7) Click on the “Go To Top” button or scroll back to the top of the page.
- 8) If you want to change inflation, enter the % into the value at line 3. As shown in Chart 10, inflation has been hovering around 2% for the past 2 decades. In 2017 it was 1.4%, so it would be appropriate to not let it have a lower value. If you want to change the marginal tax rate, enter the % into the value at line 4.
- 9) Examine the results and the charts, particularly the break-even ages, the yearly and cumulative payments charts and the cumulative payments at age 85.
- 10) Try Option 2 (working after 60), if desired.
- 11) Go to the Options page to see a summary and a narrative at any time.
The screenshots below are for the child-rearing example. Click on any screenshot to enlarge it.
There are three pages to the spreadsheet: Entry, Options and Tables. You can switch between Entry and Options by using the button at the top-right of the page or by using the tabs at the bottom of the page. The Tables page contains data that is used in the charts and the other pages and is not described here.
A – Starting Values
Go To SOC button – This button next to the Starting Values text on the “Entry” page will scroll the spreadsheet down to the “Statement of Contributions” section where you can enter your birth date and that of your children and your contributions obtained from the government. The drop-outs can be easily filled in by using the button in that section.
1) CPP Maximum Amount at age 65 – This line has two cells that can be entered: the year and the payment. They already have 2018 and the payment for 2018. For later years, change the year and the payment. The year is not used anywhere else on the spreadsheet but the payment is used in many place. The source of this amount is Canada Pension Plan – How much could you receive.
2) Your Age Now – You can enter any age. However, if it is greater than any of the ages below, they will be shown in red and must be changed. If the Age Now is less than 60, the inflation rate is used to change the values of lines below.
B – Rates
3) Inflation – Enter the value of inflation that you want to use for the CPP pension. It adjusts the payments expected on line 12 and payments each year. Check the charts to see the impact. Inflation over the past few years is shown in Chart 10. In 2016 it was 1.4%. Inflation decreases the break-even age.
4) Marginal – Enter the marginal tax rate you expect, before payment of the CPP pension amount. This is used to reduce the available funds between the “If Taken At” payments and each of the “Work To/Take At”, “Delay to 1” and “Delay to 2” payments. This is done because you will be receiving at least the “If Taken At” payment and paying taxes on it, but anything above it if you delay taking the pension will be taxed at the marginal rate and consequently be less money available than you expect. Including marginal taxes increases the break-even age.
These buttons are to the right of the “B – Rates” section.
The Go To Options button will take you to the Options page. You can also use the tab at the bottom left of the spreadsheet.
The Link Line 10 button will re-establish the links of line 10 as indicated below.
Messages Off and Messages On buttons – Be default, when you enter a cell, a message appears to indicate the constraints imposed on the value. Look at each cell by tabbing to see these constraints. After a while, these messages become annoying, so click on the Messages Off button.
Protect and Unprotect buttons – By default, the spreadsheet is protected to make data entry easier because only the enterable cells are unprotected. This also ensures that the formulae in other cells are not changed. Use the Tab key to move to the next enterable cell.
- • C – If Taken At: This column should be used when you can first take the CPP pension. It normally will be at age 60. On the charts, results for this column are shown as the thick black line labelled “If Taken At” plus the age entered. If the age of D (below) is not the same as C, the text will change to “If PRB At”. It appears on the charts as the dashed black line labelled “If PRB At” plus the age entered.
- • D – Work To/Take At: Enter the age when work is to stop. It can be any age that is greater than or equal to the “If Taken At” age and up to 70. On the charts, results for this column are shown as the solid red line labelled “Work/Take” plus the age entered.
- • E – Delay To 1: Enter the age to which the CPP pension is to be delayed. It can be any age that is greater than or equal to the “Work To/Take At” age and up to 70. On the charts, results for this column are shown as the solid green line labelled “Delay To” plus the age entered.
- • F – Delay To 2: Enter the age to which the CPP pension is to be delayed. It can be any age that is greater than or equal to the “Delay to 1” age and up to 70. On the charts, results for this column are shown as the solid orange line labelled “Delay To” plus the age entered.
The following are the lines used for each of these columns.
5) Age – Enter the age that applies for the column. The cell to the right will show a percent that is the amount of the pension that is possible for that age. It uses the values in the table in the “CPP Change From Age 65” section described below. Make sure that these ages are what you want before you complete the “Statement of Contributions” section. It is best to leave them as they are initially set for Option 1. Each time an age for D, E or F is changed, an Alert window will indicate what is being done or required.
6) Contribution Years – The CPP retirement pension calculation uses the total period from the start of your age 18 to the age when you take the pension. For example, for the “If Taken At” age of 60, it will be 42 years (60-18). This is included here so you can see how this is reduced by drop-outs.
7) Child-rearing Drop-out Years from SOC – When you have finished filling in the “Statement of Contributions” section, the value will indicate how many drop-outs have been used for each age.
8) Other Drop-out Years from SOC – When the SOC section is filled in properly, each of C, D, E and F will have the number of years that have been dropped from the calculation. The cell to the right shows the percent of the Contribution Years less Child-rearing drop-outs that are being used. This cannot exceed 17% and if it does, the years and the % cells have a have an outline and will be in red. In addition, if the number of years has too many “Ignore” entries, it will have an outline and be in blue. The starting payments of line 12 will be set to 0 until these errors are corrected in the SOC section. These cells are linked to cells in the “Results of SOC” section.
9) Included Years and Average % from SOC – These values are linked to cells from the “Results of SOC” section and indicate how many years are included in the pension payment calculation and the average Earnings/YMPE percentage of all years that do not have any Drop-outs or Ignores. If the years exceed 39, the cell will become red, the percent is set to 0% and, if line 10 is still linked to the % cell, the payments of line 12 will be set to 0 until it is corrected in the SOC section.
10) Average % of Maximum Earnings at Age – By default, this is linked to the % cell of line 9. If you do not have the SOC, you can enter the percent you expect for all your working years that count towards the pension. The cell to the right is the same percent and is used in the calculation of line 12. (Do not expect accurate results until you have your entered your SOC.) If you manually enter a value, you can re-set the formula to the % cell of line 9 by clicking on the Link Line 10 button which is at the top of the page.
11) Inflation from Age Now – This is calculated by the spreadsheet and will have a value depending on the ages used. There can be values in all columns if there is an inflation amount in line 3. The first value is the increase and the second is the value plus 100%.
12) Starting Payment Expected at Age – This is calculated by the spreadsheet by multiplying together the percentages in lines 5, 10 and 11. The result is given in the second cell in the column as a percent. The first column uses this percent and multiplies it by the amount entered in line 1. These amounts are used in the arrays that start at “Arrays for Charts” on the Tables page and the charts.
13) Break-even Age Compared to If Taken At (Actual, Adjusted) – This age is critical to your decision making. It shows the ages at which the cumulative amount for any column equals that of the “If Taken At” age. The first cell uses actual payments that only include Inflation. The second cell includes both inflation and the effect of Marginal Taxes. The second cell will always have an age that is greater than the first cell, if there is a value in the Marginal cell in line 4. This line is only visible when the ages of C and D are the same. This situation is for Option 1.
14) Break-even Age Compared to If PRB (Actual, Adjusted) – This age is critical to your decision making. It shows the ages at which the cumulative amount for any column equals that of the Post-Retirement Benefit age. The first cell uses actual payments that only include Inflation. The second cell includes both inflation and the effect of Marginal Taxes. The second cell will always have an age that is greater than the first cell, if there is a value in the Marginal cell in line 4. This line is only visible when the ages of C and D are NOT the same. This situation is for Option 2.
G – Payments at age to the right
15) Cumulative Payments Age – Next to the text for section is an age that you can change. It is initially set to 85 as this is close to the life expectancy of most break-even ages. This section is provided so that you do not have to examine the charts or the arrays in detail. Enter any age desired to see how the payments of lines 16 and 17 change.
16) Yearly Payment at [age] (Actual, Adjusted) – The spreadsheet uses the age entered on line 15 to find the yearly payment in the arrays for columns D, E and F. The first cell shows actual payments that only include Inflation. The second cell includes both inflation and the effect of Marginal Taxes. For column C, the first cell is the Actual while the second cell is the Actual if the PRB is used. (The former will only be visible for Option 1 and the later for Option 2.) Use this line to compare the yearly payments at each of the ages so that you can determine how much it adds to your total retirement income (e.g. Old Age Security, company pension, Registered Retirement Income Fund payouts, etc.).
17) Cumulative Payments at (age) (Actual, Adjusted) – The spreadsheet uses the age entered on line 15 to find the cumulative payment in the arrays for columns D, E and F. The first cell shows actual payments that only include Inflation. The second cell includes both inflation and the effect of Marginal Taxes. For column C, the first cell is the Actual while the second cell is the Actual if the PRB is used. (The former will only be visible for Option 1 and the later for Option 2.) Use this line to compare how much you will be gaining in cumulative payment if you delay, or alternatively, how much you forgo by taking the CPP early. Note that the cumulative payments for the ages of D, E and F are the same independent of whether the PRB is used or not. However, the gain is significantly reduced when the PRB is used. Rather than have to do the subtraction, refer to Chart 8 to see the differences.
The four buttons on the same line as the title can be used to Expand and Shrink charts 2 and 10 so that you can see the break-even ages in more detail. There are 10 charts that change when data is changed on lines 1 to 17 and 2 that are from the “Statement of Contributions” section. The suffix for charts 1 and 2 are inflation % and the Average %. For charts 3 to 10, Marginal % is included.
- 1. Actual Yearly CPP Payments, Inflation Only – This shows the change in actual yearly payments from age 60 to age 95 for the appropriate ages in line 5 for the option.
- 2. Actual Cumulative CPP Payments, Inflation Only – This shows the actual cumulative payments from age 60 to age 95 for the appropriate ages in line 5 for the option. To expand the chart to view the break-even ages, click on the Expand Actual button. You must have macros enabled. To restore it to the smaller size, click on the Shrink Actual button.
- 3. Actual Yearly CPP Payment for If Taken At Age with Other Ages to be Added, and Break-even Ages – This is a combination chart of charts 1 and 5. It shows the “If Taken At” line. It then subtracts this line from each of the other lines and shows the difference as “add …” lines. This chart makes it easy to see how much is added by “Delay To 1” or “Delay to 2”. The diamonds are the Actual and the circles are the Adjusted break-even ages for each line. After the diamond the payment will more than the “If Taken At” amount by the value of the line. This chart is only visible when the ages of C and D are the same.
- 4. Actual Yearly CPP Payment for If PRB with Other Ages to be Added, and Break-even Ages – This is a combination chart of charts 1 and 5. It shows the “If PRB At” line. It then subtracts this line from each of the other lines and shows the difference as “add …” lines. This chart makes it easy to see how much is added by “Working To/Take At”, “Delay To 1” or “Delay to 2”. The diamonds are the Actual and the circles are the Adjusted break-even ages for each line. After the diamond the payment will more than the “If PRB At” amount by the value of the line. This chart is only visible when the ages of C and D are NOT the same.
- 5. Break-even Ages – This chart shows the break-even ages of lines 15 or 16 to help visualize the differences. If you want to take the CPP pension early, choosing the option that has the greatest break-even age is probably a good solution. Note that the chart only shows 2 sets of columns depending on whether “If Take At” or “If PRB At” is used. Notice the effect that the Marginal tax rate has on the break-even ages (shown in the Adjusted columns).
- 6. Cumulative at [age] ($K) – This chart shows the cumulative payments of line 17 (Actual and Adjusted) to help visualize the differences. If you want to take the CPP pension early, choosing the option that has the lowest payment is probably a good solution. Note that the chart only shows 2 sets of columns depending on whether “If Take At” or “If PRB At” is used. Notice the effect that the Marginal tax rate has on the payments (shown in the Adjusted columns).
- 7. Break-even Ages less age of (text) – This uses the same data as chart 5 but subtracts the “If Taken At” or “If PRB At” break-even age (which will also show as text in the header) so that the difference is more noticeable.
- 8. Cumulative at [age] less amount for (text) ($K) – This uses the same data as chart 6 but subtracts the “If Taken At” or “If PRB At” payments so that the difference is more noticeable and includes which one is used as text in the header.
- 9. Adjusted Yearly CPP Payments, Inflation and Marginal – This shows the change in adjusted yearly payments from age 60 to age 95 for each of the ages in line 5. Comparing this chart to chart 1 (inflation only) is a little difficult, but notice how the “Delay To” lines are closer to the other lines.
- 10. Adjusted Cumulative CPP Payments, Inflation and Marginal – This shows the adjusted cumulative payments from age 60 to age 95 for each of the ages in line 5. To expand the chart to view the break-even ages, click on the Expand Adjusted button. You must have macros enabled. To restore it to the smaller size, click on the Shrink Adjusted button.
- 11. Comparison of Pensionable Earning to YMPE – This chart is obtained from data in the “Statement of Contributions” section. It shows the YMPE as a solid red line from 1966 to 2029. Your pensionable earnings are the purple line while the years that are included in the “If Taken At” column are the blue columns. If there are even a few columns that are below the YMPE, the yearly CPP payment will not be the maximum possible.
- 12. % Yearly Change in YMPE and Inflation Since 1996 – The change in the YMPE is close to the change in the Consumer Price Index (CPI), that is, Inflation, but is not the same. The CPI uses the change in prices and the YMPE uses the average industrial wage for the year.
This section is where you enter your CPP Earning and Contributions, referred to as the Statement of Contributions, plus some birth dates. You must fill in this section to get any results and for the charts to have any values.
To the right of the title is the Go To Top button. Rather than having to scroll up to get to the top of the spreadsheet, click on this button.
Below the title is a place for you to enter Your Birth Date. When this is done, the “Your birthday in the year” (column 2 of the table below) will have the years that are less than 18 and more than 69 shaded in grey to indicate that they are not involved in the calculations.
If you are going to use the child-rearing drop-outs, enter the Oldest Child Birth Date and the Youngest Child Birth Date. The Sort Order of column 9 will have orange in the years that are eligible for child-rearing drop-outs.
The Clear SOC Data button is provided in case you make a mistake and want to clear all or parts of the table. There are 5 option that you can select by entering the number into the request area. You cannot recover from any clears, so save the spreadsheet before do them in case you want to start again.
The table that has the Contributions is below the dates and has many columns. Only columns 4 to 7 and 10 to 14 are enterable when protection is on. Do not change any other columns as they are used in the calculations.
Once you have access to your SOC (see When To Take The CPP – The Rules as to how to do this), copy the data in the 4 columns (Year, Your Contributions, Your Pensionable Earnings and Notes). Then position your Years to match those of column 1 in the spreadsheet table, starting in column 4 (Your Year). Finally Paste Special – Values using the Edit menu. Make sure that you copy all of the columns (Year, Your Contributions, Your Pensionable Earnings and Notes) so that they fill columns 4, 5, 6 and 7.
When completed, add any future years that you want if you have not yet reached age 60 or want to work into the future. You only have to enter values for Your Year and Your Pensionable Earnings.
Then click on the Load Sort, Child-rearing & Removals button. This will run a macro that fills columns 10 to 14 and forces all the other cells to calculate.
If you want to try any of the drop-outs and removals yourself, click on a cell and use the drop-down menu that appears to the right. If any of the limits are exceeded, cells in the column will be outlined in red or blue or filled in red as explained above. The screenshot above shows the 3 different errors that can occur.
For each future year beyond 2018, the YMPE of column 3 will change depending on the change to the average industrial wage. To approximate this amount, the YMPE after the current year is increased by the inflation rate. However, to obtain the exact amount for future years, consult CPP contribution rates, maximums and exemptions and replace the inflated amount with the actual amount.
J – Results of SOC
This section generates the numbers needed to ensure the drop-outs and removals are within the required limits and the Average % of Maximum Earnings for the Included Years for each age set in line 5. These numbers are also linked to by cells on lines 7, 8 and 9.
The row that is labelled “Drop-out % for Years that are not Child-rearing. Maximum permitted = ” is set to 17.03% because there are a few cases (specifically if all SOC entries are the maximum) where the actual maximum permitted of 17% causes a unsolvable conflict between too many drop-outs and too many Included Years. If this is not acceptable, use 17% and watch the other cells on this line to see if they exceed the limit.
Once any errors are corrected in the Drop-outs and Removals, if any, of particular interest is how much of an affect these have on the difference between the “Average Pensionable Earnings % for all Years” and the “Average % of Maximum Earnings for Included Years”. By using drop-outs and other removals, the difference is usually quite large.
K – CPP Retirement Payments using YMPE Calculation
The calculation used by the Government of Canada to obtain your CPP retirement payments is complex and not available on the internet. However, there is a very detailed description of the methodology at How to calculate your CPP retirement pension by D. Runchey. He also offers a service at his website that provides a calculation of your CPP benefits.
This section uses this methodology, but with it restricted to years and not months. Columns 19 to 22 of the “Statement of Contributions” section are used for this calculation.
Due to rounding errors, there is often a difference between the calculation used in this spreadsheet and the YMPE method of a fraction of a percent.
I expect that there will be a difference between the Government of Canada’s calculation and this spreadsheet due to using years and not months. However, if drop-out and removals are used for the first few years and the last year, they should be small.
L, M and N
Section “L – CPP Changes From Age 65” is a table that is provided so that you can see the changes for each age from 60 to 70. Other sections of the spreadsheet use and equation instead of the table as it results in faster calculations. Section “M – Drop-down Menus” provides drop-down menus for the Drop-outs in the SOC section and the Options page because Excel requires the items be listed and then referenced. Section “N – Chart Titles” is a list of the titles used on the charts. Some of them change with changes to the numbered lines.
This section is on the Options page. Click on the Go to Options button in the top-right of the Entry page or the tab at the bottom of the page. It contains a summary of the results in lines 3 to 17 of the Entry page, provides an evaluation narrative and some charts.
The Options page changes whenever anything changes on the Entry page. It only shows either Option 1 or Option 2 depending on the values of the ages in columns C and D. If they are the same, it is Option 1. If they are different, it is Option 2.
The Options page contains the following data not included on the Entry page: survival probability and actual yearly payments of the “C – If Taken At” for Option 1 and “C – If PRB At” for Option 2 at the other ages as well as at the break-even ages.
There is a lot of information provided that summarizes the 4 key pieces of information you need to inform your decision as to when to take the CPP retirement pension for each sub-option:
- Yearly Payment for the years on line 5.
- Break-even ages for the b) and c) sub-options.
- Cumulated Payments for the year on line 15.
- Survival Probability for each age used.
Each option contains a drop-down menu where the Sex for Survival Probability can be set, a “Copy Option 1 Table” or a “Copy Option 2 Table” button and a “Go to Entry” button at the top.
Below that is a Narrative Summary and a Narrative Details which use data in the Table below to provide a summary of each sub-option. They are provided as an alternative to the table discussed below which might be a little hard to follow. The following is an example.
Below the Narratives is a Table that includes details from the Entry page organized by sub-option. The header includes the Inflation and Marginal Taxes rates. The columns are
- the Option sub-number,
- a Description (yearly, cumulated and break-even age),
- Actual Value,
- Adjusted Value and
- Survival Probability or Change.
In addition, the differences in dollars and percentage are provided to remove the need to so the subtraction yourself. For the b) and c) sub-options the Actual and Adjusted values are provided.
If you decide to evaluate both options, enter the data for Option 1 and Save the spreadsheet. Then change the data to Option 2 and Save As the spreadsheet with a different name. This is required because the income and drop-outs are different for each option.
Once completed, the two options can be compared by examining the Entry and Options pages of each spreadsheet. Alternatively, use the 1vs2 spreadsheet as described below. Make sure that the ages on line 5 are the same as follows: C must be the same for both options, Option 1’s C and D are the same, E for Option 1 must be the same as D for Option 2, E for Option 2 can be anything as it is not used, and F must be the same for both options.
This spreadsheet is downloaded using the link given above. Make sure you have spreadsheets for Option 1 and Option 2 with the proper ages as described above. Follow these instructions to populate the 1vs2 spreadsheet:
- 1. Open the 1vs2 spreadsheet.
- 2. Bring the Option 1 spreadsheet to the front.
- 2.1. Go to the Options page and click on the “Copy Option 1 Table” button.
- 2.2. Then bring the 1vs2 spreadsheet to the front and click on the “Paste Option 1” button.
- 3. Repeat 2 above for the Option 2 spreadsheet using the “Copy Option 2 Table” and the “Paste Option 2” buttons on the two spreadsheets.
If you chose to link the 1vs2 spreadsheet to the Option 1 and Option 2 spreadsheets, any changes in them will be updated on the 1vs2 spreadsheet. In this way, different ages, inflation and marginal taxes can be tried.
The 1vs2 spreadsheet has no enterable data. It contains 2 lines at the top that are used to determine if the tables have been pasted, a “Paste Option 1” button and a “Paste Option 2” button, Narratives, 3 charts and 3 tables.
Use the Narratives as an alternative to the charts and tables as they provide a nice summary of the differences between the 3 sub-options (1a versus 2a, 1b versus 2b and 3c versus 3c) as well as the 1a versus 2b. You can see the absolute and percent difference in the yearly payments and cumulated amounts, plus the break-even ages.
The Charts can be very instructive in showing the differences for each sub-option. See the Analysis post for examples of these charts.
Chart 1 shows the break-even ages for the sub-options. The actual ages will change depending on the ages that you use. They are color-coded to match the C, D, E and F columns of the source spreadsheets with Option 2 using a non-solid pattern.
Chart 2 shows the accumulated payments (in $K) at the age you selected for the same sub options above, plus starting values.
Chart 3 shows the Actual yearly payments for each Option at the specified ages for the sub-options indicated. This chart clearly shows the difference in payments
When the options are pasted the data are placed in areas below the summary table.
Please read the Disclaimer. In summary, the spreadsheets and this post provide you with general information only and does not give you advice on how to invest or manage your money. They have been prepared without taking into account your objectives, financial situation or needs. Before acting on this information you should consider its appropriateness, having regard to your own objectives, financial situation and needs.
The spreadsheets and post are provided with the understanding that the provider is not engaged in rendering legal, financial, accounting, tax or other professional services. If legal or other expert assistance is required, the services of a competent professional should be sought.
All efforts have been made to ensure the accuracy of the calculations in the spreadsheets, but the provider cannot be held responsible for errors that may occur. In addition, while initially most of the cells are locked on the spreadsheet you can easily unlock them so that if you make changes you could create errors. Use the email form on the Contact page if you find any errors or have suggestions.