Update: The SDLT rates have changed since this article was posted. Please see our updated page for current SDLT, LBTT and LTT rates, along with an updated spreadsheet.
This article includes an Excel spreadsheet you can download and use to calculate residential UK (except Scotland and Wales) stamp duty (SDLT). It covers the first/single home rate as well as the second/additional property rate.
It goes on to explain the formulas used in detail so you can understand exactly what's going on and update or adapt them if you need to.
If you ever want to double check your results, there's the official UK Government guide and calculator.
Calculating stamp duty for buy-to-let investments?
You should try our free buy-to-let profit calculator that includes stamp duty calculation along with profit and yield figures.
Or you can use our property investment tracking and comparison tool to easily do the calculations for all of the properties that you're considering for your next buy-to-let.
Downloadable Excel spreadsheet
Warning: the spreadsheet below is great for learning how to write your own stamp duty formula but may include outdated rates, our new stamp duty spreadsheet is here.
Don't want to read any more - you can jump straight in, grab the spreadsheet and explore. Just enter your purchase price in cell B3 (with a green background).
How it works - calculating per SDLT band
The first calculation in the spreadsheet breaks out the figures per SDLT band. This is in the table starting in cell A8.
There are a few important parts and formulas involved in this. First are the formulas in column F, they each look something like this:
=MAX(MIN(B9-A9, $B$3-A9), 0)
Splitting this up, the first part:
MIN(B9-A9, $B$3-A9)
...produces the minimum of the total in the band (eg 250,000 - 125,000 = 125,000) and the purchase price (B3) minus the lower bound, ie the amount of the purchase price above (eg 200,000 - 125,000 = 75,000). The result is the maximum possible amount that could be within the band.
However in cases where the band is above the purchase price, this part produces a negative result (eg 200,000 - 925,000 = -725,000) which we need to ignore, hence the MAX() function is used to replace any negative numbers with zero.
Once we know how much needs to be taxed in each band, we can calculate the per-band tax - column G for the first/single home rate and column H for second/additional properties.
For all but one case, this is a simple multiplication from of the listed rates, eg:
=$F9*C9
The exception is the lowest rate for second/additional homes as properties below £40,000 don't attract stamp duty, not even the 3% additional home rate. Hence the formula in cell H9 is:
=IF(F9>=40000,$F9*D9,0)
Here the IF() will show the tax only when the purchase price is 40,000 or more, otherwise it shows zero.
How it works - single cell formula
When you look at the stamp duty calculation spreadsheet you'll see there's also a second calculation below the detailed table of SDLT tax bands. This includes two cells (one for single home, the other additional home) that perform the whole stamp duty calculation in a single formula. If you're building a stamp duty calculation into a larger spreadsheet, the chances are you just want to know the final figure rather than a full break down per tax band.
The formulas used here are a bit more complicated so I'll break it apart and explain each element below. Here's the full formula to start with (for single homes, in cell B20):
=SUMPRODUCT(--($B$3>{125000,250000,925000,1500000}), ($B$3-{125000,250000,925000,1500000}), {0.02,0.03,0.05,0.02})
There's a good explanation (with lots of examples) of the SUMPRODUCT function here.
Trying to summarise - SUMPRODUCT() will combine several lists by multiplying each element (across the lists) and then add up those results into a single number. Here's a quick example:
=SUMPRODUCT({0, 10, 20}, {5, 15, 25})
...means:
=(0 * 5) + (10 * 15) + (20 * 25)
=0 + 150 + 500
=650
But our Excel stamp duty formula above uses some other additions to help in building the lists that are passed to SUMPRODUCT() so here are explanations on what they're doing:
--($B$3>{125000,250000,925000,1500000})
This takes the list {125000, 250000, ...} and builds a new list of True/False values based on testing whether each one is less than the value of B3 (our purchase price). The hard-coded numbers in this list match up to the SDLT boundaries. The last (or first as it's written) part of it "--" turns the True/False values into numbers (1 or 0). So for a purchase price of 200,000 it means:
--(200000>{125000,250000,925000,1500000})
=--({True, False, False, False})
={1, 0, 0, 0}
The second list in our SUMPRODUCT() stamp duty formula is:
($B$3-{125000,250000,925000,1500000})
Which builds a new list by subtracted each of the preset numbers from our purchase price. Again the preset numbers are the boundaries of the SDLT bands. Hence with a purchase price of 200,000 we end up with:
(200000-{125000,250000,925000,1500000})
={75000, -50000, -725000, -1300000}
Our last list is simply the additional stamp duty rate for each band. Because this process provides for the tax at each band, the rates used are just the additional percentage for each band above the previous one. Making our last list:
{0.02,0.03,0.05,0.02}
Keeping with our 200,000 purchase price example this results in the following calculation:
=(1 * 75000 * 0.02) + (0 * -50000 * 0.03) + (0 * -725000 * 0.05) + (0 * -1300000 * 0.02)
=(75000 * 0.02) + 0 + 0 + 0
=1500
Adding the second/additional property rate
Because the stamp duty rate for additional properties is flat across all bands we can add this on far more simply. The only slight wrinkle we need to allow for is (similar to the table calculations above) that no stamp duty will be paid on purchases below £40,000. So we calculate the additional property SDLT as:
=IF($B$3>=40000,$B$3*0.03,0)
The IF() simply includes the extra tax if the purchase price is £40,000 or more, otherwise returning zero.
Cell C20 (a single cell formula calculating the stamp duty for second/additional properties) is the above SUMPRODUCT() formula for first homes plus the IF() above, ie:
=SUMPRODUCT(--($B$3>{125000,250000,925000,1500000}), ($B$3-{125000,250000,925000,1500000}), {0.02,0.03,0.05,0.02})+IF($B$3>=40000,$B$3*0.03,0)
Excel spreadsheet and online calculator
In case you missed the links above, here's another one to the Excel spreadsheet Stamp Duty Calculator.
Plus a reminder that you can try our free buy-to-let profit calculator for quick stamp duty calculation along with profit and yield figures.
Or you can use our property investment tracking and comparison tool to easily do the calculations for all of the properties that you're considering for your next buy-to-let.
