STAMP DUTY

[YARNOS]

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

 

[Scott R]

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)

 

[YARNOS]

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

 

[PaddyD]

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?

Paddy

 

[YARNOS]

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

 

[RAN_MPA]

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.

 

[Ekim]

Yarnos,

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

Regards,

Mike

 

[YARNOS]

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

 

[Dave Patton]

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.

 

[Brocks123]

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