CPP Calculator with 2016 Changes

December 16th, 2016 by Potato

“How much CPP will I get?” is a common question, and an important one that will help feed your financial planning. You can get a statement detailing your CPP contributions from Service Canada, but unless you’re close to retirement this isn’t super-helpful for planning purposes because it’s harder to do what-ifs with it. Indeed, it will basically assume that you’ll continue earning at your current rate until a given age, which is not helpful if you’re trying to evaluate early retirement scenarios. Plus, it’s a fair bit of work for precision when you may just want a good enough estimate. There’s also a calculator at Service Canada that includes the CPP, but it’s web-based so not great for playing around with, and it doesn’t do a good job of incorporating changes to future income, nor does it appear to be updated for the coming changes to CPP (announced in 2016).

So I’ve built a spreadsheet to let you do just that. Click here to download it (Excel). Keep reading for details on the calculations and how to use it.

A screenshot of the CPP calculator spreadsheet

Background and Acknowledgements

This came about because Sandi Martin came to me asking how to solve the problem of estimating the future CPP for people that would partially benefit from the changes to CPP announced in 2016 (which will be slowly implemented over the coming years). She had a spreadsheet for the old calculation, but I pretty much started from scratch to build this in. Having her sheet in front of me as I did so was helpful, and she was instrumental in the testing and trouble-shooting.

This post by Doug Runchey was hugely helpful on the algorithm CPP uses, which is not as intuitive as you would think. Doug offers a service to calculate your CPP precisely, so if you need to know down to the last dollar how much you’re going to get, contact Doug and pay him to run that calculation for you.

Instructions

Simply fill out the boxes shaded blue, then scroll down to see how much your CPP is. The year and age should be self-explanatory.

Dropout: You’re allowed to drop a certain number of years from your CPP calculation. This lets you have a few years of low/no earnings and still collect the maximum CPP. Right now you’re allowed to drop 17% of your working life (over at CPP it’s actually in months but the spreadsheet uses years). So the number of years that works out to depends on when you collect CPP. However, there are cases where you can use the child-rearing provision to drop more years — use this field to add those drop-out years, in percentage form. Note that if you’re adding years as a percentage, now you’re making an assumption on when you’ll take CPP, so the table at the bottom will have further approximations for you.

Income: Enter how much you earned each year towards CPP. For years before 2017 enter the actual amount in that year’s dollars. For years after 2017, use real dollars. That is, if you’re earning $50,000/yr now (just a bit under the YMPE), and next year expect to get a cost-of-living raise only, enter $50,000 for 2018 — you’ll still be at the same fraction of that year’s CPP maximum. If you’re expecting a 4% raise — 2% cost-of-living, and 2% real increase in pay, then enter that increased amount above inflation for the future year ($51,000 in this example). And that continues for future years: if 10 years from now you expect to just pace inflation, keep filling in the same salary figure in 2017 dollars.

Otherwise, have fun exploring your what-if scenarios for when you stop working, etc.

Results

Keep scrolling down past all the years to enter your income history, and you’ll find a little table with the results — how much you can expect to earn from CPP annually. This includes the bonus/penalty for waiting to take it/taking it early, so you can quickly see about how much you’ll get for taking it at different points.

Approximations

The calculation should give you your CPP benefit to the nearest 5% or so (several people have sent me their statements of CPP and even for those collecting under the old system, there are differences of ~1-3%). I’m happy with that level of good enough — after all, you can get about that much difference just from deciding whether to enter your age as of the beginning of the year or the end of the year. If you need more precision, lookup Doug Runchey’s service.

CPP is actually based on months of contributions (and months of drop-outs, etc.). Here I’ve just rounded off (discretized) to years, which is going to cause a bit of discrepancy. This will also cause a slight issue in the table of results for taking CPP at different ages, as whole years of drop-out pop up at 62 and 67 (vs. the more gradual inclusion of drop-outs when you discretize to months).

You can drop extra years for the child-rearing provision, however there are some extra rules about the dropping out that the spreadsheet doesn’t account for. If you add extra percentage drop-out for child-rearing, that’s not necessarily going to translate into the same years dropped out at each age for taking CPP, so it will be less accurate if you’re including extra drop-out years for disability or child-rearing. On top of that, there has been concern that the drop-out provisions wouldn’t apply to the enhancements, though with several years to the enhancement roll-out this may yet get patched. To assume the CBC article is how it will be (i.e., it won’t get patched), reduce your drop-out by ~33% for years after the full implementation.

The five-year average rule for determining the pension payout is hard to apply in the future where the inflation rate is unknown, so I’ve assumed an inflation rate similar to that of the last 5 years.

Commentary

