A formula that calculates how much stamp duty must be paid on a property, based on current UK rates.

M

maxim642

Board Regular
Joined
Feb 4, 2021
Messages
91
Office Version
  1. 365
Platform
  1. MacOS
Property Price£475,000Up to £125,0000%
Stamp DutyThe next £125,000 (the portion from £125,001 to £250,000)2%
The next £675,000 (the portion from £250,001 to £925,000)5%
The next £575,000 (the portion from £925,001 to £1.5 million)10%
The remaining amount (the portion above £1.5 million)12%
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Sort by date Sort by votes
You can either use a formula, or a look-up table. The look-up table is easier to understand and to maintain, but really it's a personal preference. The "trick" behind the formula is to conceptualize the rates a little differently: instead of thinking 2% on a range, then 5% on a higher range, etc, look at it like you are paying 2% on everything above 125K, plus an additional 3% (ie 5% in total) on everything above 250K, and an additional 5% (10% total) on everything above 950K, etc... The values below just confirm that the two approaches give the same results.
Book1
ABCD
1Formula Based
2525,00016,250
3
4--0%
5125,000-2%
6250,0002,5005%
7925,00036,25010%
81,500,00093,75012%
9
10Look Up Table
11525,00016,250
12
13
14---
15124,999--
16125,000--
17125,00100
18249,9992,5002,500
19250,0002,5002,500
20250,0012,5002,500
21924,99936,25036,250
22925,00036,25036,250
23925,00136,25036,250
241,499,99993,75093,750
251,500,00093,75093,750
261,500,00193,75093,750
272,000,000153,750153,750
Sheet1
Cell Formulas
RangeFormula
C2C2=2% * MAX(B2 - 125000, 0) + 3% * MAX(B2 - 250000, 0) + 5% * MAX(B2 - 925000, 0) + 2% * MAX(B2 - 1500000, 0)
C5:C8C5=C4 + D4 * (B5 - B4)
B11B11=B2
C11,C14:C27C11=VLOOKUP(B11, Stamp_Rates, 2) + VLOOKUP(B11, Stamp_Rates, 3) * (B11 - VLOOKUP(B11, Stamp_Rates, 1))
D14:D27D14=2% * MAX(B14 - 125000, 0) + 3% * MAX(B14 - 250000, 0) + 5% * MAX(B14 - 925000, 0) + 2% * MAX(B14 - 1500000, 0)
B15,B24,B21,B18B15=B16 - 1
B17,B26,B23,B20B17=B16 + 1
Named Ranges
NameRefers ToCells
Stamp_Rates=Sheet1!$B$4:$D$8C11, C14:C27, C5
 
Upvote 0
T202002a.xlsm
ABCDE
1Property Price525,000
2Stamp Duty16,250
3or16,250
4or16,250
5
6Purchase price bands (£)RateRate Difference
700%0%
8125,0002%2%
9250,0005%3%
10925,00010%5%
111,500,00012%2%
12
13
3cc
Cell Formulas
RangeFormula
B2B2=SUMPRODUCT(--(B1>B7:B11),B1-B7:B11,D7:D11)
B3B3=SUMPRODUCT(--(B1>rB),B1-rB,rR)
B4B4=SUMPRODUCT(--(B1>{0;125000;250000;925000;1500000}),B1-{0;125000;250000;925000;1500000},{0;0.02;0.03;0.05;0.02})
D7:D11D7=C7-N(C6)
 
Upvote 0
The array of bracket information is named rB and the array of rate differentials is named rR.
The data details are shown above.

T202002a.xlsm
AB
1Property Price2,000,000
2
3Stamp Duty153,750
4
3cc
Cell Formulas
RangeFormula
B3B3=SUMPRODUCT(--(B1>rB),B1-rB,rR)
 
Upvote 0
I am a big fan of using lookup tables so to just take @dcardno's example and hopefully making it a bit clearer.
(Technically the From figures should be +1 (or +0.001) but the way the calculation works the rounded off number works)
I would probably convert the Lookup table into a proper Excel table.

20211214 Stamp Duty Calc.xlsx
BCD
4FromBase Duty AmountRate on Excess
5000%
6125,00002%
7250,0002,5005%
8925,00036,25010%
91,500,00093,75012%
10
11Property PriceLook Up Table Result
12525,00016,250
Sheet1
Cell Formulas
RangeFormula
C5:C9C5=IFERROR(C4 + D4 * (B5 - B4),0)
C12C12=VLOOKUP(B12, Stamp_Rates, 2) + VLOOKUP(B12, Stamp_Rates, 3) * (B12 - VLOOKUP(B12, Stamp_Rates, 1))
Named Ranges
NameRefers ToCells
Stamp_Rates=Sheet1!$B$5:$D$9C12, C5:C6
 
