You will build a calculator that returns BSD, ABSD and SSD for a given price and buyer profile.
After three lessons of stamp duty, Farah wanted one thing: a sheet where she could type a price and see the full bill, for them now and for the condo they might buy later. The IRAS calculator does one purchase at a time. A sheet of your own can show four buyer profiles side by side, and when the rates change, you update one table and every figure follows.
This exercise builds that calculator. Allow about 30 minutes. Have the IRAS stamp duty pages open, because the rates you type in must come from there.
The worked example uses invented rates, including the tiers from lesson 3.1, BSD: tiered rates that work like tax bands, so that the arithmetic is easy to follow and nobody copies an old IRAS figure by accident. Your own sheet needs the current IRAS rates.
Start with a tab called Rates, which will hold three small tables that everything else reads from.
The BSD table has one row per tier, with three columns: the lower bound of the tier, the upper bound, and the rate. For the top tier, put a very large number as the upper bound, such as 999,999,999, so every price falls inside some tier. In the worked example, the rows are 0 to 200,000 at 1%, 200,000 to 500,000 at 2%, and 500,000 upwards at 3%. Copy the real tiers from IRAS, however many there are.
The ABSD table has one row per profile and one rate column. Start with four profiles: citizen buying a first home, citizen buying a second home, permanent resident buying a first home, and foreigner. The invented rates in the example are 0%, 15%, 10% and 40%.
The SSD table has one row per year of the holding period, with the rate for a sale in that year, and a final row for "after the holding period" at zero. The invented example uses 12%, 8% and 4% for years one to three.
Keep a cell above each table 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 one cell, say B1, and the market value in B2 if you have one. The duty is charged on the higher of the two, so in B3 write =MAX(B1, B2).
Then add one row per BSD tier. For each, the slice of value that falls inside the tier is the base capped at the upper bound, minus the lower bound, and never less than zero. In most spreadsheets that is =MAX(0, MIN($B$3, upper) - lower), with upper and lower pointing at the Rates tab. Multiply each slice by its rate, then total the column.
For the Bedok flat at S$620,000, the three slices in the example are S$200,000 at 1%, S$300,000 at 2% and S$120,000 at 3%, giving S$2,000, S$6,000 and S$3,600. The total BSD is S$11,600.
Now make four columns, one per ABSD profile, each with three rows: BSD, which is the same for every profile, ABSD, which is the base multiplied by that profile's rate, and the total of the two.
At S$620,000 with the invented rates, a citizen buying a first home pays only the S$11,600 of BSD. Make it a citizen's second home and S$93,000 of ABSD lifts the total to S$104,600, while a permanent resident buying a first home would pay S$62,000 of ABSD for a total of S$73,600. The foreigner column dwarfs the rest: S$248,000 of ABSD and S$259,600 in all, at the same price. A foreigner can't buy an HDB flat at all, so that column is really about a private home at that price.
Add a note under the citizen second-home column reminding you of remission, from 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, but they pay it upfront first, so the cash still has to be found.
Below the purchase figures, add a sale price cell and a "year of sale" cell. Use a lookup, such as VLOOKUP or XLOOKUP, to find the SSD rate for that year from the Rates tab, and multiply. For a private home bought at S$620,000 and sold in year two for the same price, the invented 8% gives S$49,600. For an HDB flat sold after its MOP, this line should show zero, as lesson 3.3, Seller's Stamp Duty if you sell too soon, explained.
A calculator you haven't checked is a guess with formulas. Pick two prices, one below and one well above your planned price, and run each through the IRAS stamp duty calculator for at least two of your profiles. Write the IRAS result next to your own, with the date.
If a figure differs, look first at the tier boundaries, where a mistyped bound shifts every slice above it, then at the base, in case your sheet used the price where it should have used the market value. Fix the table, not the formula, wherever you can.
A finished sheet has a Rates tab with dated, sourced tables, 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 checks against the IRAS calculator that match. Farah and Hakim's showed S$11,600 for their purchase with the invented rates, and once they typed in the real tiers, a figure that matched IRAS to the dollar. Build yours for your planned price in the activity below.
Build the calculator, run it for your planned price under four profiles and save the checks against the IRAS calculator.
Junxiong-WFG Organisation is an authorised representative of AIA Financial Advisers Private Limited (Reg. No. 201715016G).