matwright06
New Member
- Joined
- Jan 20, 2017
- Messages
- 9
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
[TABLE="width: 100%"]
<tbody>[TR]
[TD="width: 32%"]Value of the property (including chattels)[/TD]
[TD="width: 68%"]Duty payable[/TD]
[/TR]
[TR]
[TD="width: 32%"]Not more than $3 000 [/TD]
[TD="width: 68%"]$50 [/TD]
[/TR]
[TR]
[TD="width: 32%"]More than $3 000 but not more than $25 000 [/TD]
[TD="width: 68%"]$50 plus $1.75 for every $100, or part, by which the dutiable value exceeds $3 000[/TD]
[/TR]
[TR]
[TD="width: 32%"]More than $25 000 but not more than $75 000 [/TD]
[TD="width: 68%"]$435 plus $2.25 for every $100, or part, by which the dutiable value exceeds $25 000 [/TD]
[/TR]
[TR]
[TD="width: 32%"]More than $75 000 but not more than $200 000 [/TD]
[TD="width: 68%"]$1,560 plus $3.50 for every $100, or part, by which the dutiable value exceeds $75 000 [/TD]
[/TR]
[TR]
[TD="width: 32%"]More than $200 000 but not more than $375 000 [/TD]
[TD="width: 68%"]$5,935 plus $4.00 for every $100, or part, by which the dutiable value exceeds $200 000 [/TD]
[/TR]
[TR]
[TD="width: 32%"]More than $375 000 but not more than $725 000 [/TD]
[TD="width: 68%"]$12,935 plus $4.25 for every $100, or part, by which the dutiable value exceeds $375 000 [/TD]
[/TR]
[TR]
[TD="width: 32%"]More than $725 000 [/TD]
[TD="width: 68%"]$27,810 plus $4.50 for every $100, or part, by which the dutiable value exceeds $725 000[/TD]
[/TR]
</tbody>[/TABLE]
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
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
[TABLE="width: 100%"]
<tbody>[TR]
[TD="width: 32%"]Value of the property (including chattels)[/TD]
[TD="width: 68%"]Duty payable[/TD]
[/TR]
[TR]
[TD="width: 32%"]Not more than $3 000 [/TD]
[TD="width: 68%"]$50 [/TD]
[/TR]
[TR]
[TD="width: 32%"]More than $3 000 but not more than $25 000 [/TD]
[TD="width: 68%"]$50 plus $1.75 for every $100, or part, by which the dutiable value exceeds $3 000[/TD]
[/TR]
[TR]
[TD="width: 32%"]More than $25 000 but not more than $75 000 [/TD]
[TD="width: 68%"]$435 plus $2.25 for every $100, or part, by which the dutiable value exceeds $25 000 [/TD]
[/TR]
[TR]
[TD="width: 32%"]More than $75 000 but not more than $200 000 [/TD]
[TD="width: 68%"]$1,560 plus $3.50 for every $100, or part, by which the dutiable value exceeds $75 000 [/TD]
[/TR]
[TR]
[TD="width: 32%"]More than $200 000 but not more than $375 000 [/TD]
[TD="width: 68%"]$5,935 plus $4.00 for every $100, or part, by which the dutiable value exceeds $200 000 [/TD]
[/TR]
[TR]
[TD="width: 32%"]More than $375 000 but not more than $725 000 [/TD]
[TD="width: 68%"]$12,935 plus $4.25 for every $100, or part, by which the dutiable value exceeds $375 000 [/TD]
[/TR]
[TR]
[TD="width: 32%"]More than $725 000 [/TD]
[TD="width: 68%"]$27,810 plus $4.50 for every $100, or part, by which the dutiable value exceeds $725 000[/TD]
[/TR]
</tbody>[/TABLE]
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
