CPP example

	A	B	C	D	E	F	G	H	I	K	L	M	O	P	Q
1
2	The set up:
3	I created a fictional earnings history, added in 2 kids with some part year parental leaves, and 4 years with some unemployment after age 45, in 2019 dollars
4	This person turns 18 in 2019 and is applying for benefits effective the day they turn 65
5	Since YMPE moves with average earnings, I have assumed that this person's earnings will too, making it easy to work in 2019 dollars
6	If you want to check details, the formulas can be found in the Canada Pension Plan Act: https://laws-lois.justice.gc.ca/eng/acts/c-8/page-1.html
7
8	Basic CPP benefit calculation
9	Step 1	Find your covered earnings to YMPE ratio for each year (maximum ratio is 1)
10	Step 2	Apply child-rearing provisions. Drop any ratios when children are under age 7 if you were the primary caregiver, if doing so raises your average ratio
11	Step 3	Flag years of lowest earnings, amounting to 17% of the remaining work history. At least 10 years (120 months) must remain.
12		In this column, years that are lowest are marked with a 0, and a partial year that will count towards averaging
13		Potential drop-out	6.46	years
14	Step 4	Obtain an average ratio from remaining years in the work history (ie. places no weight on lowest earnings years)
15		For this example, we have	0.748
16
17	Step 5	Find the basic benefit(annual basis) in 2019 dollars (25% * ratio * 5 year average of YMPE at the time of application)
18		$10,737.48	annually
19
20	Additional CPP benefit calculation - First additional benefit
21	Step 6	Apply child-rearing provisions. In effect, this takes an average of earnings prior to a child's birth and assumes that would go forward. See section 53.3 and 53.4 of the CPP Act
22		Note: not confident about the second child calculation here
23	Step 7	Define contribution period that counts in averaging - best 480 months, flag that with 1
24	Step 8	Obtain an average ratio for earnings up to the YMPE, based on the best 40 years
25		For this example, we have	0.736
26	Step 9	Find the first additional benefit (8.33% * average ratio * 5 year average of YMPE)
27		For this example, we have	$3,505.42	annually
28
29	Additional CPP benefit calculation - second additional benefit
30	In this example, earnings never exceed YMPE, so I have not added this explicitly in the calculations below.
31	Step 10	Find the Year's Additional Maximum Pensionable Earnings (section 18.1 of the CPP Act) as an increment over YMPE
32	Step 11	Find the ratio of earnings above YMPE to the increment (amount between YMPE and YAMPE)
33	Step 12	Find the best 40 ratios and average
34	Step 13	Find the second additional benefit (33.33% * average ratio* 5 year average of YAMPE-YMPE)
35		For this example, we have	$-	annually
36
37	Find the total CPP benefit
38	Step 14	Add up basic and additional benefits
39		$14,242.90	annually
40		$1,186.91	monthly
41
42	Average earnings over lifetime:		$36,408.51
43	Benefits/average earnings		39%
44
45
46	Note: benefits can be taken between ages 60 and 70, an adjustment factor would be applied to the benefit that they would be eligible for at 65.
47		(reduced by 6% per year before age 65 and increased by 7% per year after age 65)
48
49
50	Calculations:				Column numbers align with steps described above
51							Basic CPP calculation			First additional CPP calculations			Second additional CPP calculations
52					1	2	2	3	4	6	7		10	10	11
53	Year	Age	Earnings (2019 dollars)	YMPE (2019 dollars)	Ratio (earnings/YMPE)	Kids under 7?	Include after child provisions	Include after low earnings drop out	Weighted ratio	Ratios to YMPE with child provisions	Best 40 flags	weighted	YAMPE (2019)	Increment over YMPE	Ratio of earnings above YMPE/(YAMPE-YMPE)
54	2019	18	7600	57400	0.132404181		1	0	0	0.13240418	0	0	57400	0	0
55	2020	19	7600	57400	0.132404181		1	0	0	0.13240418	0	0	57400	0	0
56	2021	20	16000	57400	0.278745645		1	0.54	0.150522648	0.27874564	0	0	57400	0	0
57	2022	21	16000	57400	0.278745645		1	1	0.278745645	0.27874564	1	0.27874564	57400	0	0
58	2023	22	16000	57400	0.278745645		1	1	0.278745645	0.27874564	1	0.27874564	57400	0	0
59	2024	23	16000	57400	0.278745645		1	1	0.278745645	0.27874564	1	0.27874564	61418	4018	0
60	2025	24	16000	57400	0.278745645		1	1	0.278745645	0.27874564	1	0.27874564	65436	8036	0
61	2026	25	34000	57400	0.592334495		1	1	0.592334495	0.59233449	1	0.59233449	65436	8036	0
62	2027	26	34000	57400	0.592334495		1	1	0.592334495	0.59233449	1	0.59233449	65436	8036	0
63	2028	27	34000	57400	0.592334495		1	1	0.592334495	0.59233449	1	0.59233449	65436	8036	0
64	2029	28	5000	57400	0.087108014	Yes (1st born)	0	0	0	0.46689895	1	0.46689895	65436	8036	0
65	2030	29	34000	57400	0.592334495	Yes	0	0	0	0.59233449	1	0.59233449	65436	8036	0
66	2031	30	5000	57400	0.087108014	Yes (2nd born)	0	0	0	0.56724739	1	0.56724739	65436	8036	0
67	2032	31	40000	57400	0.696864111	Yes	0	0	0	0.69686411	1	0.69686411	65436	8036	0
68	2033	32	40000	57400	0.696864111	Yes	0	0	0	0.69686411	1	0.69686411	65436	8036	0
69	2034	33	40000	57400	0.696864111	Yes	0	0	0	0.69686411	1	0.69686411	65436	8036	0
70	2035	34	40000	57400	0.696864111	Yes	0	0	0	0.69686411	1	0.69686411	65436	8036	0
71	2036	35	45000	57400	0.783972125	Yes	0	0	0	0.78397213	1	0.78397213	65436	8036	0
72	2037	36	45000	57400	0.783972125	Yes	0	0	0	0.78397213	1	0.78397213	65436	8036	0
73	2038	37	45000	57400	0.783972125		1	1	0.783972125	0.78397213	1	0.78397213	65436	8036	0
74	2039	38	45000	57400	0.783972125		1	1	0.783972125	0.78397213	1	0.78397213	65436	8036	0
75	2040	39	45000	57400	0.783972125		1	1	0.783972125	0.78397213	1	0.78397213	65436	8036	0
76	2041	40	49000	57400	0.853658537		1	1	0.853658537	0.85365854	1	0.85365854	65436	8036	0
77	2042	41	49000	57400	0.853658537		1	1	0.853658537	0.85365854	1	0.85365854	65436	8036	0
78	2043	42	49000	57400	0.853658537		1	1	0.853658537	0.85365854	1	0.85365854	65436	8036	0
79	2044	43	49000	57400	0.853658537		1	1	0.853658537	0.85365854	1	0.85365854	65436	8036	0
80	2045	44	49000	57400	0.853658537		1	1	0.853658537	0.85365854	1	0.85365854	65436	8036	0
81	2046	45	50000	57400	0.871080139		1	1	0.871080139	0.87108014	1	0.87108014	65436	8036	0
82	2047	46	10000	57400	0.174216028		1	0	0	0.17421603	0	0	65436	8036	0
83	2048	47	50000	57400	0.871080139		1	1	0.871080139	0.87108014	1	0.87108014	65436	8036	0
84	2049	48	50000	57400	0.871080139		1	1	0.871080139	0.87108014	1	0.87108014	65436	8036	0
85	2050	49	10000	57400	0.174216028		1	0	0	0.17421603	0	0	65436	8036	0
86	2051	50	50000	57400	0.871080139		1	1	0.871080139	0.87108014	1	0.87108014	65436	8036	0
87	2052	51	50000	57400	0.871080139		1	1	0.871080139	0.87108014	1	0.87108014	65436	8036	0
88	2053	52	10000	57400	0.174216028		1	0	0	0.17421603	0	0	65436	8036	0
89	2054	53	50000	57400	0.871080139		1	1	0.871080139	0.87108014	1	0.87108014	65436	8036	0
90	2055	54	50000	57400	0.871080139		1	1	0.871080139	0.87108014	1	0.87108014	65436	8036	0
91	2056	55	10000	57400	0.174216028		1	0	0	0.17421603	0	0	65436	8036	0
92	2057	56	50000	57400	0.871080139		1	1	0.871080139	0.87108014	1	0.87108014	65436	8036	0
93	2058	57	50000	57400	0.871080139		1	1	0.871080139	0.87108014	1	0.87108014	65436	8036	0
94	2059	58	50000	57400	0.871080139		1	1	0.871080139	0.87108014	1	0.87108014	65436	8036	0
95	2060	59	50000	57400	0.871080139		1	1	0.871080139	0.87108014	1	0.87108014	65436	8036	0
96	2061	60	50000	57400	0.871080139		1	1	0.871080139	0.87108014	1	0.87108014	65436	8036	0
97	2062	61	50000	57400	0.871080139		1	1	0.871080139	0.87108014	1	0.87108014	65436	8036	0
98	2063	62	50000	57400	0.871080139		1	1	0.871080139	0.87108014	1	0.87108014	65436	8036	0
99	2064	63	50000	57400	0.871080139		1	1	0.871080139	0.87108014	1	0.87108014	65436	8036	0
100	2065	64	50000	57400	0.871080139		1	1	0.871080139	0.87108014	1	0.87108014	65436	8036	0