Stamp Duty Calculation formula

matwright06 · Jan 20, 2017

Hi all,

I'm creating a property investment calculator and I'm trying to automate the stamp duty calculation with a formula rather than checking the government site for each and every property analysed.

Eg, Cell B2 contains the purchase price, Cell J2 needs to show the TAS Stamp Duty payable on that purchase price. The schedule is as follows

Value of the property (including chattels)	Duty payable
Not more than $3 000	$50
More than $3 000 but not more than $25 000	$50 plus $1.75 for every $100, or part, by which the dutiable value exceeds $3 000
More than $25 000 but not more than $75 000	$435 plus $2.25 for every $100, or part, by which the dutiable value exceeds $25 000
More than $75 000 but not more than $200 000	$1,560 plus $3.50 for every $100, or part, by which the dutiable value exceeds $75 000
More than $200 000 but not more than $375 000	$5,935 plus $4.00 for every $100, or part, by which the dutiable value exceeds $200 000
More than $375 000 but not more than $725 000	$12,935 plus $4.25 for every $100, or part, by which the dutiable value exceeds $375 000
More than $725 000	$27,810 plus $4.50 for every $100, or part, by which the dutiable value exceeds $725 000

So I completed 200K to 375K rule and the 375K to 725k rule with the below formula...I guess for figures greater than 725K the calculation will be inaccurate.

=IF(AND(B2>=200000,B2<375000),5935+4*INT((B2-199901)/100),IF(B2>=375000,12935+4.25*INT((B2-374901)/100),"-"))

I tried adding the 375K - 725K rule to the end of this formula but Excel returned an error saying I had too many arguments.

Can anyone assist? I wonder if its possible to have a formula where all 7 rules in the table above can be calculated accurately in one cell? Perhaps its too complicated for one cell...

Thanks for listening

MW

abhi1687 · Jan 20, 2017

you can use following formula:

Code:

=IFERROR(VLOOKUP(AGGREGATE(14,6,($A$2:$A$7)/(($A$2:$A$7-$D$1)<=0),1),$A$2:$C$7,2,0)+ROUNDUP(($D$1-AGGREGATE(14,6,($A$2:$A$7)/(($A$2:$A$7-$D$1)<=0),1))/100,0)*VLOOKUP(AGGREGATE(14,6,($A$2:$A$7)/(($A$2:$A$7-$D$1)<=0),1),$A$2:$C$7,3,0),50)

D1=property value
A2:A7=3000 to 725000
B2:B7=50 to 27810
C2:C7= 1.75 to 4.5

I'm sure some one else here will have shorter version of it

Tetra201 · Jan 20, 2017

Here is a shorter version:

=LOOKUP(E2-0.01,A2:A8,B2:B8)+LOOKUP(E2-0.01,A2:A8,C2:C8)*(ROUNDUP(E2,-2)-LOOKUP(E2-0.01,A2:A8))/100

E2 =property value
A2 =0, B2 =50, C2 =0.00
A3 =3000, B3 =50, C3 =1.75
A4 =25000, B4 =435, C4 =2.25
A5 =75000, B5 =1560, C5 =3.50
A6 =200000, B6 =5935, C6 =4.00
A7 =375000, B7 =12935, C7 =4.25
A8 =725000, B8 =27810, C8 =4.50

Dave Patton · Jan 20, 2017

Excel 2010

A

B

C

D

E

F

G

1

Brackets

Rate

Rate Diff

2

Purchase Price

725,000.00

27,810.00

3,000

1.75%

3

25,000

2.25%

0.50%

4

75,000

3.50%

1.25%

5

200,000

4.00%

0.50%

6

375,000

4.25%

0.25%

7

725,000

4.50%

0.25%

2a

Cell Formulas
Range	Formula
C2	=SUMPRODUCT(--(B2>rB),B2-rB,rDiff)+(B2>0)*50
G2	=F2-N(F1)

Named Ranges
Name	Refers To	Cells
rB	='2a'!$E$2:$E$7
rDiff	='2a'!$G$2:$G$7

oldbrewer · Jan 20, 2017

Value of the property (including chattels)	Duty payable
Not more than $3 000	$50
More than $3 000 but not more than $25 000	$50 plus $1.75 for every $100, or part, by which the dutiable value exceeds $3 000
More than $25 000 but not more than $75 000	$435 plus $2.25 for every $100, or part, by which the dutiable value exceeds $25 000
More than $75 000 but not more than $200 000	$1,560 plus $3.50 for every $100, or part, by which the dutiable value exceeds $75 000
More than $200 000 but not more than $375 000	$5,935 plus $4.00 for every $100, or part, by which the dutiable value exceeds $200 000
More than $375 000 but not more than $725 000	$12,935 plus $4.25 for every $100, or part, by which the dutiable value exceeds $375 000
More than $725 000	$27,810 plus $4.50 for every $100, or part, by which the dutiable value exceeds $725 000




with a look up reference table should the rates change just amend the table					1	50	0	0
						3000.01	50	1.75	3000
formula giving the first value 85 below is						25000.01	435	2.25	25000
						75000.01	1560	3.5	75000
=((A25-VLOOKUP(A25,mytable,4))/100)*VLOOKUP(A25,mytable,3)+VLOOKUP(A25,mytable,2)			200000.01	5935	4	200000
						375000.01	12935	4.25	375000
						725000.01	27810	4.5	725000
						999999999	27810	4.5	725000