Upvote 0
Solution
dcardno said:
You can either use a formula, or a look-up table. The look-up table is easier to understand and to maintain, but really it's a personal preference. The "trick" behind the formula is to conceptualize the rates a little differently: instead of thinking 2% on a range, then 5% on a higher range, etc, look at it like you are paying 2% on everything above 125K, plus an additional 3% (ie 5% in total) on everything above 250K, and an additional 5% (10% total) on everything above 950K, etc... The values below just confirm that the two approaches give the same results.
Book1
ABCD
1Formula Based
2525,00016,250
3
4--0%
5125,000-2%
6250,0002,5005%
7925,00036,25010%
81,500,00093,75012%
9
10Look Up Table
11525,00016,250
12
13
14---
15124,999--
16125,000--
17125,00100
18249,9992,5002,500
19250,0002,5002,500
20250,0012,5002,500
21924,99936,25036,250
22925,00036,25036,250
23925,00136,25036,250
241,499,99993,75093,750
251,500,00093,75093,750
261,500,00193,75093,750
272,000,000153,750153,750
Sheet1
Cell Formulas
RangeFormula
C2C2=2% * MAX(B2 - 125000, 0) + 3% * MAX(B2 - 250000, 0) + 5% * MAX(B2 - 925000, 0) + 2% * MAX(B2 - 1500000, 0)
C5:C8C5=C4 + D4 * (B5 - B4)
B11B11=B2
C11,C14:C27C11=VLOOKUP(B11, Stamp_Rates, 2) + VLOOKUP(B11, Stamp_Rates, 3) * (B11 - VLOOKUP(B11, Stamp_Rates, 1))
D14:D27D14=2% * MAX(B14 - 125000, 0) + 3% * MAX(B14 - 250000, 0) + 5% * MAX(B14 - 925000, 0) + 2% * MAX(B14 - 1500000, 0)
B15,B24,B21,B18B15=B16 - 1
B17,B26,B23,B20B17=B16 + 1
Named Ranges
NameRefers ToCells
Stamp_Rates=Sheet1!$B$4:$D$8C11, C14:C27, C5
Click to expand...
Thank you very much. Really helpful.
 
Upvote 0
Alex Blakenburg said:
I am a big fan of using lookup tables so to just take @dcardno's example and hopefully making it a bit clearer.
(Technically the From figures should be +1 (or +0.001) but the way the calculation works the rounded off number works)
I would probably convert the Lookup table into a proper Excel table.

20211214 Stamp Duty Calc.xlsx
BCD
4FromBase Duty AmountRate on Excess
5000%
6125,00002%
7250,0002,5005%
8925,00036,25010%
91,500,00093,75012%
10
11Property PriceLook Up Table Result
12525,00016,250
Sheet1
Cell Formulas
RangeFormula
C5:C9C5=IFERROR(C4 + D4 * (B5 - B4),0)
C12C12=VLOOKUP(B12, Stamp_Rates, 2) + VLOOKUP(B12, Stamp_Rates, 3) * (B12 - VLOOKUP(B12, Stamp_Rates, 1))
Named Ranges
NameRefers ToCells
Stamp_Rates=Sheet1!$B$5:$D$9C12, C5:C6
Click to expand...
Excellent stuff. Thank you for refining the above.
 
Upvote 0
A variety of solutions.
Sumproduct is very useful for such calculations.
The formulas in E2:G2 do not require the table.
BigNum is a named value for a very large number =9.99999999999999E+30

T202002a.xlsm
ABCDEFG
1Property Price ---- Stamp Duty ------The Table is not required
22,000,000.00153,750.00153,750.00153,750.00153,750.00153,750.00153,750.00
3
4BracketsRatesBase Duty AmountAccumulated Arithmetic
500%00
6125,0002%00
7250,0005%2,5002,500
8925,00010%36,25036,250
91,500,00012%93,75093,750
101E+31153,750
11
3ccc
Cell Formulas
RangeFormula
B2B2=VLOOKUP(A2,rL, 3) + VLOOKUP(A2, rL, 2) * (A2 - VLOOKUP(A2, rL, 1))
C2C2=D10
D2D2=SUMPRODUCT(--(A2>A6:A9),A2-A6:A9,B6:B9-B5:B8)
E2E2=SUMPRODUCT(--(A2>{125000;250000;925000;1500000}),A2-{125000;250000;925000;1500000},{0.02;0.03;0.05;0.02})
F2F2=SUMPRODUCT(--(A2>aB),A2-aB,aR)
G2G2=(A2>125000)*(A2-125000)*0.02+(A2>250000)*(A2-250000)*0.03+(A2>925000)*(A2-925000)*0.05+(A2>1500000)*(A2-1500000)*0.02
C6:C9C6=IFERROR(C5 + B5 * (A6 - A5),0)
A10A10=BigNum
D6:D10D6=MAX(0,(MIN($A$2,A6)-A5))*B5+D5
Named Ranges
NameRefers ToCells
'3ccc'!rL='3ccc'!$A$5:$C$9B2, C6:D6


T202002a.xlsm
AB
12Property Price525,000.00
13Stamp Duty16,250.00
14
3ccc
Cell Formulas
RangeFormula
B13B13=SUMPRODUCT(--(B12>aB),B12-aB,aR)
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question
Formula to calculate stamp duty payable on commercial property in the UK (post 2016)
2
Replies
17
Views
6K
Nikork
N
O
  • Question
December 2014 UK Stamp Duty Changes
Replies
4
Views
11K
MARK858
MARK858
S
  • Question
Stamp Duty Calculator
3 4 5
Replies
43
Views
54K
isdoo
I

Forum statistics

Threads
1,215,261
Messages
6,123,949
Members
449,134
Latest member
NickWBA

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