Income Tax: CF picks the correct tax for each employee from the table as the tax

[Magalela]

Good day everyone
Can you help me with a conditional formula. I have the attached tax table which I want to use in my created excel payroll.
How do I structure the formula so that it picks the correct tax for each employee from the table as the tax is dependant on the income?

The rates of Income Tax applicable are as follows:
RATES OF NORMAL TAX FOR INDIVIDUALS

Taxable Income Rates
Exceeds Does not exceed
0 100000 0 20% 0*******+ 20% of the excess of E0
100000 150000 20000 25% 20 000 + 25% of the excess of E100 000 *
150000 200000 32500 30% 32 500 + 30% of the excess of E150 000
200000 47500 33% 47 500 + 33% of the excess of E200 000

[AliGW]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. As you are new, I did it for you this time.)

[AliGW]

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

[Magalela]

Hi,
Sorry, I guess my question is vague.
I want to calculate tax for someone earns 88659.00 and another one earns 175500.00 and the last one earns 143589.00.
I need to create a formula that will just select the applicable tax from the table.

[Magalela]

Thank you very much. I hope I have joined a forum that will enrich me and the other members.

[AliGW]

Waiting for you to attach your sample workbook.

[Magalela]

Good day,
I'm not sure if the file has been attached.
Please confirm.

[Magalela]

The rates are annual not monthly. Please note that

[Magalela]

Good morning
Can someone help me with my plight on the pay as you earn.

[AliGW]

What would you expect the result in D14 to be?

[Magalela]

Hi,
I expect a result of 14916.67

[AliGW]

So please tell us the manual calculation you made to get that result - is this a tiered calculation?

[Magalela]

Good day,
Please find the attached filed with a view to simplifying my request for assitance.

[Magalela]

Not sure if the file is attached

[JeteMc]

Although this could be done without a helper column, I feel that adding one will be more computationally efficient.
The helper column (E14:E17) may be moved and/or hidden for aesthetic purposes and is populated using: =C14*12
The formula for D14 is: =SUM(INDEX(C$5:C$8,MATCH(E14,A$5:A$8)),INDEX(D$5:D$8,MATCH(E14,A$5:A$8))*(E14-INDEX(A$5:A$8,MATCH(E14,A$5:A$8))))
Let us know if you have any questions.

[bebo021999]

The Income Tax in D14 should be:

=IF(C14<=100000,C14*0.2,IF(C14<=150000,200000+(C14-100000)*0.25,IF(C14<=200000,32500+(C14-150000)*0.3,47500+(C14-200000)*0.33)))

could be shorten:

Please Login or Register  to view this content.

multifly with 12 to be Annual ICT

[Magalela]

Good morning
Thanks, this works perfectly.

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[Magalela]

Good morning
How do you deduct 8200.00 tax rebate from the formula and divide by 12 months to get a monthly tax?

[AliGW]

Start a new thread with an appropriate title, please. You can link back to this thread if you wish.

[AliGW]

I have asked you to start a new thread. As you have ignored me, I am closing this thread to force you to do so.