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]

For the property I am calculating, the purchase price is £325K. So that's no stamp duty up to £150K, 2% from £150K up to £250K (£2K) and 5% from £250K up to £325K (£3,750). Therefore the total should be £2,000 + £3,750 = £5,750. Your formula gives a result of £6,250.
We are considering COMMERCIAL property stamp duty cost yes? Thank you again for your help.

 

[Nikork]

You have provided a formula that works for a single property stamp duty and not for a COMMERCIAL property which have different rates.

 

[MARK858]

Which formula are

You have provided a formula that works for a single property stamp duty and not for a COMMERCIAL property which have different rates.

Book1 (1).xlsm
	A	B	C
1	325000.00		5750
Sheet4

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

Same formula as in post number 7

 

[Nikork]

Yes that works, thank you.

 

[MARK858]

You're welcome and just to make the formula shorter

Book1 (1).xlsm
	A	B	C
1	325000.00		5750
Sheet4

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

or if the rates change then you might find it easier with a table

Book1 (1).xlsm
	A	B	C	D	E	F	G
1	325000.00		5750		Thres	rate	diff
2					150000	2.00%	2.00%
3					250000	5.00%	3.00%
Sheet4

Cell Formulas
Range	Formula
C1	C1	=SUMPRODUCT(--(A1>$E$2:$E$3), (A1-$E$2:$E$3), $G$2:$G$3)
G2	G2	=F2
G3	G3	=F3-F2

 

[Nikork]

I work in property development and I need someone to build a sheet which can assess development opportunities taking into account financials, market trends, income, GDV, re-valuation etc etc. I have various calculators but they are more suited to simple residential buy to lets and flips. WOuld you be interested in a project?

 

[MARK858]

No I am afraid I get too tied up with my own work (this I just do out of interest). MrExcel does have a Consulting Services site, see the link below.

Consulting Services

Are you stuck doing a tedious manual process day after day? This can be automated using VBA macros behind your Excel worksheets. For over twenty years, these Excel consultants work with customers via e-mail or telephone to build a solution.

www.mrexcel.com

 

[Nikork]

Excellent. You are too kind. I will buy you a beer or a coffee if you are ever in London or Manchester.