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
Hello. First, let me just say thanks for making this CPP calculator available.
I took my contribution values from the Version 7 of the spreadsheet and put them into this new Version 8, but I have a question about the D11 value. This is the "Age at which you start taking CPP". Does this value get used in calculations or is it just for highlighting the corresponding row in the Results section?
After I put in my contribution values it generated the Results for ages 60 to 70. But I noticed that if I changed the D11 value, those results also change. Perhaps I am misunderstanding something basic here, but I would not have expected the Results to have been recalculated differently as the same contribution values are still being used (and in Version 7 of the spreadsheet the Results do not change as I change D11).
For example, keeping all parameters the same except for D11, if I had D11 set as 65, I had results such as:
60 9526
61 10,596
62 11,670
etc
But if I changed D11 to 60, the results are now:
60 10,080
61 11,216
62 12,351
etc
I see the same thing. I am confused by that as well.
Also, the numbers I see for Version 8 are lower than for Version 7 with all the same input. I would have thought I'd see higher numbers with CPI adjustment.
Thank you
Hello, what if I received CPP Disability for 12 months in 2018. How do I account for this on the Spreadsheet?
Hello
How do I take into consideration CPP Disability income for 12 months in 2018?
Look forward to your response