Thursday, November 24, 2016

Excel formula for calculating stamp duty in Australian Capital Territory (ACT), Australia (that's where Canberra is)

Excel runs the world. If Microsoft removed nested IFs in Excel, every taxation system would transition to a flat rate overnight. Here's a stamp duty table for properties in Canberra, clearly constructed by someone who loves Excel.

I live one quarter my life in Excel, a quarter in Word, Visio and PowerPoint.

This is taken from the official ACT Revenue Office (that's state revenue, not federal). The Excel formula representing this table is as follows:

If you rely on this formula for critical financial decisions without testing it yourself, you're nuts.

Tuesday, November 22, 2016

Excel formula for calculating stamp duty in Western Australia (WA), Australia (that'd be where Perth is)

If you want to buy property in Western Australia, it's helpful to understand stamp duty liability.

Location of Perth, according to CNN.


This formula replicates the WA Department of Finance stamp duty calculator. It assumes you're an Australian citizen, aren't eligible for concessions, and are purchasing a residential property.


However, the stamp duty calculator rounds to the nearest 100. Hence, a $1000 property will have the same liability as a $1099 property. Here's another formula that calculates liability exactly, as per the WA schedule of rates


Pick whichever one makes your wildest property fantasies come true. I hope it goes without saying that you should test an Excel formula you found on the internet before making actual financial decisions. It is entirely possible for the WA government to change the stamp duty rates 3 seconds after you read this blog post.

Monday, November 21, 2016

Excel formula for calculating stamp duty in Tasmania (TAS), Australia (that'd be where Hobart is)

Here's my Microsoft Excel formula for calculating stamp duty in Tasmania (TAS), Australia.

The output of this formula doesn't match the official Tasmanian State Revenue Office's calculator, because their calculator only "rounds" the input to the nearest $100.

Example: if you enter $100,000 into the official calculator and the Excel formula, they will match. However, if you enter $100,001, the official calculator will say $2438.50 and the Excel formula will say $2435.04. In fact, if you enter any figure between $100,000 and $199,999, their calculator will give the same response. I'm not sure whether the Tasmanian SRO round their inputs to the nearest $100, or whether they follow the letter of the law when calculating liability.

I've created an additional Excel formula which replicates the behaviour of the online calculator.
You're free to pick which one you like. Either way, the maximum margin of error for this formula will be $4.50 (the liability from the highest range). If an inaccuracy of $4.50 bothers you, perhaps you shouldn't be in the property market!

Standard disclaimer applies: do not trust this formula until you have tested it yourself. It makes all sorts of assumptions like you are an Australian citizen and are not entitled to any concessional rates.

Sunday, November 20, 2016

Excel formula for calculating stamp duty in South Australia (SA), Australia (that's where Adelaide is)

Because I'm a completionist, I feel the urge to write Excel formulas to calculate stamp duty for every state in Australia. Here's the formula for South Australia from Revenues SA.



Unlike other states, Revenues SA calcualate liablility to include cents. Hence, no ROUND() in this formula.

If you're trusting an internet stranger's formula without comparing it with the official SA stamp duty calculator, you're nuts. The intent of these formulas is to calculate ballpark stamp duty.

Saturday, November 19, 2016

Excel formula for calculating stamp duty in Northern Territory (NT), Australia (that's where Darwin and Alice Springs are!)

There are 244,300 people in the NT, which is about the population of "Foxconn City" in Shenzhen, China. Darwin has the lowest population density of any capital city (43 people per square kilometer), compared to a Sydney train which can hold up to 4.3 people per square meter.

Here's an Excel formula for calculating stamp duty in NT.

The official calculator rounds to the nearest 5 cents, so I've used MROUND() to achieve the same.

Usual disclaimer applies: these Excel formulas are designed for quick ballpark estimates of property stamp duty pricing. Though they are accurate, you would be wise to confirm important calculations yourself with the official NT stamp duty calculator.

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]


Monday, November 14, 2016

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.