+ Reply to Thread
Results 1 to 3 of 3

Payroll sheet with Canada Pension Plan calculation that needs fine tuning

  1. #1
    Registered User
    Join Date
    01-30-2009
    Location
    Flin Flon, Manitoba
    MS-Off Ver
    Excel 2003
    Posts
    1

    Payroll sheet with Canada Pension Plan calculation that needs fine tuning

    I have set up my payroll spreadsheet so that all I have to do is enter hours worked and it will calculate the taxes, cpp , ei etc. I have the tax brackets working ok, my problem is the formulas for Canada Pension and Employment Insurance.

    CPP is calculated on earnings over $3,500 and stops calculating at a maximum of $46,300. I don't know how to start the calculation on a running total at $3,500 exactly - I can only get it to start in the cell that is greater than 3500 - ditto with ending at 46,300.

    The total maximum deduction for CPP in the year is 2118.60, so I entered a second column to do a final check and balance the amounts. I have attached a spreadhseet with the VERY ugly CPP and EI calculation, that I am hoping someone will be able to refine.

    The employment insurance column has the same problem, but I can fix that myself if someone can teach me the fix for the cpp (I'm like a very bright trained monkey!).

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Payroll sheet with Canada Pension Plan calculation that needs fine tuning

    Perhaps for Q11:

    =IF(OR(N($K11)<3500,N($K10)>=46200),0,((MIN($K11,46200)-3500)*4.95%)-SUM($Q$10:$Q10))
    copied down

    EDIT: the above uses 46200 as the upper threshold as per your file, however, your post states 46300 -- change the 46200 references as and if required.
    Last edited by DonkeyOte; 02-01-2009 at 03:56 PM. Reason: upper threshold discrepancy

  3. #3
    Registered User
    Join Date
    05-26-2011
    Location
    Edmonton, Alberta
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Payroll sheet with Canada Pension Plan calculation that needs fine tuning

    Did you ever get your ei, cpp calculations ever straightened out?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1