Formula to define tax based on salary. Excel formula.

Hi,

Formula to define tax based on salary. Excel formula.

Attached excel

Regards
sharath

Tax.xls
LVL 11
bsharathAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RunriggerCommented:
please advise specifically what it is you meed to know;

Are the tax rates applicable on all of the salary if is triggers a threshold or the parts of the salary fallling in to the bandings;

e.g. a salary of say 160000, is all 160000 taxed at 5% or is it 150000 @ 0% + 10000 @ 10%?

I am of course assuming that your value 1,50,000 = 150000 in UK/US format!
0
bsharathAuthor Commented:
Salary      To      Tax
1,50,000            0%
1,50,000      300,000      5%
300,000      500,000      10%
500,000      1,000,000      15%
1,000,000      >      20%

The formula when placed has to calculate the colum A amounts and place the Tax
0
jkunreinCommented:
There are still a couple of things that we're unclear about.

1. Does the tax apply to everything evenly, or is each band taxed (which I think is Runrigger's question).
2. What is the break point?  You are saying that 300,000 is taxed at both 5% and 10%.  

If #1 applies to everything evenly, and you are assuming 0 to 150k with >150k to 300k, then this formula will work:

=IF(A1>1000000, A1*0.2, IF(A1>500000, A1*0.15, IF(A1>300000, A1*0.1, IF(A1>150000, A1*0.05, 0))))
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

jkunreinCommented:
On the other hand, if you are taxing someone 5% for the income over 150k but not at 300k and 10% for income over 300k but not 500k, then this formula may be more of what you need:

=IF(A1>1000000,(A1-1000000)*0.2,0)+IF(A1>500000,MIN(500000, (A1-500000))*0.15,0)+IF(A1>300000,MIN(200000, (A1-300000))*0.1,0)+IF(A1>150000,MIN(150000, (A1-150000))*0.05,0)

If this still doesn't help, please provide some examples of what you want the final number to be.
0
bsharathAuthor Commented:
Thanks i think it works
Can i know what are these mentioning to
*0.2,0
*0.15,0

0
tilsantCommented:
to get 20% of something.....we multiply it be 0.2 this accounts for "*0.2"
and i the syntax for IF formula is:

IF(logical_test,value_if_true,value_if_false)

Logical_test    is any value or expression that can be evaluated to TRUE or FALSE. For example, A10=100 is a logical expression; if the value in cell A10 is equal to 100, the expression evaluates to TRUE. Otherwise, the expression evaluates to FALSE.


in your case for "IF(A1>1000000,(A1-1000000)*0.2,0)" means:
if the value in A1 > 1000000 then the result will be 20% of the difference and if the value is less than 1000000 then the result will be 0.



tils.
0
jkunreinCommented:
Tils has explained it spot-on.

I really should have elaborated on the formula, and I apologize for that.  I was in a bit of a hurry when I posted that.  

It may help to look at the formula backwards.  It actually generates four numbers and sums them together.  A lot of these numbers will be zero.

The last number generated is:
IF(A1>150000,MIN(150000, (A1-150000))*0.05,0)

So, if your salary is less than or equal to 150k, the last number is applied (zero).  If it is greater than 150k, then the MIN function is evaluated.  I used the MIN function because you obviously don't want to take 5% of the entire salary, since everything less than 150k is not taxed.  So, you examine the difference between the salary and the threshold (A1-150000).  But, you are only applying 5% to the next 150k up to the next threshold of 300k.  If the salary is over 300k, then you want to apply 5% to only the portion between 150k and 300k.  So, the 5% is applied to either (salary - 150k) or (300k - 150k), whichever is smaller.  

The third number is:
 IF(A1>300000,MIN(200000, (A1-300000))*0.1,0)

The concept is still the same here.  If the salary is not greater than 300k, then this generates zero, so it won't add to your final tax.  Otherwise, it applies 10% to everything above 300k.  Since you have an upper limit at 500k, the MIN function again chooses between (salary - 300k) or (500k - 300k), whichever is smaller.  

And this continues on up the line.  So, some examples you'd get is:
Salary: 100k; Tax: 0 + 0 + 0 + 0
Salary: 200k; Tax: 0 + 0 + 0 + 50k*5%
Salary: 900k; Tax: 0 + 400k*15% + 200k*10% + 150k*5%
Salary: 2000k; Tax: 1000k*20% + 500k*15% + 200k*10% + 150k*5%

I hope that explains the formula better for you.
0
byundtMechanical EngineerCommented:
Assuming that each band is taxed at the rates given, you could use:
=SUM((A1>{150000,300000,500000,1000000})*(A1-{150000,300000,500000,1000000})*0.05)

This formula takes advantage of the fact that the bands are multiples of 5%. In essence, it taxes that portion of the salary above 150000 at 5%; that portion above 300000 an additional 5%; that portion above 500000 yet another 5%; and that portion above 1,000,000 yet one more 5%.

Brad
0
byundtMechanical EngineerCommented:
I see that your original problem skipped the 10% and 20% tax brackets. If so, my previous suggestion should be modified to:
=SUM((A2>{150000,180000,180000,200000,200000,250000})*(A2-{150000,180000,180000,200000,200000,250000})*0.05)
The above type of formula requires the incremental tax rates to be 1, 2, 3, etc times a constant amount. To skip an increment, just repeat its place in the table.

Using a three column table like presented in http:/#a28466527.html you can use a general formula like:
=SUMPRODUCT(((A2>INDEX(Brackets,,1))*(A2-INDEX(Brackets,,1))-(A2>INDEX(Brackets,,2))*(A2-INDEX(Brackets,,2)))*INDEX(Brackets,,3))

This formula will work no matter how many tax brackets there are, and no matter what the incremental tax rates.

Brad
IncomeTaxBracketsQ25500789.xls
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bsharathAuthor Commented:
Thank U
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.