I was actually somewhat shocked as I went through this at how mis-leading the initial government release on the CPP enhancement was. I had seen the “upper earnings limit will be targeted at $82,700” in all the news stories — which sounds substantially higher than today’s ~$55k figure. What I didn’t see was that this figure was not in today’s dollars, but in 2025 dollars, and that they assumed a rate of inflation close to 3% (well above the recent experience) — in today’s dollars, the new upper limit is actually just $62,586 (14% higher).

I was also surprised at some of the little things in the calculation, like that the payout is based not on the YMPE level when you start collecting, but a lower number (the average of the previous 5 years). I can’t fathom the point of this step of the calculation.

Otherwise, I haven’t seen explicit information on precisely how the CPP enhancements will be rolled out, but have made reasonable guesses as to how they will be pro-rated. What are the CPP enhancements, you ask? In short, an increase to the maximum amount you can contribute to CPP (so it will cover more of your income if you earn more than the maximum now), and an increase to the amount of income CPP will look to replace (from ~25% now to ~33% for those in the future). These were announced in the summer of 2016, and will start being phased-in in 2019 over the course of 7 years.

Updates

Version 2: Adjusted the age 66-70 calculations to keep using 48 years as the number of contributory years as part of the over-65 drop-out provision (see comments). Also rolled forward the calculations for 2017’s YMPE.

Once again, click here to download the CPP calculator (Excel). (click here for version 1).

10 Responses to “CPP Calculator with 2016 Changes”

  1. Harry Says:

    Hi Potato, this is great! I have 10-15 years to go and will use your spreadsheet for planning. Thanks for creating the template.

  2. Moi Says:

    Did not work in Open Office. :(

  3. Joe McNeill Says:

    Hey Potato,

    Thanks for providing this.

    I’ve found a possible bug though. Over in column Q, you list the YPME for each year. Shouldn’t it be adjusted for inflation going forward from 2016? I see you have it entered as $54,900 for all years going forward from 2016. It is already published as $55,300 for 2017.
    Without that adjustment, people retiring in future years will see a much lower figure than they’ll actually get.

    Thanks,
    Joe

  4. Potato Says:

    Hi Joe, for 2016 forward everything is in real dollars (i.e., constant 2016 dollars). That way your future CPP benefit is in understandable terms (it will be lower than the nominal dollars you’ll get, but then you don’t have to adjust some future year’s dollars back to today’s dollars). That’s why in the instructions, you put in your future earnings in 2016 dollars, and only increase it by future raises that are above inflation.

    Now that they’ve released the 2017 YMPE, I can update it in the new year to make the current year 2017 (or the user can do that on their copy), and then have everything forward in constant 2017 dollars.

  5. Joe McNeill Says:

    Scenario 1
    – I enter a “Client Age” of 65
    – I set the income to full YPME every year from age 18 until age 65. So the person should get max CPP.
    Max CPP in 2016 is $13,110.
    The CPP amount shown at age 65 (in 2016) is $13,106
    So this is correct.

    Scenario 2
    – I enter a “Client Age” of 60
    – I set the income to full YPME every year from age 18 until age 60. So the person should still get max CPP.
    Max CPP in 2020 based on inflation of 1.5% will be $14,123
    The CPP amount shown at age 65 (in 2020) is $13,106.
    I don’t think this is being calculated correctly. It should be $14,123.

  6. Joe McNeill Says:

    Sorry – my post at 10:13 PM crossed with your reply from 9:31 PM.

  7. Christmas Weekend Reading – $90,000 job or house, ways to save, cutting fees and dividend kings - My Own Advisor Says:

    […] Robertson built a Canada Pension Plan estimate calculator.  I just found a home for John’s tool on Helpful Sites page along with other easy to use […]

  8. fred Says:

    If you start CPP after age 65, I believe there is an additional drop-out provision called the over-65 dropout.
    So for example, if you are not working from age 65 to 70, I think
    you can also drop those 5 years as well which should result in a higher
    calculated amount.
    I don’t think the spreadsheet includes this additional drop-out

  9. Potato Says:

    Hi Fred, that was actually the first I had heard of the over-65 drop-out. I can’t find a single reference to it in a main article after a little while of googling, not on Service Canada, not on the CRA’s site; the only reference I’ve found has been in comments and forum posts (e.g., Doug Runchey’s comment here: http://www.michaeljamesonmoney.com/2013/04/when-should-you-start-collecting-cpp.html )

    So it sounds to me like that basically has the effect of capping the number of years of contribution used at 48 — working more beyond that will just push down other, lower-earning years, or have no effect. Version 2 incorporates this change.

    Once again, this highlights how crazy it is that the formula for this foundational retirement plan is so little-known, with basically just Doug Runchey being the main source of the algorithm for the world.

  10. In the world of Save. Spend. Splurge. | Save. Spend. Splurge. Says:

    […] Are you Canadian? Want to know how much the government may give you? Check out HP’s post on CPP calculations. […]