Salary Tax Formula,

  • Thread starter Thread starter NOORZAD
  • Start date Start date
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
 
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.
 
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.
 
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
Click to expand...

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
 
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)
 
Back
Top