5000		85
25300		441.75
80,500		1752.5
135700		3684.5
190,900		5616.5
246100		7779
301,300		9987
356500		12195
411,700		14494.75
466900		16840.75
522,100		19186.75
577300		21532.75
632,500		23878.75
687700		26224.75
742,900		28615.5
798100		31099.5

matwright06 · Jan 20, 2017

Brilliant - thanks mate. I ended up putting the table in another data sheet. Just had to add in the $ symbols to make it work on the following rows. Cheers!

Tetra201 said:
Here is a shorter version:

=LOOKUP(E2-0.01,A2:A8,B2:B8)+LOOKUP(E2-0.01,A2:A8,C2:C8)*(ROUNDUP(E2,-2)-LOOKUP(E2-0.01,A2:A8))/100

E2 =property value
A2 =0, B2 =50, C2 =0.00
A3 =3000, B3 =50, C3 =1.75
A4 =25000, B4 =435, C4 =2.25
A5 =75000, B5 =1560, C5 =3.50
A6 =200000, B6 =5935, C6 =4.00
A7 =375000, B7 =12935, C7 =4.25
A8 =725000, B8 =27810, C8 =4.50

matwright06 · Jan 20, 2017

Thanks for the quick response! Appreciate it!

abhi1687 said:
you can use following formula:

Code:

=IFERROR(VLOOKUP(AGGREGATE(14,6,($A$2:$A$7)/(($A$2:$A$7-$D$1)<=0),1),$A$2:$C$7,2,0)+ROUNDUP(($D$1-AGGREGATE(14,6,($A$2:$A$7)/(($A$2:$A$7-$D$1)<=0),1))/100,0)*VLOOKUP(AGGREGATE(14,6,($A$2:$A$7)/(($A$2:$A$7-$D$1)<=0),1),$A$2:$C$7,3,0),50)

D1=property value
A2:A7=3000 to 725000
B2:B7=50 to 27810
C2:C7= 1.75 to 4.5

I'm sure some one else here will have shorter version of it

matwright06 · Jan 20, 2017

Thanks for having a crack. Much appreciated Oldbrewer!

oldbrewer said:
Value of the property (including chattels) Duty payable
Not more than $3 000 $50
More than $3 000 but not more than $25 000 $50 plus $1.75 for every $100, or part, by which the dutiable value exceeds $3 000
More than $25 000 but not more than $75 000 $435 plus $2.25 for every $100, or part, by which the dutiable value exceeds $25 000
More than $75 000 but not more than $200 000 $1,560 plus $3.50 for every $100, or part, by which the dutiable value exceeds $75 000
More than $200 000 but not more than $375 000 $5,935 plus $4.00 for every $100, or part, by which the dutiable value exceeds $200 000
More than $375 000 but not more than $725 000 $12,935 plus $4.25 for every $100, or part, by which the dutiable value exceeds $375 000
More than $725 000 $27,810 plus $4.50 for every $100, or part, by which the dutiable value exceeds $725 000
with a look up reference table should the rates change just amend the table 1 50 0 0
3000.01 50 1.75 3000
formula giving the first value 85 below is 25000.01 435 2.25 25000
75000.01 1560 3.5 75000
=((A25-VLOOKUP(A25,mytable,4))/100)*VLOOKUP(A25,mytable,3)+VLOOKUP(A25,mytable,2) 200000.01 5935 4 200000
375000.01 12935 4.25 375000
725000.01 27810 4.5 725000
999999999 27810 4.5 725000
5000 85
25300 441.75
80,500 1752.5
135700 3684.5
190,900 5616.5
246100 7779
301,300 9987
356500 12195
411,700 14494.75
466900 16840.75
522,100 19186.75
577300 21532.75
632,500 23878.75
687700 26224.75
742,900 28615.5
798100 31099.5

<tbody>
</tbody>

matwright06 · Jan 20, 2017

Thanks Dave! Looks sharp and clean. You guys are jets!

Dave Patton said:
Excel 2010

A B C D E F G
1 Brackets Rate Rate Diff
2 Purchase Price 725,000.00 27,810.00 3,000 1.75% 1.75%
3 25,000 2.25% 0.50%
4 75,000 3.50% 1.25%
5 200,000 4.00% 0.50%
6 375,000 4.25% 0.25%
7 725,000 4.50% 0.25%

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
2a

Worksheet Formulas

Cell Formula
C2 =SUMPRODUCT(--(B2>rB),B2-rB,rDiff)+(B2>0)*50
G2 =F2-N(F1)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Workbook Defined Names

Name Refers To
rB ='2a'!$E$2:$E$7
rDiff ='2a'!$G$2:$G$7

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Tetra201 · Jan 20, 2017

matwright06 said:
Brilliant - thanks mate. I ended up putting the table in another data sheet. Just had to add in the $ symbols to make it work on the following rows. Cheers!

You are welcome. Glad it worked for you.

Stamp Duty Calculation formula

matwright06

New Member

Excel Facts

abhi1687

Board Regular

Tetra201

MrExcel MVP

Dave Patton

Well-known Member

oldbrewer

Well-known Member

matwright06

New Member

matwright06

New Member

matwright06

New Member

matwright06

New Member

Tetra201

MrExcel MVP

Similar threads

Forum statistics

Share this page

Stamp Duty Calculation formula

New Member

Excel Facts

Board Regular

MrExcel MVP

Well-known Member

Well-known Member

New Member

New Member

New Member

New Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock