Stamp Duty Calculator

S

sblatch

New Member
Joined
Jan 14, 2014
Messages
2
I want to be able to calculate the UK Stamp Duty on land purchases using an Excel formula. Shown below is the current rates.

Purchase price/lease premium or transfer value​
SDLT rate​
Up to £125,000​
Zero​
Over £125,000 to £250,000​
1%​
Over £250,000 to £500,000​
3%​
Over £500,000 to £1 million​
4%​
Over £1 million to £2 million​
5%​
Over £2 million7%

<thead style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; "> </thead><tbody style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; ">
</tbody>
As you can see there are various rates, which are applicable to the whole value. i.e.
If the land is worth £270,000 the stamp duty would be 3% which would need to be shown. This would then be calculated. C1 "3%" and D1 "£90,000"

I am sure this can be done but is beyond my basic knowledge.

Any help would be appreciated.

:)
 
Sort by date Sort by votes
You can better use a INDEX/MATCH formua. This makes it a lot easier to maintain your formulas in case the government modifies the rates or price bands. First start by creating a table like this. Note that I start the table with 0, this is critical for the formula to work!


Column AColumn BColumn C
Row 1Puchase priceSDLT rateSpend previous band(s)
Row 200,000
Row 31250000,00=A2*B2+C2
Row 42500000,02=A3*B3+C3
Row 59250000,05=A4*B4+C4
Row 615000000,10=A5*B5+C5

<tbody>
</tbody>


Assume you placed the purchase price in cell E1, then for formula becomes like this:

=(E1-INDEX($A:$A;MATCH(E1;$A:$A;1);1))*INDEX($B:$B;MATCH(E1;A:A;-1);1)+INDEX($C:$C;MATCH(E1;$A:$A;1);1)
 
Last edited:
Upvote 0

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
I've tried using the SUMPRODUCT formula for calculating the SDLT totals, however this only works for me when the first rate bracket is 0%

Can anyone help with calculating the below updated rates?

Band​
SDLTrate​
Up to £125,000
3%
£125,000 to £250,000
5%
£250,000 to £925,000
8%
£925,000 to £1.5 million
13%
£1.5 million +
15%

<tbody>
</tbody>


Thanks!!
 
Upvote 0
try this

=SUMPRODUCT(--(A1>{0;125000;250000;925000;1500000}), (A1-{0;125000;250000;925000;1500000}), {0.03;0.02;0.03;0.05;0.02})
 
Upvote 0
AlanY said:
try this

=SUMPRODUCT(--(A1>{0;125000;250000;925000;1500000}), (A1-{0;125000;250000;925000;1500000}), {0.03;0.02;0.03;0.05;0.02})
Click to expand...

Thank you so much! Also... do you know the formula I would use to populate the individual "SDLT Payable" cell, based on the property price?

e.g. for a £350,000 property:

Band SDLT ratesSDLT payable (£)
£0-£125k3%3,750
£125k-£250k5%6,250
£250-£925k8%8,000
Total18,000

<colgroup><col><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
e.g. for a £350,000 property:
tierbaldeductrateprevtier(s)
100.030
BandSDLT ratesSDLT payable (£)1250001250000.053750
£0-£125k3%3,7502500002500000.0810000
£125k-£250k5%6,2509250009250000.0864000
£250-£925k8%8,000
Total18,000
once you set up this lookup table
any amount can be entered
and total tax due calculated
amounttax due
1090003270
1250003750
1260003800note the last amount double checks it
25000010000
25100010080
92500064000
92600064080
92700064160
100000070000
35000018000

<colgroup><col><col><col><col><col><col><col span="9"></colgroup><tbody>
</tbody>
 
Upvote 0
oldbrewer said:
e.g. for a £350,000 property:
tierbaldeductrateprevtier(s)
100.030
BandSDLT ratesSDLT payable (£)1250001250000.053750
£0-£125k3%3,7502500002500000.0810000
£125k-£250k5%6,2509250009250000.0864000
£250-£925k8%8,000
Total18,000
once you set up this lookup table
any amount can be entered
and total tax due calculated
amounttax due
1090003270
1250003750
1260003800note the last amount double checks it
25000010000
25100010080
92500064000
92600064080
92700064160
100000070000
35000018000

<tbody>
</tbody>
Click to expand...


Try below

Sheet1

ABCD
1Property Price is Greater Than or Equal To:But Less Than or Equal to:Tax RateDifferential rate
2£0.00£125,000.003.0%3.0%
3£125,000.01£250,000.005.0%2.0%
4£250,000.01£925,000.008.0%3.0%
5£925,000.01£1,500,000.0013.0%5.0%
6£1,500,000.01 15.0%2.0%
7
8
9
10
11
12
13amounttax due
14£109,000.00£3,270.00
15£125,000.00£3,750.00
16£126,000.00£3,800.00
17£250,000.00£10,000.00
18£251,000.00£10,080.00
19£925,000.00£64,000.00
20£926,000.00£64,130.00
21£927,000.00£64,260.00
22£1,000,000.00£73,750.00
23£350,000.00£18,000.00

