SDLT Calculation Excel Formula

[Mayur Jagtap]

Can someone help me to create an excel formula for the updated version of UK SDLT ?

 

[Dave Patton]

Welcome to the forum.

You did not provide very much information.
Please provide a clear explanation of the brackets and examples with expected results.

A guess follows. Edit for your actual requirements.

N.B. You can paste the example into a clean sheet. Click on the icon below the f(x) in the header and then move to your sheet and paste.

Commission2020.xlsm
	A	B	C	D
1	2,000,000.00	Sumproduct with Named Ranges	153,750.00
2		as above criteria in formula	153,750.00
3
4		Bracket [rB]	Rate	Rate Delta [rR]
5
6		0	0.00%	0.00%
7		125,000	2.00%	2.00%
8		250,000	5.00%	3.00%
9		925,000	10.00%	5.00%
10		1,500,000	12.00%	2.00%
11
SDLT

Cell Formulas
Range	Formula
C1	C1	=SUMPRODUCT(--(A1>rB),A1-rB,rR)
C2	C2	=SUMPRODUCT(--(A1>{0;125000;250000;925000;1500000}),A1-{0;125000;250000;925000;1500000},{0;0.02;0.03;0.05;0.02})
D6:D10	D6	=C6-N(C5)

Named Ranges
Name	Refers To	Cells
rB	=SDLT!$B$6:$B$10	C1
rR	=SDLT!$D$6:$D$10	C1

 

[Dave Patton]

An example that includes an option for First purchase'

Commission2020.xlsm
	A	B	C	D	E
1	Purchase Type	First		First
2	Amount	450,000.00		Maximum	500,000.00
3	SDLT	7,500.00		Threshold	300,000.00
4				Rate	5%
SDLT

Cell Formulas
Range	Formula
B3	B3	=IF(AND(B1=D1,B2<E2),MAX(0,(B2-E3)*0.05),SUMPRODUCT(--(B2>aB),B2-aB,aR))

Commission2020.xlsm
	A	B	C
1	Purchase Type
2	Amount	800,000.00
3	SDLT	30,000.00
4
SDLT

Cell Formulas
Range	Formula
B3	B3	=IF(AND(B1=D1,B2<E2),MAX(0,(B2-E3)*0.05),SUMPRODUCT(--(B2>aB),B2-aB,aR))

N.B. You can use the table shown previously or use the values and use the formula without the table.
You can secure the values from the table above (highlight the name and press F9) or by looking at the brackets and rates.
For this post, I used Excel's Name Manager and created new names for the Bracket data and Rate Differential data
The names are as follows:
aB ={0;125000;250000;925000;1500000}
aR={0;0.02;0.03;0.05;0.02}