Cpp Ei Fed and ON stub

[top1]

Hi

I need a formula which works as below criteria. I need biweekly pay period CPP deduction

Year's Maximum Pensionable Gross Earnings - $47,200.00
Biweekly Basic Exemption - $3,500.00/26 = 134.62
Contribution Rate (employee) - 4.95%
Annual Maximum Contribution (employee) - $2,163.15


Pls see attached file.

Thanks

[martindwilson]

CPP? what is CPP?

[top1]

CPP? what is CPP?

Canadian Pension Plan.

[Cutter]

I believe you want:

In E2:
=ROUND((D2-ROUND(3500/26,2))*0.0495,2)

In F2
=E2

In F3
=MIN(2163.15,F2+E3)

[Cutter]

The formula for E2 will give you a result of $37.66

That amount is verified by using the CRA Online Payroll Calculator
https://apps.cra-arc.gc.ca/ebci/rhpd...o?lang=English

[top1]

I believe you want:

In E2:
=ROUND((D2-ROUND(3500/26,2))*0.0495,2)

In F2
=E2

In F3
=MIN(2163.15,F2+E3)

Thanks Cutter. It solved my one problem.

Now Please If you can help me to determine Employee E.I and Employer E.I. and Ontario and Federal TAX for payroll. that would be great.

Employee E.I
* Maximum Annual Insurable Earnings - $42,300.00
* Premium/Contribution Rate (employee) - 1.73%
* Annual Maximum Premium (employee) EI - $747.36
Employer contribution rate - 2.492%
Annual Employer maximum - 1101.46

Provincial and Federal tax rate can be found on this website:
http://www.cra-arc.gc.ca/tx/ndvdls/fq/txrts-eng.html

Thanks

[Cutter]

What year are you interested in?
Your original post gives figures for 2010 CPP.
Your latest request gives 2010 EI numbers BUT the max for 2010 was 43,200 not 42,300
The link you give for tax rates is for 2011.

The calculations for Fed and Prov taxes is a lot more complex than just having the tax rates. There are a lot of factors that come into play and I can tell you that when I did those formulas for a file I created a few years ago they turned out to be the longest I have ever written. The basis for the formulas was from this site

http://www.cra-arc.gc.ca/E/pub/tg/t4127-jan/README.html

From Page 23 on in the PDF

[top1]

Hi Cutter
I need this for 2011. If you can help me to determine EI and TAX it would be great help for me.

Also I tried you suggested formula for CPP. It really work well. But How the CPP deduction stop to count in CPP column( Not YTD column) when it reach the maximum limit. I need that when CPP max reach, the cell appears Zero. Pls let me know any suggestion.

Thanks

[Cutter]

Based on the gross pays listed in your sample you won't have to be concerned with the maximums but to account for them anyway do this:

Change the formula in E3 to:

=MIN(ROUND((D3-ROUND(3500/26,2))*0.0495,2),2217.6-F2)

and in F3 to:

=F2+E3

and drag them down

For EI in G2 put this: =ROUND(D2*0.0178,2)

In G3 this: =MIN(ROUND(D3*0.0178,2),786.76-H2) and drag down

In H2 this: =G2

In H3 this: =H2+G3

As for the Fed/Prov taxes I'll refer you to my previous post regarding that. Go to the linked PDF and follow the formulas as detailed from page 23 onward.

[top1]

Hi cutter, I went through your suggested site. I am not getting anything about how to tax deduct provincial and federal. Pls help me. Thanks

[Cutter]

Here is what I have done so far. I assumed you did the Minimum Claim Code (Form TD1) and that you have NO extra deductions on your pay (RRSP, Pension, Union Dues, Alimony).

I have not done the Provincial Tax. It is more complex thanks to Mr McGuinty and his OHIP premiums. If I get time I'll come back to it but you should make an effort on your own based on what I did for the Fed Tax.

The tax results from the formula I have used produce the same results as the CRA online calculator (which I provided a link for in an earlier post).

[top1]

Hi Cutter,
Thanks for help me out. I will try myself to figure out Province tax. But your work is beyond my mind intelligence. Anyway If I am able to figure (IF I COULD) out the last problem, I will inform u.

[top1]

Hi cutter,

I tried to figure out the provincial tax for last 2 days, not getting any success.

[Cutter]

Sorry to hear that you have had no success with the Ontario tax.

I had a look at the formula that I used for my own file a couple of years ago and it occupies 19 1/2 lines in the formula bar. Like I said earlier - the longest formula I have ever written.

Before I spend any more time on this I need to know if the online calculator (for which I've already provided the link) gives you the expected result when using just the basic information (no extra deductions, minimum claim codes).

And I assume you are using this just to verify figures provided on your pay stub - is that correct?

[top1]

Yes I want to verify my paystub.

[Cutter]

That answers the second question I asked - and the online calculator accomplishes that.

The first question I asked was: Does the online calculator, without inputting any additional deductions AND using minimum claim codes, produce the same results as your pay stub? If so, I can test the formula against its results. If not, your employer's method of calculation may be incorrect (or may use another method) in which case this exercise would be a waste of time.

[top1]

Hi Cutter:

Yes, the online calculator gives me the same amount as of on my pay stub.

[top1]

Hi Cutter,

Are u getting any success for provincial tax?

[top1]

I need some help in determine the provincial tax for Ontario Canada. If any one can help me out there.

[Cutter]

I put this on the back burner for a couple of reasons - the main one being that your request is a 'want', not a 'need' and the online calculator fulfills that 'want' in the interim.

I will try to give it some time this weekend - possibly during the Daytona 500.

[Cutter]

Here you go. I have broken the Ont Tax down into 3 separate cells. The T4 value, the OHIP value and the T2 value (which is the one you see on your pay stub). This simplifies the overall calculation a great deal. To further simplify it I have omitted factors that you are unlikely to need giving consideration to (but I have placed zeros in the formula just to let you know that other factors could come into play and show you where they would go).

The T4 and OHIP columns can be hidden if you want so that you only see numbers that would appear on your pay stub (but it's interesting to see the OHIP non-tax tax!).

Of the 3 pays in your sample file the first and 3rd are to the penny as per the online CRA calculator. The 2nd one is off by 1 cent.

Next year you will have to adjust the numbers highlighted in blue on the Fed and Ont sheets when they become available.

Finally, you should change the title of this thread to "Formulas for Ontario payroll deductions" to aid someone who may do a search for same.

[top1]

THANKS CUTTER. Ur genious.

[letzgowild]

New.xlsx
O.K
I have looked at this and cannot get the provincial tax to work properly. I have attached the same document with a few minor adjustments. I added the 2013tax year changes as you indicated in the "blue" fields. Rate of pay is 30./hr. Everything works flawlessly but that darn Provincial code will not work with a 52 week schedule instead of 26 weeks.
Can you PLLLEEAAASSSEEE help? Thank you in advance!!!
Letzgowild

[martindwilson]

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.