Tuesday, November 15, 2016

Excel formula for calculating stamp duty in Queensland, Australia (that's where Brisbane is)

There used to be a joke that if you wanted to know what Brisbane was like 20 years ago, you should go there now! Having grown up in Brisbane, I can now say that the joke is the cost of housing. Stamp duty makes a bad situation worse: because it isn't indexed to cost of living increases, people are liable for increasing amounts. Here's my Excel formula for calculating stamp duty in QLD if you're one of the shrinking amount of people who can afford a property.

Magic numbers:

  • 1050. This is 1.5% of $70,000. Why $70,000? It's the difference between $70,000 (upper bounds of second dutiable range) and $5,000 (lower bounds of the second dutiable range).
  • 17325. This is $1050 plus 3.5% of $465,000. Why $465,000? It's the difference between $540,000 (upper bounds of third dutiable range) and $75,000 (lower bounds of the third dutiable range).
  • 38025. This is $17,325 plus plus 4.5% of $460,000. Why $460,000? It's the difference between $1,000,000 (upper bounds of fourth dutiable range) and $540,000 (lower bounds of the fourth dutiable range).

Other numbers:
  • 0.015 is 1.5%
  • 0.035 is 3.5%
  • 0.045 is 4.5%
  • 0.0575 is 5.75%

For the mathematical purists, I've also written a long form edition that doesn't contain magic numbers.

  • In the long form, 1050 is expressed as (75000-5000)*0.015
  • In the long form, 17325 is expressed as (75000-5000)*0.015+(540000-75000)*0.035
  • In the long form, 38025 is expressed as (75000-5000)*0.015+(540000-75000)*0.035+(1000000-540000)*0.045
The sauce:

The disclaimer:
  • If you use an internet stranger's Excel formula without rigorous testing against the official Queensland OSR transfer duty calculator, you're awfully trustworthy and I have a tiger repellant rock to sell you.
Homer: Not a bear in sight. The Bear Patrol must be working like a charm.
Lisa: That’s specious reasoning, Dad.
Homer: Thank you, dear.
Lisa: By your logic I could claim that this rock keeps tigers away.
Homer: Oh, how does it work?
Lisa: It doesn’t work.
Homer: Uh-huh. Lisa: It’s just a stupid rock.
Homer: Uh-huh.
Lisa: But I don’t see any tigers around, do you? [Homer thinks of this, then pulls out some money]
Homer: Lisa, I want to buy your rock. [Lisa refuses at first, then takes the exchange]


  1. Nice work - only seems to marry up with the Qld gov calculator on vacant land when compared though:


  2. Or investment property sorry, so I guess there's some sort of dicount when it's your own home?