# STAMP DUTY

#### YARNOS

##### New Member
Hi
Does any body know how to calculate the following formula to work out stamp duty on the purchase of a property in NSW Australia

FORMULA:=
Not Exceeding \$14000------\$1.25
\$14001-\$30000---\$175.00 plus \$1.50 per\$100.00 in excess 0f \$14000.00
\$30001-\$80000---\$415.00 plus \$1.75 per \$100 inexcess of \$30000
\$80001-\$300000---\$1290.00 plus \$3.50 p/\$100
\$300001-\$1000000--\$8990 plus \$4.50 p/\$100
over\$1000000 ---\$40490 plus\$5.50/\$100

then i can apply this formula to also calculate tax on my wages as well

thank you

### Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
In cells A1:A6, put 0, 14000, 30000, etc.
In cells B1:B6, put 1.25%, 1.5%, 1.75%, etc.

In C1 enter your dollar amount.
Enter this array formula wherever:
=SUMPRODUCT(IF(C1>=A2:A6,A2:A6)-IF(C1>=A2:A6,A1:A5),B1:B5)+(C1-VLOOKUP(C1,A1:A6,1))*VLOOKUP(C1,A1:B6,2)

THANK YOU FOR YOUR ASSISTANCE I TYPED WHAT YOU STATED AND I DON"T GET ANY RESULT APART FROM #VALUE IN D1 HOW CAn you just type in the amount of purchase and automatically give you the stamp duty amount
eg
purchase price of property?...\$xxxx.000
stampduty on purchase price is \$yyyy.000
legal fees?
bank loan approval fees?
etc
etc
net purchase price is \$zzzzz.000
thank you

the suggested formula needs to be "array entered". That is, press control + shift + enter at the same time to enter the formula, not just enter. If successful, excel will add 'curly brackets' around the formula. Try - do you get the results you expect?

IAM STILL NOT GETTING ANY RESULTS CAN ANYBODY CLEARLY EXPLAIN HOW TO SET IT ALL UP SO THAT STAMP DUTY IS AUTOMATICALLY CALCULATED ON THE PURCHASE PRICE OF THE PROPERTY ACCORDING TO THE ORIGINAL FORMULA

I entered Scott's (CSE) formula as written and received correct results.

Make sure you're entering the formula correctly, and you'll get the correct results.

Nice formula, Scott. I can see many uses for it with my work.

Yarnos,

Check your Private Messages. I will send you a workbook with a working example using Scott's formula and an alternative.

Regards,

Mike

thank you for that superb formula i got the hang of it!!!! how can you do it without displaying the formula eg \$0,14000,30000, 1.5%,1.75 etc) in your work sheet ????
thank you

Another alternative is to just use Vlookup

I added a 3rd column to the Lookup range with the accumulated tax.

I named the range rT. see below
0 1.25% 0
14,000 1.50% 175
30,000 1.75% 415
80,000 3.50% 1,290
300,000 4.50% 8,990
1,000,000 5.50% 40,490

The formula is

=VLOOKUP(\$F\$1,rT,3)+(\$F\$1-VLOOKUP(\$F\$1,rT,1))*VLOOKUP(\$F\$1,rT,2)

And if you do not want to see the details of the formula, use Insert|Name| Define.

Provide a name say Tax and in the Refers to part put the above formula.

Revise the references as necessary. It is not essential to name the table.

To get the tax information use =Tax.

thank you for that superb formula i got the hang of it!!!! how can you do it without displaying the formula eg \$0,14000,30000, 1.5%,1.75 etc) in your work sheet ????
thank you

Hi Can you please email me the spreatsheet

Replies
1
Views
2K
Replies
12
Views
2K

1,218,560
Messages
6,143,204
Members
450,469
Latest member
brent3162

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back