Spreadsheet: 851534d1701746185-additional-ccp2-deduction-formula-not-working-correctly-payroll-cpp-calculations
Sheet: Sheet1

 | Payroll Sheet
 | Example 1
 | Name |  |  |  |  |  |  |  | Name
 | Period From |  |  | Period To |  | Check Date |  |  | Period From |  |  | Period To |  | Check Date
 | 2024-01-07 00:00:00
 |  |  | Hours | Income | Deductions | Current | YTD |  |  |  | Hours | Income | Deductions | Current | YTD
 | Regular Pay |  | 40 |  |  |  |  |  | Regular Pay |  | 40
 | Total Gross Pay |  |  |  |  |  |  |  | Total Gross Pay
 | CPP1 |  |  |  |  |  |  |  | CPP1
 | CPP2 |  |  |  |  |  |  |  | CPP2
 | Income tax |  |  |  |  |  |  |  | Income tax
 | Total Deductions |  |  |  |  |  |  |  | Total Deductions
 | Pay Rate |  | 16.55 |  | Net Pay |  |  |  | Pay Rate |  | 16.55 |  | Net Pay
 | CPP1 Deduction: |  |  |  |  |  |  |  | [NAME]:
 | $3,500.00 Basic Exemption |  |  |  |  |  |  |  | Starts after reached CPP1 limit of Total Gross Pay $68,500.00
 | $[NAME]. Gross Income for the deduction |  |  |  |  |  |  |  | $73,200  [NAME]. Gross Income for the deduction
 | $3,500.00 + $65,000.00 = $68,500.00 Total Gross Income |  |  |  |  |  |  |  | Rate 4.0% on Gross Income above Total Gross Income $68,500.00
 | Rate 5.95% on Gross Income after basic exemption |  |  |  |  |  |  |  | [NAME]. Contribution $188.00. ($73,200.00 X 4.0%).
 | [NAME]. Contribution $3,867.50. ($65,000.00 X 5.95%). |  |  |  |  |  |  |  | Same Pay Period Bi-Weekly
 | Pay Period Bi-Weekly, $3,500.00/26=134.61 exemption in each pay period
 | CPP1 [NAME] works OK |  |  |  |  |  |  |  | [NAME] is not correct
 | Example 2
 | Name |  |  |  |  |  |  |  | Name
 | Period From |  |  | Period To |  | Check Date |  |  | Period From |  |  | Period To |  | Check Date
 | 2024-01-07 00:00:00
 |  |  | Hours | Income | Deductions | Current | YTD |  |  |  | Hours | Income | Deductions | Current | YTD
 | Regular Pay |  | 80 |  |  |  |  |  | Regular Pay |  | 80
 | Total Gross Pay |  |  |  |  |  |  |  | Total Gross Pay
 | CPP1 |  |  |  |  |  |  |  | CPP1
 | CPP2 |  |  |  |  |  |  |  | CPP2
 | Income tax |  |  |  |  |  |  |  | Income tax
 | Total Deductions |  |  |  |  |  |  |  | Total Deductions
 | Pay Rate |  | 16.55 |  | Net Pay |  |  |  | Pay Rate |  | 16.55 |  | Net Pay
 | CPP1 deduction on $1,324.00 - $134.61 = $1,189.39 X 5.95% = $70.77 |  |  |  |  |  |  |  | Same CPP1 deduction as it doesn't reach [NAME]. Limit of $3,867.50
 | [NAME] requied as Gorss Income is less than $68,500.00
 | Example 3
 | Name |  |  |  |  |  |  |  | Name
 | Period From |  |  | Period To |  | Check Date |  |  | Period From |  |  | Period To |  | Check Date
 | 2024-01-07 00:00:00
 |  |  | Hours | Income | Deductions | Current | YTD |  |  |  | Hours | Income | Deductions | Current | YTD
 | Regular Pay |  | 80 |  |  |  |  |  | Regular Pay |  | 80
 | Total Gross Pay |  |  |  |  |  |  |  | Total Gross Pay
 | CPP1 |  |  |  |  |  |  |  | CPP1
 | CPP2 |  |  |  |  |  |  |  | CPP2
 | Income tax |  |  |  |  |  |  |  | Income tax
 | Total Deductions |  |  |  |  |  |  |  | Total Deductions
 | Pay Rate |  | 410 |  | Net Pay |  |  |  | Pay Rate |  | 410 |  | Net Pay
 |  |  |  |  |  |  |  |  | CPP1 deduction limit $3,867.50 reached
 |  |  |  |  |  |  |  |  | Here, [NAME]. Gross Income doesn't reach, but due to exemption amount is $134.61
 |  |  |  |  |  |  |  |  | [NAME]. Contribution limit reached, so CPP1 deduction is less than previous period.
 |  |  |  |  |  |  |  |  | [NAME] doesn't reach, so it should not deduct, CPP2 deduction is not correct.
 | Example 4
 | Name |  |  |  |  |  |  |  | Name |  |  |  |  |  |  |  | Name
 | Period From |  |  | Period To |  | Check Date |  |  | Period From |  |  | Period To |  | Check Date |  |  | Period From |  |  | Period To |  | Check Date
 | 2024-01-07 00:00:00
 |  |  | Hours | Income | Deductions | Current | YTD |  |  |  | Hours | Income | Deductions | Current | YTD |  |  |  | Hours | Income | Deductions | Current | YTD
 | Regular Pay |  | 88 |  |  |  |  |  | Regular Pay |  | 88 |  |  |  |  |  | Regular Pay |  | 88
 | Total Gross Pay |  |  |  |  |  |  |  | Total Gross Pay |  |  |  |  |  |  |  | Total Gross Pay
 | CPP1 |  |  |  |  |  |  |  | CPP1 |  |  |  |  |  |  |  | CPP1
 | CPP2 |  |  |  |  |  |  |  | CPP2 |  |  |  |  |  |  |  | CPP2
 | Income tax |  |  |  |  |  |  |  | Income tax |  |  |  |  |  |  |  | Income tax
 | Total Deductions |  |  |  |  |  |  |  | Total Deductions |  |  |  |  |  |  |  | Total Deductions
 | Pay Rate |  | 250 |  | Net Pay |  |  |  | Pay Rate |  | 300 |  | Net Pay |  |  |  | Pay Rate |  | 250 |  | Net Pay
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | CPP2 deduction sholud be, ($70,400-$68,500) X 4.0% = 76.00
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | CPP2 deduction is not correct.