Formula for calculating CPP [Canada Pension Plan deductions]

Hello All,

I am looking for a formula to calculate CPP deductions to the max amount allowable. My current formula is:


My fiscal year runs from Sept to August.

This sample is from January:


=IF((N7-3500/12)*4.95%*1<=2594,MAX((N7-3500/12)*4.95%,0),MAX(2594-(N7-3500/12)*4.95%*(1-1),0))

This issue I have is that once it reaches the max allowable amount it is either returning a value less or greater than the max

Any suggestions. I've attached a copy of the file.

Welcome back RBK2, it has been a while.
I assume that the CPP calculation for January is in cell N81.
I also assume that 624 is either more or less than what it should be.
It would probably help us to help you if:
1) You tell us the expected value for N81. Employee 1's expected values for each moth would help even more.
2) Explain the basic requirements of the calculation and why the (2-2) part in N81 changes to (4-2) in O81 and (3-1) in P81 etc.
Let us know if you have any questions.

Hi,

First of all thank you for your help. This has me baffled.

Yes the January CPP calculation is in cell N81. The 624 is bang on. For the employee in row 81 they should be at max of 2594 and for the employe in row 82 they should be at max 2594. There should be no total amount over the max amount. The monthly value we vary depending on their salary in row 7.

Basically I need to know how much CPP the employee should pay based on there monthly salary to a max CPP of 2594. Since the fiscal year runs from Sept to Aug I thought I had to divide by the number of pay periods 25 and identify the month that I was working with.

Not entirely sure I have the formula correct. I initially tried it with the following formula based on 12 months =IF((N7-$C$109/12)*$C$112*1<=$C$110,MAX((N7-$C$109/12)*$C$112,0),MAX($C$110-(N7-$C$109/12)*$C$112*(1-1),0)) and got the same results.

Basically I need to know how much CPP the employee should pay based on there monthly salary to a max CPP of 2594.

Then it would seem to me that the monthly amount should be the lesser of 4.95% of the employees monthly salary or the max CPP minus the cumulative amounts for that employee.
Paste the following into cell J81, drag the fill handle over to U81, then (while J81:U81 are still selected) drag down to U104:

Formula:

Please Login or Register  to view this content.

Notice that employees 1, 2 and 6 have a max yearly contribution of 2594, while no employee has a total contribution that is larger than 2594. The employees monthly contributions also vary directly with salary.
Let us know if you have any questions.

See if this will work.
Select cell J78 and type in the date for 1 September, 2017 as dictated by the date format of your computer (i.e. 9/1/17)
After pressing the Ctrl + Enter keys press Ctrl + 1 and select custom
Type mmmm in the 'Type:' box
Drag the fill handle of cell J78 over to cell K78
Change the date to 1 October, 2017
After pressing Ctrl + Enter, select both J78 and K78
Drag the fill handle over to cell U78
At this point the names of the months should be in row 78 although September may be displaying ******* until either J78 is enlarged or the font is reduced.
Now paste the following formula into cell J81: =IF(MONTH(J$78)<9,MIN(J7*$C$112,$C$110-SUM($I81:I81)),0)
After pressing the Ctrl + Enter keys, drag the fill handle over to U81, then (while J81:U81 are still selected) drag down to U104.
Let us know if you have any questions.

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread