Excel formula for calculating stamp duty in New South Wales, Australia (that's Sydney!)

Looking at the average house prices in Sydney makes me wonder, why on Earth do I live in Sydney?! Apparently the cost of living is more expensive than New York and London. Here's a picture of a AU$10.95 (US$8.26) bottle of watermelon juice.

Has Sydney gone crazy?!

I'm creating a auction guide spreadsheet that automatically calculates bidding positions. Part of the spreadsheet is calculating the NSW stamp duty liability. Here it is.



It's based off the dutiable range guide available at the NSW Office of State Revenue. It assumes you're an Australian citizen, and aren't eligible for a first home owner's benefit. Like all good Excel formulas, there are some magic numbers:

• 175. This is 1.25% of $14,000, which is the maximum liability under the first dutiable range ($0 to $14,000).
• 415. This is $175, plus 1.5% of $16,000. Why $16,000? The second dutiable range ($14,000 to $30,000) contains $16,000.
• 1290. This is $175 (maximum liability under first dutiable range) plus $415 (maximum liability under second dutiable range) plus 1.75% of $50,000. Why $50,000? The third dutiable range ($30,000 to $80,000) contains $50,000.
• 8990. This is $175 (maximum liability under first dutiable range) plus $415 (maximum liability under second dutiable range) plus $1290 (maximum liability under the third dutiable range) plus 3.5% of $220,000. Why $220,000? The fourth dutiable range ($300,000 to $1,000,000).
• 40490. This is $175 (maximum liability under first dutiable range) plus $415 (maximum liability under second dutiable range) plus $1290 (maximum liability under the third dutiable range) plus $40490 (maximum liability under the fourth dutiable range) plus 0.045% of $700,000. Why $700,000? The fifth dutiable range is $300,000 to $1,000,000.
• 150490. This is $175 (maximum liability under first dutiable range) plus $415 (maximum liability under second dutiable range) plus $1290 (maximum liability under the third dutiable range) plus $40490 (maximum liability under the fourth dutiable range) plus 5.5% of $2,000,000. Why $2,000,000? The sixth dutiable range is $1,000,000 to $3,000,000.

• 0.0125 is 1.25%
• 0.015 is 1.5%
• 0.0175 is 1.75%
• 0.035 is 3.5%
• 0.045 is 4.5%
• 0.055 is 5.5%
• 0.07 is 7%.

For the sadists, I've produced a long form edition of the formula that doesn't have any magic numbers.

• In the long form, 175 is expressed as (0.0125*14000)
• In the long form, 415 is expressed as (0.0125*14000)+(0.015*(30000-14000))
• In the long form, 1290 is expressed as (0.0125*14000)+(0.015*(30000-14000))+(0.0175*(80000-30000))
• In the long form, 8990 is expressed as (0.0125*14000)+(0.015*(30000-14000))+(0.0175*(80000-30000)+(0.035*(300000-80000)))
• In the long form, 40490 is expressed as (0.0125*14000)+(0.015*(30000-14000))+(0.0175*(80000-30000)+(0.035*(300000-80000)+(0.045*(1000000-300000))))
• In the long form, 150490 is expressed as (0.0125*14000)+(0.015*(30000-14000))+(0.0175*(80000-30000)+(0.035*(300000-80000)+(0.045*(1000000-300000))))+(0.055*(3000000-1000000))

Sounds obvious, but you should verify this before use unless you are in the habit of trusting internet strangers to write financial formulas that affect your life choices. If the property you're buying is in the 7% range, then you can probably afford an accountant to calculate these things for you.