Tax Table - Which Formulas

  • Thread starter Thread starter shyryih
  • Start date Start date
S

shyryih

Dear Professionals
I would like to calculate the tax on any amount with the
rate table details below

Amount Does not exceed 100,000---------Nil
Amount Exceeds 100K but does not exceed 250K---5% of value
Exceed 250K but Not exceed 500K----1250+3%value over 250K
Exceed 500K but does not exceed 1000K--8750+6% value over
500K
Exceed 1500K----68750+8%valueover1500K.

I tried using the VLOOKUP & If function but did not
succeed.
Many Thanks in Advance for any assistance received

Dominic Y
 
Hi Dominic!

I think that you've missed out a band:

Exceeds 1000K but does not exceed 1500K ??

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
One way using VLOOKUP

In Sheet1
-----------
Set-up the VLOOKUP table below in A1:D6

0...............0......0...........0
100000....5%...0............0
250000....3%...1250.....250000
500000....6%...8750.....500000
1000000...7%...10000...1000000
1500000...8%...68750...1500000

(Note: Presumed values have been inserted in B5:D5
for the "missing" band of 1M - 1.5M, as pointed out by Norman H.
Change this according to your specs)

Name the range A1:D6 as : TaxTable

If you have in Sheet2 (say):
"Values" in col A, "Tax" in col B,
with data from row 2 down

Put in B2:
=SUM(VLOOKUP(A2,TaxTable,2)*(A2-VLOOKUP(A2,TaxTable,4)),VLOOKUP(A2,TaxTable,
3))

Copy B2 down col B

Col B will return the tax
 
In Sheet1
-----------
Set-up the VLOOKUP table below in A1:D6

0...............0......0...........0
100000....5%...0............0
250000....3%...1250.....250000
500000....6%...8750.....500000
1000000...7%...10000...1000000
1500000...8%...68750...1500000
Click to expand...

Just a little clarification on the above table
(the table embeds the specs posted)

col A = "Taxable Amount" thresholds
col B = "Tax bracket" percentages
col C = "Base" tax amounts to be added
col D = Threshold amounts (for calculation of "Value above threshold")
 
A B C
1 $ 0.00 $ 0.00 0%
2 $100,000.00 $ 0.00 5%
3 $250,000.00 $1,250.00 3%
4 $500,000.00 $8,750.00 6%
5 $1,000,000.00 $30,000.00 7% (note: left out of
your email)
6 $1,500,000.00 $68,750.00 8%

In A9 Place target amount and in A10 insert below

=VLOOKUP(A9,A1:C6,2)+((A9-VLOOKUP(A9,A1:A6,1))*VLOOKUP(A9,A1:C6,3))
 
Back
Top