Stamp Duty Calculator

[sblatch]

I want to be able to calculate the UK Stamp Duty on land purchases using an Excel formula. Shown below is the current rates.

Purchase price/lease premium or transfer value​	SDLT rate​
Up to £125,000​	Zero​
Over £125,000 to £250,000​	1%​
Over £250,000 to £500,000​	3%​
Over £500,000 to £1 million​	4%​
Over £1 million to £2 million​	5%​
Over £2 million	7%

<thead style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; "> </thead><tbody style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; ">
</tbody>

As you can see there are various rates, which are applicable to the whole value. i.e.
If the land is worth £270,000 the stamp duty would be 3% which would need to be shown. This would then be calculated. C1 "3%" and D1 "£90,000"

I am sure this can be done but is beyond my basic knowledge.

Any help would be appreciated.

 

[skorpionkz]

add it in row 1

=IF(A1<=125000,0,IF(AND(A1>125000,A1<=250000),A1*1%,IF(AND(A1>250000,A1<=500000),A1*3%,IF(AND(A1>500000,A1<=1000000),A1*4%,IF(AND(A1>1000000,A1<=2000000),A1*5%,IF(A1>2000000,A1*7%,""))))))

 

[sblatch]

Thanks skorpionkz for amazingly quick response. Simple now I've seen it, but I didn't have a clue where to start. Nesting the if statement makes for a powerful option.

 

[domster62]

skorpionkz

Great formula - I am trying to update it as a result of the change in UK Stamp Duty announced 3rd December 2014 but keep getting an error message - I copied and modified the formula you provided but obviously got one bit wrong.

The new rates are


Up to £125,000 : 0%
£125,001 to £250,000 : 2%
£250,001 to £925,000 : 5%
£925,001 to £1.5m : 10%
Above £1.5m : 12%

 

[oldbrewer]

Up to £125,000 : 0%
£125,001 to £250,000 : 2%
£250,001 to £925,000 : 5%
£925,001 to £1.5m : 10%
Above £1.5m : 12%
												1	0
												125001	0.02
												250001	0.05
												925001	0.1
												15000001	0.12
												999999999	0.12
				price	duty
				77000	0	aaaaa
				122000	0
				167000	3340
				212000	4240
				257000	12850
				302000	15100
				347000	17350
				392000	19600
				437000	21850
				482000	24100
				527000	26350
				572000	28600
				617000	30850
				662000	33100
				707000	35350
				752000	37600
				797000	39850
				842000	42100
				887000	44350
				932000	93200
				1300000	130000
				1700000	170000
				5000000	500000
				99000000	11880000
		formula in cell marked aaaaa
		=VLOOKUP(E16,mytable,2)*E16

<colgroup><col span="12"><col><col span="3"></colgroup><tbody>
</tbody>

 

[pjmorris]

Try this thread:

http://www.mrexcel.com/forum/excel-questions/822156-december-2014-uk-stamp-duty-changes.html

its a smart use of SUMPRODUCT, but not that you need to calculate the marginal rate of Stamp Duty.

Note that this is not my work, but Adam who made the post.

Regards

 

[domster62]

Thanks for your reply - I tried it and I don't think it is quite right - the new Stamp Duty Regime means the first £125,000.00 is exempt and the bands operate a bit like the tax rate, you only pay the higher rate on the value above the band not on the entire sum. So a value of 126000 would have a liability of £1000 at the band rate, a property of 255000 value would have £125000 liable to the rate of 2% and £5000 liable to the rate of 5%.

 

[pjmorris]

Domster,

The solution in the other post was this: =SUMPRODUCT(--(A1>{125000;250000;925000;1500000}), (A1-{125000;250000;925000;1500000}), {0.02;0.03;0.05;0.02}) Answer was curtesy of MARK858.
Note the rates at the end are marginal interest rates, and A1 is the location of the property value for which SD is to be calculated.

 

[MARK858]

Domster62 , not sure which post you are referring to in post #7 but the results (and breakdown) for 255000 using the formula I posted is below.

Excel Workbook
	A	C	D
1	255000		2750
2
3	125000	1st 125000 @ 0%	0
4	125000	next 125000 @ 2%	2500
5	5000	next 5000 @ 5%	250
6		Total	2750
Sheet3

and a comparison with the results oldbrewer got is below.

Excel Workbook
	A	C	D
14		oldbrewer	MARK858
15	122000	0	0
16	167000	3340	840
17	212000	4240	1740
18	257000	12850	2850
19	302000	15100	5100
20	347000	17350	7350
21	392000	19600	9600
22	437000	21850	11850
23	482000	24100	14100
24	527000	26350	16350
25	572000	28600	18600
26	617000	30850	20850
27	662000	33100	23100
28	707000	35350	25350
29	752000	37600	27600
30	797000	39850	29850
31	842000	42100	32100
32	887000	44350	34350
33	932000	93200	36950
34	1300000	130000	73750
35	1700000	170000	117750
36	5000000	500000	513750
37	99000000	11880000	11793750
Sheet3

 

[domster62]

Peter,

Thanks - your suggestion appears to work in line with my expectations, much appreciated.