Build a calculator that gives BSD, ABSD and SSD for a price and a buyer profile.
The IRAS stamp duty calculator works out one purchase at a time. If you want to see what the same price would cost as a first home, as a second home, or for a buyer with a different residency status, you have to run it again for each one and note down every answer. A spreadsheet of your own can show four buyer profiles side by side from a single price. When rates change, you update one table and every figure in the sheet follows. Building it takes about 30 minutes.
Farah is buying a flat in Bedok with Hakim, at S$620,000 (an example figure). After three lessons on stamp duty, Farah wanted a sheet where you type in a price and see the full bill, both for the couple's purchase now and for a condo they might buy later. The worked figures below use their flat.
Keep the IRAS stamp duty pages open while you build, because the rates you type in must come from there. The worked example uses invented rates so the arithmetic is easy to follow and nobody copies an old IRAS figure by accident. Your own sheet must use the current IRAS rates.
Create a tab called Rates. It holds three small tables, and everything else in the sheet reads from them.
The BSD table has one row per tier and three columns: lower bound, upper bound and rate. The example tiers follow the structure from lesson 3.1, "BSD: tiered rates that work like tax bands", because BSD tiers work like tax bands. They are 0 to 200,000 at 1%, 200,000 to 500,000 at 2%, and 500,000 upwards at 3% (invented rates). The top tier needs a very large upper bound, such as 999,999,999, so that every price falls inside some tier. When you build yours, copy the real tiers from IRAS, however many there are.
The ABSD table has one row per profile and one rate column. A profile is a buyer category with its own ABSD rate. Start with four: citizen buying a first home at 0%, citizen buying a second home at 15%, permanent resident buying a first home at 10%, and foreigner at 40% (invented rates).
The SSD table has one row per year of the holding period, giving the rate for a sale in that year. The example uses 12% for year one, 8% for year two and 4% for year three (invented rates). A final row for "after the holding period" is set at zero.
Above each table, keep a cell with the date you copied the rates and the IRAS page they came from. When the government announces a change, that cell tells you the sheet is out of date.
On a second tab, put the purchase price in B1 and the market value, if you know it, in B2. Duty is charged on the higher of the two, so B3 holds the base, with the formula =MAX(B1, B2).
Below that, add one row per BSD tier. Each row works out a slice, the part of the base that falls inside that tier: the base capped at the tier's upper bound, minus the lower bound, and never less than zero. The formula is =MAX(0, MIN($B$3, upper) - lower), where upper and lower point at the matching cells on the Rates tab. Multiply each slice by its tier's rate, then total the column to get BSD.
For Farah and Hakim's S$620,000 flat with the invented rates, the slices are S$200,000 at 1%, which is S$2,000; S$300,000 at 2%, which is S$6,000; and S$120,000 at 3%, which is S$3,600. BSD comes to S$11,600.
Make four profile columns, each with three rows: BSD, ABSD and the total. BSD is the same for every profile. ABSD is the base multiplied by the profile's rate from the Rates tab. At S$620,000 with the invented rates, the columns read:
Citizen first home: ABSD 0, total S$11,600 Citizen second home: ABSD S$93,000, total S$104,600 PR first home: ABSD S$62,000, total S$73,600 Foreigner: ABSD S$248,000, total S$259,600
A foreigner cannot buy an HDB flat at all, so the foreigner column really applies to a private home at that price.
Under the citizen second-home column, add a note about remission, covered in lesson 3.2, "ABSD: who pays it and when it comes back". A married couple upgrading may be able to claim ABSD back if they sell their first home in time. They still pay it upfront first, so they have to find the cash for it.
Below the purchase figures, add a sale price cell and a year-of-sale cell. Use VLOOKUP or XLOOKUP on the Rates tab to find the SSD rate for that year, then multiply it by the sale price. For a private home bought at S$620,000 and sold in year two for the same price, the invented 8% gives SSD of S$49,600. An HDB flat sold after its MOP should show zero SSD. Lesson 3.3, "Seller's Stamp Duty if you sell too soon", explains why.
A calculator you haven't checked is just a guess with formulas, so test yours against the IRAS stamp duty calculator before you rely on it. Use two prices, one below and one well above your planned price, and run each for at least two profiles. Write the IRAS result and the date next to your own figure.
If a figure differs from IRAS, check the tier boundaries first, because one mistyped bound shifts every slice above it. Then check the base, in case the sheet used the price where it should have used the market value. Wherever you can, correct the number in the Rates table and leave the formula as it is.
Farah and Hakim's sheet showed S$11,600 with the invented rates. Once they typed in the real tiers, it gave a figure that matched IRAS to the dollar.
The finished calculator has a Rates tab with dated, sourced tables. It also has a calculation tab where one price returns BSD, ABSD and a total for four profiles, an SSD line for a sale in any year, and a record of two matching checks against the IRAS calculator. Build yours for your planned price in the activity below.
Build the calculator and run it on your planned price for all four profiles. Save your checks against the IRAS calculator.
Junxiong-WFG Organisation is an authorised representative of AIA Financial Advisers Private Limited (Reg. No. 201715016G).