<colgroup><col style="WIDTH: 30px; FONT-WEIGHT: bold"><col style="WIDTH: 243px"><col style="WIDTH: 176px"><col style="WIDTH: 64px"><col style="WIDTH: 107px"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
D2=C2
A3=B2+0.01
D3=C3-C2
A4=B3+0.01
D4=C4-C3
A5=B4+0.01
D5=C5-C4
A6=B5+0.01
D6=C6-C5
B14=SUMPRODUCT(--(A14>$A$2:$A$6),(A14-$A$2:$A$6),$D$2:$D$6)
B15=SUMPRODUCT(--(A15>$A$2:$A$6),(A15-$A$2:$A$6),$D$2:$D$6)
B16=SUMPRODUCT(--(A16>$A$2:$A$6),(A16-$A$2:$A$6),$D$2:$D$6)
B17=SUMPRODUCT(--(A17>$A$2:$A$6),(A17-$A$2:$A$6),$D$2:$D$6)
B18=SUMPRODUCT(--(A18>$A$2:$A$6),(A18-$A$2:$A$6),$D$2:$D$6)
B19=SUMPRODUCT(--(A19>$A$2:$A$6),(A19-$A$2:$A$6),$D$2:$D$6)
B20=SUMPRODUCT(--(A20>$A$2:$A$6),(A20-$A$2:$A$6),$D$2:$D$6)
B21=SUMPRODUCT(--(A21>$A$2:$A$6),(A21-$A$2:$A$6),$D$2:$D$6)
B22=SUMPRODUCT(--(A22>$A$2:$A$6),(A22-$A$2:$A$6),$D$2:$D$6)
B23=SUMPRODUCT(--(A23>$A$2:$A$6),(A23-$A$2:$A$6),$D$2:$D$6)

<tbody>
</tbody>

<tbody>
</tbody>
 
Upvote 0
Code: 
[COLOR=#000000][FONT=Inconsolata]=[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]SUMPRODUCT[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]-[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]-[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata](A[/FONT][/COLOR][FONT=Inconsolata]1[/FONT][COLOR=#000000][FONT=Inconsolata]>[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]{[/FONT][/COLOR][COLOR=#1155CC][FONT=Inconsolata]125000[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata];[/FONT][/COLOR][COLOR=#1155CC][FONT=Inconsolata]250000[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata];[/FONT][/COLOR][COLOR=#1155CC][FONT=Inconsolata]925000[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata];[/FONT][/COLOR][COLOR=#1155CC][FONT=Inconsolata]1500000[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]}[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata] [/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][FONT=Inconsolata]A1[/FONT][COLOR=#000000][FONT=Inconsolata]-[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]{[/FONT][/COLOR][COLOR=#1155CC][FONT=Inconsolata]125000[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata];[/FONT][/COLOR][COLOR=#1155CC][FONT=Inconsolata]250000[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata];[/FONT][/COLOR][COLOR=#1155CC][FONT=Inconsolata]925000[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata];[/FONT][/COLOR][COLOR=#1155CC][FONT=Inconsolata]1500000[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]}[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata] [/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]{[/FONT][/COLOR][COLOR=#1155CC][FONT=Inconsolata]0.02[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata];[/FONT][/COLOR][COLOR=#1155CC][FONT=Inconsolata]0.03[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata];[/FONT][/COLOR][COLOR=#1155CC][FONT=Inconsolata]0.05[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata];[/FONT][/COLOR][COLOR=#1155CC][FONT=Inconsolata]0.02[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]}[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]+[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][FONT=Inconsolata]A1[/FONT][COLOR=#000000][FONT=Inconsolata]*[/FONT][/COLOR][COLOR=#1155CC][FONT=Inconsolata]0.03[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR]/CODE]

is great.

However how would we ensure that the additional 3% is not charged where a property has a sale price of under 40,000? 

Over 40,000 then the stamp duty is 3% if a second property
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
873
gvsumich
G
M
  • Solved
A formula that calculates how much stamp duty must be paid on a property, based on current UK rates.
Replies
8
Views
22K
Alex Blakenburg
A
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
537
AndyEd
A
H
  • Question
VBA for Advanced filter copy to another location
2
Replies
14
Views
2K
Fluff
Fluff
E
  • Question
How to copy values from one workbook sheet to another, based on criterias ?
Replies
7
Views
2K
DanteAmor
DanteAmor

Forum statistics

Threads
1,216,189
Messages
6,129,411
Members
449,509
Latest member
ajbooisen

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top