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

[maxim642]

Property Price	£475,000	Up to £125,000	0%
Stamp Duty		The 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%

 

[dcardno]

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
	A	B	C	D
1			Formula Based
2		525,000	16,250
3
4		-	-	0%
5		125,000	-	2%
6		250,000	2,500	5%
7		925,000	36,250	10%
8		1,500,000	93,750	12%
9
10			Look Up Table
11		525,000	16,250
12
13
14		-	-	-
15		124,999	-	-
16		125,000	-	-
17		125,001	0	0
18		249,999	2,500	2,500
19		250,000	2,500	2,500
20		250,001	2,500	2,500
21		924,999	36,250	36,250
22		925,000	36,250	36,250
23		925,001	36,250	36,250
24		1,499,999	93,750	93,750
25		1,500,000	93,750	93,750
26		1,500,001	93,750	93,750
27		2,000,000	153,750	153,750
Sheet1

Cell Formulas
Range	Formula
C2	C2	=2% * MAX(B2 - 125000, 0) + 3% * MAX(B2 - 250000, 0) + 5% * MAX(B2 - 925000, 0) + 2% * MAX(B2 - 1500000, 0)
C5:C8	C5	=C4 + D4 * (B5 - B4)
B11	B11	=B2
C11,C14:C27	C11	=VLOOKUP(B11, Stamp_Rates, 2) + VLOOKUP(B11, Stamp_Rates, 3) * (B11 - VLOOKUP(B11, Stamp_Rates, 1))
D14:D27	D14	=2% * MAX(B14 - 125000, 0) + 3% * MAX(B14 - 250000, 0) + 5% * MAX(B14 - 925000, 0) + 2% * MAX(B14 - 1500000, 0)
B15,B24,B21,B18	B15	=B16 - 1
B17,B26,B23,B20	B17	=B16 + 1

Named Ranges
Name	Refers To	Cells
Stamp_Rates	=Sheet1!$B$4:$D$8	C11, C14:C27, C5

 

[Dave Patton]

T202002a.xlsm
	A	B	C	D	E
1	Property Price	525,000
2	Stamp Duty	16,250
3	or	16,250
4	or	16,250
5
6		Purchase price bands (£)	Rate	Rate Difference
7		0	0%	0%
8		125,000	2%	2%
9		250,000	5%	3%
10		925,000	10%	5%
11		1,500,000	12%	2%
12
13
3cc

Cell Formulas
Range	Formula
B2	B2	=SUMPRODUCT(--(B1>B7:B11),B1-B7:B11,D7:D11)
B3	B3	=SUMPRODUCT(--(B1>rB),B1-rB,rR)
B4	B4	=SUMPRODUCT(--(B1>{0;125000;250000;925000;1500000}),B1-{0;125000;250000;925000;1500000},{0;0.02;0.03;0.05;0.02})
D7:D11	D7	=C7-N(C6)

 

[Dave Patton]

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
	A	B
1	Property Price	2,000,000
2
3	Stamp Duty	153,750
4
3cc

Cell Formulas
Range	Formula
B3	B3	=SUMPRODUCT(--(B1>rB),B1-rB,rR)

 

[Alex Blakenburg]

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
	B	C	D
4	From	Base Duty Amount	Rate on Excess
5	0	0	0%
6	125,000	0	2%
7	250,000	2,500	5%
8	925,000	36,250	10%
9	1,500,000	93,750	12%
10
11	Property Price	Look Up Table Result
12	525,000	16,250
Sheet1

Cell Formulas
Range	Formula
C5:C9	C5	=IFERROR(C4 + D4 * (B5 - B4),0)
C12	C12	=VLOOKUP(B12, Stamp_Rates, 2) + VLOOKUP(B12, Stamp_Rates, 3) * (B12 - VLOOKUP(B12, Stamp_Rates, 1))

Named Ranges
Name	Refers To	Cells
Stamp_Rates	=Sheet1!$B$5:$D$9	C12, C5:C6

 

[maxim642]

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
	A	B	C	D
