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.

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.

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.

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.

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.

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:

• Transfer duty rates from the Queensland Office of State Revenue.

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]

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.

Excel formula for calculating stamp duty in Victoria, Australia (that'd be where Melbourne and good coffee is!)

I've been looking for a house to buy which means I don't have Saturday mornings anymore, and my laptop overheats because I have 97 Chrome tabs all open at domain.com.au and realestate.com.au. (one for every affordable property remaining in Australia). As a Sydneysider, I was shocked by the low cost of housing in Victoria. A house for less than $1 million?! Surely a misprint!

Long story short, the Excel formula for calculating stamp duty in the state of Victoria is:


It's based on the duty ranges published by the Victoria State Revenue Office. The SRO also have a duty calculator which appears to be a Lotus Notes application. Go figure. The formula assumes you are an Australian citizen and are not eligible for any sort of first home owner's benefit.

BWAHAHAHAH LOTUS NOTES APP
NO SERIOUSLY WHAT CENTURY ARE WE IN

Here are the components of the Excel formula.

A1: This is the cell that contains the property value or purchase price (whichever is higher, otherwise people would sell their properties on paper for the price of a Big Mac.)

ROUND: Land duty operates on percentages, and the tax office don't bother with cents.

IF(IF(IF(x))): Stamp duty operates on a sliding scale. There are four valuable duty ranges, therefore we need 3 IF() statements to determine which range the property has liabilities in. Why not four? The first IF() evalutes whether the property is range 4 or 3, the second IF() evaluates whether the property is in range 3 or 2, and the final IF() evaluates whether the property is in range 2 or 1.

The 960000, 130001 and 25001 magic numbers: These are the dutiable value ranges.

• If the property is below $25,000, you're subject to range 1.
• If the property is above $25,000 but below $130,001, it's subject to range 1 and 2.
• If the property is above $130,001 but below $960,001, it's subject to ranges 1 and 2 and 3.
• If the property is above $960,001, it's subject to range 4 only.

Wasn't transfer duty supposed to be abolished with GST?!

The 0.055, 0.06, 0.024 and 0.014 magic numbers: These are the duty amounts.

• 0.055 is 5.5%
• 0.06 is 6.0%
• 0.024 is 2.4%
• 0.014 is 1.4%

0.06*(A1-130000), 0.025*(A1-25000): When calculating the stamp duty liability for a range, you don't calculate the liability based on the entire property value; you only calculate it on the liability within that range. Example:

• The property price is $26,000. Liability for the first $25,000 is 1.4%. For the $1000 over this amount, the liability is calculated 2.4%. To calculate the 2.4% liability, you'd subtract $25,000 from the total property value.

The 350 and 2870 magic numbers: This is the residual amount from the previous range.

• If you pay $25,001, your property value is in range 2, and the total liability from range 1 is $350. Your range 2 liability is calculated on top of that. 
• If you pay $130,000, your property value is in range 3, and the total liability from range 1 and 2 is $2870. Your range 3 liability is calculated on top of that. 
• It would be cleaner if I updated this formula so that the residual amount was calculated live. I guess I have a reason to update this blog post now. UPDATED: below!

Although I have tested my Excel formula against a number of values, you should compare its output against the official SRO website calculator before making any decisions with a financial impact larger than a McDonalds meal.

UPDATE: Yes, you can replace the 350+ and 2870+ components of the formula. No, it's definitely not cleaner. It makes the formula more complex and unmaintainable, which is what you don't want in Excel because the Excel formula bar has all the readability of hieroglyphics.

Here's what it looks like long form.

Why is 350+ replaced with (0.014*25000)? Assuming the property costs more than $25,000, the maximum liability of range 1 is $350 is 1.4% of a $25,000. This can be represented in Excel as plain jane 350, or it can be expressed long form.

Why is 2870+ replaced with (0.014*25000)+(0.024*(130000-25000))? Assuming the property costs more than $130,000, the total liability of range 1 and 2 is $2870. $2870 is made up of $350 (maximum liability of range 1) and $2520 (maximum liability in range 2).