Note: this tool was last updated on March 1st, 2024, to reflect the latest 2024 figures for max pensionable earnings (for CPP), and max benefit (for OAS)
Download the Excel version here (recommended!)
Download the Google Sheets version here
The Canadian government operates two main programs to provide financial assistance to Canadians who have reached retirement age. These programs are the Canada Pension Plan (CPP) program and the Old Age Security (OAS) program.
Let’s be clear – the CPP and OAS retirement programs will likely not be able to fund your living expenses in their entirety.
However, they do provide a great safety net. Canadians receiving benefits from these programs today are able to receive a maximum payment of over $20,000 per year.
Getting a good grasp on what these government benefits are and how they are calculated will help us get an accurate assessment of how prepared we are for retirement.
The Canada Pension Plan (CPP) is a program that working Canadians pay into during their career, and receive benefits from when they reach retirement age. Money is deducted directly from each paycheck, and the government in turn provides payments back to you when you reach retirement age.
Nearly all Canadians who work in the country contribute to this program (or the Quebec Pension Plan – QPP – if you work in Quebec).
The main features of the CPP program are:
Starting in 2019, the CPP program will begin to be ‘enhanced’. Working Canadians will begin to contribute a higher percentage of their income towards the CPP, and in exchange they will receive a higher CPP payment during retirement age.
The Canadian government also has a CPP calculator on their website; however, the advantages of using my calculator are:
This calculator does not currently include QPP calculations for those who work in Quebec; however, the CPP and QPP programs provide similar benefits, so the CPP calculations can be used as a proxy for what you’d receive from the QPP.
If you haven’t done so already, please download the tool. Links for the Excel / Google Sheets versions are at the top of this post; I’d recommend using Excel if possible. Navigate to the “CPP” tab of the spreadsheet. You’ll need to fill in a few assumptions, and the spreadsheet will then spit out the answer. Input your assumptions in any cell which is highlighted in yellow.
You will also need to input your income earned from age 18 up until the current year, starting on cell D34 of the spreadsheet.
The calculator will then forecast your future income based on the assumptions you entered earlier. As an optional feature, if you want to manually ‘override’ your forecasted income in a particular year, you can do that in column F (e.g., you can enter $0 for years in which you don’t plan on working).
You can also use the “Child-Rearing” drop out (CRDO) in column G. The CRDO allows Canadians to exclude certain low-earning years from their CPP calculation as long as the following rules are met:
The “Results” section of the spreadsheet will now give you your estimated CPP payment results. There is detail on the CPP amount you’d receive annually / monthly, your payment as a % of the max payment you could receive, how your payment would change based on if you started receiving it at age 60 up until 70, and the “optimal” age to start receiving your payment.
The optimal age shows you what age would maximize the present value of your future CPP payments, based on your assumed life expectancy and discount rate. Your ‘discount rate’ assumption represents how much less you value money in the future compared to money today. A 5% discount rate means that one dollar next year is worth 5% less to you than one dollar this year. I’d suggest using a discount rate of 3% to 7%, reflecting the annual investment return that could be earned on your money.
The OAS program is much simpler, and it is also much easier for Canadians to get the maximum benefit. The amount of money that you will earn from the OAS program in retirement age is based purely on the number of years in which you’ve lived in Canada. There is no requirement that you were earning income in any of those years.
The main features of the OAS program are:
Navigate to the “OAS” tab of the spreadsheet. Once again, fill in the assumptions in the cells which are highlighted in yellow.
After this, your OAS payment results will be shown in the “Results” section.
For further reading on these retirement benefit programs and instructions on how to apply (note that you need to apply to actually get your payments!), see these links for the CPP program and for the OAS program.
—
Many thanks and credits given to Doug Runchey, whose articles on the CPP program were an immense help in building this calculator.
Header image credit: Canada Currency
View Comments
Hi there.
I find this tool helpful and am looking to see if there will be updates for a 2021 version....I am copying and pasting the wording from a previous poster who put the ask in very good wording:
I’m checking in to see if it has been updated for 2021.
Also, if it’s not too much trouble, possible to outline what requires changing each year?
Thanks,
John
Hi John,
I've just updated the spreadsheet for the latest 2021 figures on max pensionable earnings (for CPP), and max benefit (for OAS).
Also, I've included notes on the "Change Log" tab about what I changed. These small tweaks need to be made each year.
I hope this helps!
Hi again.
Do you plan to update the tool at some point for us again ( please :) ).
John
I notice that in Version 6 the cell for the current year row (2022) is blank in Column K (Max annual pensionable earnings). This has the effect of making this a zero income year, regardless of the actual income you input in Column D. The end result appears to be that you lose one year of pensionable earnings (and possibly one of the best since it's the most recent).
Version 5 does not appear to have this issue.
I have noticed the same error in Version 6 (as well as Version 5). I assume if we fix column K by inserting a value that the results are still accurate?
Hi David,
Yes that's correct. Please note that I've updated the spreadsheet today with the latest 2022/2023 values, so a manual adjustment is no longer needed.
Hi Charles,
Apologies for the delay in this. I've just updated the spreadsheet for the latest 2022/2023 data.
The error you pointed out is now fixed.
Hi, Thanks for sharing this useful spreadsheet. I was wondering if you plan to update it for 2022.
Thanks
Hi Mark,
Apologies for the delay in this. I've just updated the spreadsheet for the latest 2022/2023 data.
Hi,
Hoping you will be updating the Canadian Retirement Benefits Calculator for 2022 / 2023 as it is a great asset in determining at which age to start collecting CPP and OAS.
Hi J.,
I've just updated the spreadsheet this morning, reflecting the latest data for 2022/2023.
Happy new year :)
Can you explain nominal vs. real dollars?
As I toggle back and forth, it changes the optimal age to receive CPP from 62 to 69.
Real dollars (or today's dollars) is the amount without taking into account inflation. Nominal dollars takes into account inflation. For example something that costs $100 today (real) might cost $120 (nominal) in the future due to inflation. (You'll note that the inflation rate is greyed out when Real is your setting)
I would expect the optimal age to not change however when switching between them so I'm wondering if this spreadsheet has an error.
Thanks for sharing this very useful spreadsheet.
I retired early (50) and now at 60 am deciding when to commence CPP. Your spreadsheet has been very helpful, especially with the math regarding "dropouts", given that I already have more than 10 years of zero contributions, so each year of delay adds another zero year to the calculations.
I have a question about whether the spreadsheet is correctly handling the CPP Enhancements for a person in my situation?
The spreadsheet shows that I can get much higher 5-year average max earnings if I defer commencement long enough to take advantage of the 7% and 14% enhancements in 2024 and 2025. That is pushing my optimal age of commencement up to 67, vs. only 63 if I remove the effect of enhancement (by entering 1.00 in cells C138 and C139.)
It surprises me that CPP Enhancement is affecting me at all: my last CPP contribution was years ago, before any of the CPP Enhancement provisions started. The official CPP "backgrounder" states: "The CPP enhancement will affect you only if you work and contribute in 2019 or later. If you are retired, not working, and not making contributions to the CPP, nothing will change and your CPP benefits will not increase."
To get more insight, I read the Doug Runcey description of the enhancements here: https://retirehappy.ca/enhanced-cpp/ which seems to support the view that enhancements won't help me. He breaks the calculation into Parts 1, 2, and 3, and if I understand correctly, only Part 1 will apply to people like me whose entire contribution history was pre-2019. The 7% and 14% increases would only apply to a new parameter,"YAMPE", which would only be used to calculate benefits for people who made enhanced contributions in 2019 or later.
Appreciate your thoughts about whether I'm interpreting that correctly, and therefore whether I should remove the effects of enhancement when I analyze my situation?
I downloaded the Excel file yesterday the annual payment for age 66 & 67 for CPP was showing #DIV/0!. I thought I had inadvertently changed a formula, so I redownloaded the file and entered my historical income again and it's still showing the error.
I have a more high level question. I assume that when you do your yearly "tweaks" you take into account the indexing of CPP/OAS based on inflation? Thank you for confirming.
Hi Dominic, yes confirmed that I am updating for those inflation tweaks when I do my yearly update.
I have just uploaded a new version of the spreadsheet with the 2024 values.
Thanks so much for the tool. I am inputting pensionable earnings from my Service Canada details and have a question what to enter when we get to enhancement years. Do I only enter the base portion of pensionable earnings or do I add base plus the enhancement additional portion? Thanks again and I really like the detail your tool provides. Great job!
Hi there,
I have plugged in all my pensionable earnings. The age 65 number i get is $93 greater then what government site says. Ok, that's not too far off. But then the age 70 number the calculator gives is 72% greater then the age 65 number it gave. Is there a way to figure out what is wrong? Should only be 42% bigger then the 65 number
Hi Allison,
Thanks for your message. There was an error in the sheet. It should be fixed now.
I have also updated the tool with the latest 2024 values for CPP max pensionable earnings.
Can you please try again and let me know if it's working for you?