Microsoft Excel formula for calculating stamp duty in New South Wales (NSW), Australia. Long form edition without "magic numbers".

excelStampDutyNewSouthWalesAustralia-LongForm

=ROUND(IF(A1>3000000,(0.0125*14000)+(0.015*(30000-14000))+(0.0175*(80000-30000)+(0.035*(300000-80000)+(0.045*(1000000-300000))))+(0.055*(3000000-1000000))+0.07*(A1-3000000),IF(A1>1000000,(0.0125*14000)+(0.015*(30000-14000))+(0.0175*(80000-30000)+(0.035*(300000-80000)+(0.045*(1000000-300000))))+0.055*(A1-1000000),IF(A1>300000,(0.0125*14000)+(0.015*(30000-14000))+(0.0175*(80000-30000)+(0.035*(300000-80000)))+0.045*(A1-300000),IF(A1>80000,(0.0125*14000)+(0.015*(30000-14000))+(0.0175*(80000-30000))+0.035*(A1-80000),IF(A1>30000,(0.0125*14000)+(0.015*(30000-14000))+0.0175*(A1-30000),IF(A1>14000,(0.0125*14000)+0.015*(A1-14000),A1*0.0125)))))),)

here's for the latest rates. I used B1 instead of A1

=ROUNDDOWN(IF(B1>3194000,(0.0125*14000)+(0.015*(32000-14000))+(0.0175*(85000-32000)+(0.035*(319000-85000)+(0.045*(1064000-319000))))+(0.055*(3194000-1064000))+0.07*(B1-3194000),IF(B1>1064000,(0.0125*14000)+(0.015*(32000-14000))+(0.0175*(85000-32000)+(0.035*(319000-85000)+(0.045*(1064000-319000))))+0.055*(B1-1064000),IF(B1>319000,(0.0125*14000)+(0.015*(32000-14000))+(0.0175*(85000-32000)+(0.035*(319000-85000)))+0.045*(B1-319000),IF(B1>85000,(0.0125*14000)+(0.015*(32000-14000))+(0.0175*(85000-32000))+0.035*(B1-85000),IF(B1>32000,(0.0125*14000)+(0.015*(32000-14000))+0.0175*(B1-32000),IF(B1>14000,(0.0125*14000)+0.015*(B1-14000),B1*0.0125)))))),)

Updated for thresholds as of 1 July 2022. Property price assumed to be in cell B1.

=ROUNDDOWN(IF($B$1>3268000,(0.0125*15000)+(0.015*(32000-15000))+(0.0175*(87000-32000)+(0.035*(327000-87000)+(0.045*(1089000-327000))))+(0.055*(3268000-1089000))+0.07*($B$1-3268000),IF($B$1>1089000,(0.0125*15000)+(0.015*(32000-15000))+(0.0175*(87000-32000)+(0.035*(327000-87000)+(0.045*(1089000-327000))))+0.055*($B$1-1089000),IF($B$1>327000,(0.0125*15000)+(0.015*(32000-15000))+(0.0175*(87000-32000)+(0.035*(327000-87000)))+0.045*($B$1-327000),IF($B$1>87000,(0.0125*15000)+(0.015*(32000-15000))+(0.0175*(87000-32000))+0.035*($B$1-87000),IF($B$1>32000,(0.0125*15000)+(0.015*(32000-15000))+0.0175*($B$1-32000),IF($B$1>15000,(0.0125*15000)+0.015*($B$1-15000),$B$1*0.0125)))))),)

July 2023 update:

https://www.revenue.nsw.gov.au/taxes-duties-levies-royalties/transfer-duty

The property price is assumed to be in cell B5.

=ROUNDDOWN(IF(B5>3268000,(0.0125*16000)+(0.015*(35000-16000))+(0.0175*(93000-35000)+(0.035*(351000-93000)+(0.045*(1168000-351000))))+(0.055*(3268000-1168000))+0.07*(B5-3268000),IF(B5>1168000,(0.0125*16000)+(0.015*(35000-16000))+(0.0175*(93000-35000)+(0.035*(351000-93000)+(0.045*(1168000-351000))))+0.055*(B5-1168000),IF(B5>351000,(0.0125*16000)+(0.015*(35000-16000))+(0.0175*(93000-35000)+(0.035*(351000-93000)))+0.045*(B5-351000),IF(B5>93000,(0.0125*16000)+(0.015*(35000-16000))+(0.0175*(93000-35000))+0.035*(B5-93000),IF(B5>35000,(0.0125*16000)+(0.015*(35000-16000))+0.0175*(B5-35000),IF(B5>16000,(0.0125*16000)+0.015*(B5-16000),B5*0.0125)))))),)

Sorry if this is a silly question...

Where is the figure 3268000 coming from for this formula?

@tommytico That figure should be updated now to $3,636,000:

https://www.revenue.nsw.gov.au/taxes-duties-levies-royalties/transfer-duty#accordion__target-1194549-1

Hello @steve-keep, ive updated the formula to 3,636,000 but it looks like I'm $646 off what the NSW duty calculator says. My purchase price is $11m. Are you able to help?

My total duty is calculating at (using your formula): 698515
NSW duty calculator is calculating at: 697869

I'm using your formula:

=ROUNDDOWN(IF(I6>3636000,(0.012516000)+(0.015(35000-16000))+(0.0175*(93000-35000)+(0.035*(351000-93000)+(0.045*(1168000-351000))))+(0.055*(3636000-1168000))+0.07*(I6-3636000),IF(I6>1168000,(0.012516000)+(0.015(35000-16000))+(0.0175*(93000-35000)+(0.035*(351000-93000)+(0.045*(1168000-351000))))+0.055*(I6-1168000),IF(I6>351000,(0.012516000)+(0.015(35000-16000))+(0.0175*(93000-35000)+(0.035*(351000-93000)))+0.045*(I6-351000),IF(I6>93000,(0.012516000)+(0.015(35000-16000))+(0.0175*(93000-35000))+0.035*(I6-93000),IF(I6>35000,(0.012516000)+(0.015(35000-16000))+0.0175*(I6-35000),IF(I6>16000,(0.012516000)+0.015(I6-16000),I6*0.0125)))))),)

Also, Do you also have another formula for surcharge duty?