1			Formula Based
2		525,000	16,250
3
4		-	-	0%
5		125,000	-	2%
6		250,000	2,500	5%
7		925,000	36,250	10%
8		1,500,000	93,750	12%
9
10			Look Up Table
11		525,000	16,250
12
13
14		-	-	-
15		124,999	-	-
16		125,000	-	-
17		125,001	0	0
18		249,999	2,500	2,500
19		250,000	2,500	2,500
20		250,001	2,500	2,500
21		924,999	36,250	36,250
22		925,000	36,250	36,250
23		925,001	36,250	36,250
24		1,499,999	93,750	93,750
25		1,500,000	93,750	93,750
26		1,500,001	93,750	93,750
27		2,000,000	153,750	153,750
Sheet1

Cell Formulas
Range	Formula
C2	C2	=2% * MAX(B2 - 125000, 0) + 3% * MAX(B2 - 250000, 0) + 5% * MAX(B2 - 925000, 0) + 2% * MAX(B2 - 1500000, 0)
C5:C8	C5	=C4 + D4 * (B5 - B4)
B11	B11	=B2
C11,C14:C27	C11	=VLOOKUP(B11, Stamp_Rates, 2) + VLOOKUP(B11, Stamp_Rates, 3) * (B11 - VLOOKUP(B11, Stamp_Rates, 1))
D14:D27	D14	=2% * MAX(B14 - 125000, 0) + 3% * MAX(B14 - 250000, 0) + 5% * MAX(B14 - 925000, 0) + 2% * MAX(B14 - 1500000, 0)
B15,B24,B21,B18	B15	=B16 - 1
B17,B26,B23,B20	B17	=B16 + 1

Named Ranges
Name	Refers To	Cells
Stamp_Rates	=Sheet1!$B$4:$D$8	C11, C14:C27, C5

Thank you very much. Really helpful.

 

[maxim642]

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
	B	C	D
4	From	Base Duty Amount	Rate on Excess
5	0	0	0%
6	125,000	0	2%
7	250,000	2,500	5%
8	925,000	36,250	10%
9	1,500,000	93,750	12%
10
11	Property Price	Look Up Table Result
12	525,000	16,250
Sheet1

Cell Formulas
Range	Formula
C5:C9	C5	=IFERROR(C4 + D4 * (B5 - B4),0)
C12	C12	=VLOOKUP(B12, Stamp_Rates, 2) + VLOOKUP(B12, Stamp_Rates, 3) * (B12 - VLOOKUP(B12, Stamp_Rates, 1))

Named Ranges
Name	Refers To	Cells
Stamp_Rates	=Sheet1!$B$5:$D$9	C12, C5:C6

Excellent stuff. Thank you for refining the above.

 

[Dave Patton]

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
	A	B	C	D	E	F	G
1	Property Price	---- Stamp Duty ------			The Table is not required
2	2,000,000.00	153,750.00	153,750.00	153,750.00	153,750.00	153,750.00	153,750.00
3
4	Brackets	Rates	Base Duty Amount	Accumulated Arithmetic
5	0	0%	0	0
6	125,000	2%	0	0
7	250,000	5%	2,500	2,500
8	925,000	10%	36,250	36,250
9	1,500,000	12%	93,750	93,750
10	1E+31			153,750
11
3ccc

Cell Formulas
Range	Formula
B2	B2	=VLOOKUP(A2,rL, 3) + VLOOKUP(A2, rL, 2) * (A2 - VLOOKUP(A2, rL, 1))
C2	C2	=D10
D2	D2	=SUMPRODUCT(--(A2>A6:A9),A2-A6:A9,B6:B9-B5:B8)
E2	E2	=SUMPRODUCT(--(A2>{125000;250000;925000;1500000}),A2-{125000;250000;925000;1500000},{0.02;0.03;0.05;0.02})
F2	F2	=SUMPRODUCT(--(A2>aB),A2-aB,aR)
G2	G2	=(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:C9	C6	=IFERROR(C5 + B5 * (A6 - A5),0)
A10	A10	=BigNum
D6:D10	D6	=MAX(0,(MIN($A$2,A6)-A5))*B5+D5

Named Ranges
Name	Refers To	Cells
'3ccc'!rL	='3ccc'!$A$5:$C$9	B2, C6:D6

T202002a.xlsm
	A	B
12	Property Price	525,000.00
13	Stamp Duty	16,250.00
14
3ccc

Cell Formulas
Range	Formula
B13	B13	=SUMPRODUCT(--(B12>aB),B12-aB,aR)

 

[Alex Blakenburg]

@dcardno - Thank you for the feedback. Glad we could help.