Formula to calculate stamp duty payable on commercial property in the UK (post 2016)

[Nikork]

Detailed calculation
This is a breakdown of how the total amount of SDLT was calculated based on the rules from 17 March 2016

Purchase price bands (£)	Percentage rate (%)	SDLT due (£)
Up to 150,000	0	0
Above 150,000 and up to 250,000	2	2,000
Above 250,000+	5	3,750
	Total SDLT due	5,750

Here are the stamp duty bands. I am just looking for a formula that can take the purchase price and give me a stamp duty figure based on above. Probably a very easy task to someone who knows how!

 

[Nikork]

Sorry I should have said post March 2017 and not post 2016. Thanks

 

[MARK858]

I don't know why you don't want the current rates but...

Book1 (1).xlsm
	A	B	C
1	675000.00		3250
Sheet4

Cell Formulas
Range	Formula
C1	C1	=SUMPRODUCT(--(A1>{0;150000;250000}), (A1-{0;1150000;250000}), {0;0.02;0.03})

 

[Nikork]

I do want the current rates. Are these not them? We are talking about commercial property rates... The link I dropped into my thread is from the current government website so should be up to date.

 

[Nikork]

Thank you for the fast response but the code you provided does not work for me.

 

[MARK858]

Current rate (and without the typo in the last post).

Book1 (1).xlsm
	A	B	C
1	675000.00		23750
Sheet4

Cell Formulas
Range	Formula
C1	C1	=SUMPRODUCT(--(A1>{0;125000;250000}), (A1-{0;125000;250000}), {0;0.02;0.03})

 

[MARK858]

Thank you for the fast response but the code you provided does not work for me.

See next post typo in the other post corrected is

Book1 (1).xlsm
	A	B	C
1	675000.00		23250
Sheet4

Cell Formulas
Range	Formula
C1	C1	=SUMPRODUCT(--(A1>{0;150000;250000}), (A1-{0;150000;250000}), {0;0.02;0.03})

 

[MARK858]

Current rate (and without the typo in the last post) if you are talking uk rates.

Book1 (1).xlsm
	A	B	C
1	675000.00		23750
Sheet4

Cell Formulas
Range	Formula
C1	C1	=SUMPRODUCT(--(A1>{0;125000;250000}), (A1-{0;125000;250000}), {0;0.02;0.03})

Stamp Duty Rates

Stamp Duty Rates. UK SDLT rates and thresholds including Rates for Scotland. Key dates for SDLT rate increase in 2016.

www.stampdutycalculator.org.uk

 

[Nikork]

Thanks, I think it is correct apart from the end of the code. 0.03 should be 0.05. Then in works.

=SUMPRODUCT(--(A1>{0;125000;250000}), (A1-{0;125000;250000}), {0;0.02;0.05})

Thank you so much. Very grateful. I might havea project for someone with your skills... PM me if interested.

 

[MARK858]

Thanks, I think it is correct apart from the end of the code. 0.03 should be 0.05

No it should not be 0.05, it should be O.03 it is the difference between the percentages. Check the current rate formula against the online calculator in the link.