Calculating Stamp Duty (with Second Homes) in Excel

This article includes an Excel spreadsheet you can download and use to calculate residential UK (except Scotland) stamp duty (SDLT rates correct from April 2016). 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

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 test 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.

Improvements to our free buy-to-let profit calculator

There are two excellent improvements to our free buy-to-let profit calculator live today.

Better Results Layout

Improved buy-to-let profit calculator results previewNow with much clearer separation between the result sections. You can easily see how the figures are grouped. For example, showing the total investment required and how it’s broken down into the component parts underneath.

Yellow highlighting shows which mortgage criteria is restricting the amount you can borrow. In the screenshot above, the rental cover requirement (150% at a stress rate of 5.5% in this case) is the restriction. Meaning that only 73% LTV is possible.

Calculate Minimum Investment

You can now leave the “Cash available” field empty and the calculator will work out the minimum investment required to satisfy the mortgage criteria.

Buy-to-let calculation of minimum investment

This calculation was run with no value set for “Cash available” and PaTMa has calculated the minimum investment required as £101,762.64. It also shows the factor limiting the size of the mortgage is the rental cover requirement.

If you know how much cash you have available to invest, you can still enter it before calculating. The system will check you can actually afford the purchase and highlight in red any mortgage restrictions you can’t meet.

Buy-to-let calculator, too little investment

Make sure you check the figures for your next property purchase on our free buy-to-let calculator. To save your investment scenario with details of multiple properties, then easily compare them create a free account for the PaTMa prospector tool.

Gas Safety Week: Fighting for a Gas Safe nation

We’re proud to be supporting Gas Safety Week 2017, taking place 18th – 24th September.

Gas Safety Week is an annual safety week to raise awareness of gas safety and the importance of taking care of your gas appliances. It’s co-ordinated by Gas Safe Register, the official list of gas engineers who are legally allowed to work on gas.

Badly fitted and poorly serviced gas appliances can cause gas leaks, fires, explosions and carbon monoxide poisoning. Every year thousands of people across the UK are diagnosed with carbon monoxide poisoning. It’s a highly poisonous gas. You can’t see it, taste it or smell it, but it can kill quickly with no warning.

Landlords are legally responsible for the safety of their tenants. Landlords should make sure maintenance and annual safety checks on gas appliances are carried out by a Gas Safe registered engineer.

Gas Safe Hero Doug

As a landlord, you’re legally obliged to make sure:

  • Pipe-work, appliances and flues provided for tenants are maintained in a safe condition.
  • All appliances and flues provided for tenants use have an annual safety check. Our previous article can help you remember your gas safety renewals.
  • Maintenance and annual safety checks are carried out by an engineer registered with Gas Safe Register.
  • All gas equipment (including any appliance left by a previous tenant) is safe or otherwise removed before re-letting.
  • A Gas Safety Record is provided to the tenant within 28 days of completing the check or to any new tenant before they move in.
  • You keep a copy of the Gas Safety Record for two years.

Before any gas work is carried out always check the engineers ID card and make sure the engineer is qualified for the work you need doing. Encourage your tenants to do the same.

To find or check an engineer go to www.gassaferegister.co.uk or call 0800 408 5500.

Never Forget Gas Safety Renewals

When you’re managing property you have to stay on top of your gas safety certificates. Both for the safety of your tenants and to meet your legal requirements.

A gas safety certificate lasts for a year and remembering when it’s due for renewal, especially if you’re keeping track of several properties, can be a difficult task.

Reminder Options

Here are some possible approaches we’ve seen before that you could try:

  • A paper diary – put the expiry in next year plus a reminder to book the safety check a few weeks before.
  • An electronic diary – pretty much the same as above, Google Calendar is a good online diary if you need one.
  • A spreadsheet – a simple list of each property and the renew date should do it, make sure you sort them by due date and check it frequently though.
  • A reminder service – there’s a Gas Safe specific one at www.staygassafe.co.uk.
  • Rely on your gas engineer to keep track – you’re the one legally responsible though so we wouldn’t recommend this.
  • Use the safety certificate features in PaTMa – never loose the certificate and get automatic reminders before renewal.

Recommended – PaTMa

Keeping track of all your safety certificates in PaTMa gives you two great benefits:

  • Always know where your latest certificate is – easily provide a copy to new tenants (a legal requirement) or anyone else as requested.
  • Receive automatic reminders to make sure you get the renewal booked in plenty of time.

It couldn’t be simpler to add each new certificate as you receive them, here’s a screenshot of the upload form:

PaTMa add safety certificate screenshot

With your safety certificates securely uploaded and stored you can relax. Every day PaTMa checks through your properties and will email if any have safety certificate renewals coming up soon (30 days and again 7 days warning). You just need to pick up the phone to your favourite Gas Safe engineer to book the check. Here’s what a PaTMa reminder email looks like:

Hi Peter,

Your gas safety certificate for 1 High Street will expire on 8th September 2017.

You should arrange for it to be renewed and upload the new details on https://www.patma.co.uk/

Kind regards

PaTMa

There’s even an extra layer of reminder, just in case you might miss an email. You can turn on SMS reminders (paid plans only) and opt-in to receiving safety certificate reminders by text.

Simplify your life and use PaTMa to keep track of your buy-to-let properties.