Salary Tax Formula,

N

NOORZAD

Dears,

If any one can help me to write a formula for the following salary tax:

From $1 to $5,000 tax should be 0, from $5,001 to $12,500 Tax should be
charged 2%.

From $12,501 to $100,000 tax should be 10%.

From more than 100,000 the tax should be 20%.

Thanks in Advance,

Regards,
KN
 
N

NOORZAD

Dear Steve,

I want the formula to show the amount of money charged as a tax on an
idividual like: the salary for David is $20,000 now how much is the tax for
him as it is between $12,501 and $100,000 the percentage of tax which should
be charged on him is 10%.

Regards,

KN.
 
R

Rich/rerat

NOORZAD,
You can use the following formulas in Cell (C2), if the salary is in Cell
(A2):

=ROUND($A2*LOOKUP($A2,{0,5001,12501,100000},{0,0.02,0.1,0.12}),2)
OR
=ROUND($A2*LOOKUP($A2,{0,5001,12501,100000},{0,2,10,12})/100,2)

And format Column (C) as:
"0.00" (w/o quotes)
--
Add MS to your News Reader: news://msnews.microsoft.com
Rich/rerat
(RRR News) <message rule>
<<Previous Text Snipped to Save Bandwidth When Appropriate>>


Dear Steve,

I want the formula to show the amount of money charged as a tax on an
idividual like: the salary for David is $20,000 now how much is the tax for
him as it is between $12,501 and $100,000 the percentage of tax which should
be charged on him is 10%.

Regards,

KN.
 
R

Ron Rosenfeld

Dears,

If any one can help me to write a formula for the following salary tax:

From $1 to $5,000 tax should be 0, from $5,001 to $12,500 Tax should be
charged 2%.

From $12,501 to $100,000 tax should be 10%.

From more than 100,000 the tax should be 20%.

Thanks in Advance,

Regards,
KN

Set up a table someplace that looks like:

$ 5,000 0.00 2%
$12,500 $ 150.00 10%
$100,000 $8,900.00 20%

Note that column 2 is the total tax on the salary in column 1.

Then use this formula:


=VLOOKUP(Salary,TaxTbl,2)+(Salary-VLOOKUP(Salary,TaxTbl,1))*VLOOKUP(Salary,TaxTbl,3)

TaxTbl can be either a named range, or a cell reference to where you have the
3x3 table.
--ron
 
A

AltaEgo

Noorzad,

In some countries you need to round currency to the nearest 5c. If this is
the case, you will find the mround function useful. To use mround, you need
to install the Analysis Toolpack add-in. Rich/rerat's formula would become:

=MROUND($A2*LOOKUP($A2,{0,5001,12501,100000},{0,0.02,0.1,0.12}),0.05)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top