Land Tax Calculation formula and table. Can't see where I'm going wrong. Please help

[matwright06]

Hi All,

I'm attempting a formula that calculates land tax. For a property price of $289,000 I keep ending up with a value of $275.78 but I believe the right figure is $353.00

Column D has my property prices so this example is using cell D12 with a property price of $289,000

BTW - My Formula is in cell Y12 if that's relevant.

=LOOKUP(D12-0.01,Data!$I$2:$I$7,Data!$J$2:$J$7)+LOOKUP(D12-0.01,Data!$I$2:$I$7,Data!$K$2:$K$7)*(ROUNDUP(D12,-2)-LOOKUP(D12-0.01,Data!$I$2:$I$7))/100

I have a sheet called "Data" where I have created the below table.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Land Tax Calculator[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]00[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]250,000[/TD]
[TD="align: center"]275[/TD]
[TD="align: center"]0.2%[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]600,000[/TD]
[TD="align: center"]975[/TD]
[TD="align: center"]0.5%[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]1,000,000[/TD]
[TD="align: center"]2,975[/TD]
[TD="align: center"]0.8%[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]1,800,000[/TD]
[TD="align: center"]9,375[/TD]
[TD="align: center"]1.3%[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]3,000,000[/TD]
[TD="align: center"]24,975[/TD]
[TD="align: center"]2.25%[/TD]
[/TR]
</tbody>[/TABLE]

The schedule of land tax rates is below
[TABLE="width: 517"]
<tbody>[TR]
[TD]Total taxable value of landholdings[/TD]
[TD]Land tax payable[/TD]
[/TR]
[TR]
[TD] < $250,000[/TD]
[TD]Nil[/TD]
[/TR]
[TR]
[TD]$250,000 to < $600,000[/TD]
[TD]$275 plus 0.2% of amount > $250,000[/TD]
[/TR]
[TR]
[TD]$600,000 to < $1,000,000[/TD]
[TD]$975 plus 0.5% of amount > $600,000[/TD]
[/TR]
[TR]
[TD]$1,000,000 to < $1,800,000[/TD]
[TD]$2,975 plus 0.8% of amount > $1,000,000[/TD]
[/TR]
[TR]
[TD]$1,800,000 to < $3,000,000[/TD]
[TD]$9,375 plus 1.3% of amount > $1,800,000[/TD]
[/TR]
[TR]
[TD]$3,000,000 and over[/TD]
[TD]$24,975 plus 2.25% of amount > $3,000,000[/TD]
[/TR]
</tbody>[/TABLE]

It would be great if some gun could run a quick eye over it and offer up a suggestion. Thanks for your help again guys.

Mat

 

[Scott T]

This should work

=VLOOKUP(D12,Data!I2:J7,2)+(D12-VLOOKUP(D12,Data!I2:I7,1))*VLOOKUP(D12,Data!I2:K7,3)

 

[Michael M]

I used...on Sheet1

Excel 2007
	A	B	C
1	Land Tax Calculator
2	0	0	0
3	250,000	275	0.20%
4	600,000	975	0.50%
5	1,000,000	2,975	0.80%
6	1,800,000	9,375	1.30%
7	3,000,000	24,975	2.25%
8
9	Property Price	$ 289,000.00
10
11
12
13	Tax	$ 353.00
Sheet3

Cell Formulas
Range	Formula
B13	=LOOKUP(B9,A2:A7,B2:B7)+(B9-LOOKUP(B9,A2:A7,A2:A7))*LOOKUP(B9,A2:A7,C2:C7)

 

[dfsobral]

I hope you are sure of your understanding of the table
anyway try it

=VLOOKUP(D12,$I$2:$K$7,2)+(D12-VLOOKUP(D12,$I$2:$K$7,1))*VLOOKUP(D12,$I$2:$K$7,3)

 

[Dave Patton]

Did you try suggestions that you received in your previous posts?

 

[Michael M]

Hmmm, that's a bit annoying....time wasted !!!

https://www.mrexcel.com/forum/excel-questions/986744-stamp-duty-calculation-formula.html

 

[matwright06]

Hmmm, that's a bit annoying....time wasted !!!

https://www.mrexcel.com/forum/excel-questions/986744-stamp-duty-calculation-formula.html

Apologies Michael - Yes I had a similar question previously. For some reason I tried for days and couldn't get it to work!

I thought It would have been the same just creating a new table and amending the cell references in the formula to correspond to the new table however I had no luck.

Thank you guys - this worked.
=VLOOKUP(D12,Data!$I$2:$J$7,2)+(D12-VLOOKUP(D12,Data!$I$2:$I$7,1))*VLOOKUP(D12,Data!$I$2:$K$7,3)

 

[matwright06]

Thanks Scott - worked a treat! Spaceballs was a top flick!