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.

[TABLE="width: 387"]
<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; ">[TR]
[TH="class: colHeader, bgcolor: #F5F5D6, align: left"]

Purchase price/lease premium or transfer value​

[/TH]
[TH="class: colHeader, bgcolor: #F5F5D6, align: left"]

SDLT rate​

[/TH]
[/TR]
</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; ">[TR]
[TD="align: left"]

Up to £125,000​

[/TD]
[TD="align: left"]

Zero​

[/TD]
[/TR]
[TR]
[TD="align: left"]

Over £125,000 to £250,000​

[/TD]
[TD="align: left"]

1%​

[/TD]
[/TR]
[TR]
[TD="align: left"]

Over £250,000 to £500,000​

[/TD]
[TD="align: left"]

3%​

[/TD]
[/TR]
[TR]
[TD="align: left"]

Over £500,000 to £1 million​

[/TD]
[TD="align: left"]

4%​

[/TD]
[/TR]
[TR]
[TD="align: left"]

Over £1 million to £2 million​

[/TD]
[TD="align: left"]

5%​

[/TD]
[/TR]
[TR]
[TD="align: left"]Over £2 million[/TD]
[TD="align: left"]7%[/TD]
[/TR]
</tbody>[/TABLE]
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.

 

[petertenthije]

You can better use a INDEX/MATCH formua. This makes it a lot easier to maintain your formulas in case the government modifies the rates or price bands. First start by creating a table like this. Note that I start the table with 0, this is critical for the formula to work!

	Column A	Column B	Column C
Row 1	Puchase price	SDLT rate	Spend previous band(s)
Row 2	0	0,00	0
Row 3	125000	0,00	=A2*B2+C2
Row 4	250000	0,02	=A3*B3+C3
Row 5	925000	0,05	=A4*B4+C4
Row 6	1500000	0,10	=A5*B5+C5

<tbody>
</tbody>

Assume you placed the purchase price in cell E1, then for formula becomes like this:

=(E1-INDEX($A:$A;MATCH(E1;$A:$A;1);1))*INDEX($B:$B;MATCH(E1;A:A;-1);1)+INDEX($C:$C;MATCH(E1;$A:$A;1);1)

 

[MARK858]

You can better use a INDEX/MATCH formua

@petertenthije, See the question in post #26

 

[shivshev]

I've tried using the SUMPRODUCT formula for calculating the SDLT totals, however this only works for me when the first rate bracket is 0%

Can anyone help with calculating the below updated rates?

[TABLE="class: cms_table, width: 387"]
<tbody>[TR]
[TH="class: cms_table_colHeader, bgcolor: #F5F5D6, align: center"]

Band​

[/TH]
[TH="class: cms_table_colHeader, bgcolor: #F5F5D6, align: center"]

SDLTrate​

[/TH]
[/TR]
[TR]
[TD="align: left"]Up to £125,000
[/TD]
[TD="align: left"]3%
[/TD]
[/TR]
[TR]
[TD="align: left"]£125,000 to £250,000
[/TD]
[TD="align: left"]5%
[/TD]
[/TR]
[TR]
[TD="align: left"]£250,000 to £925,000
[/TD]
[TD="align: left"]8%
[/TD]
[/TR]
[TR]
[TD="align: left"]£925,000 to £1.5 million
[/TD]
[TD="align: left"]13%
[/TD]
[/TR]
[TR]
[TD="align: left"]£1.5 million +
[/TD]
[TD="align: left"]15%
[/TD]
[/TR]
[TR]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[/TR]
</tbody>[/TABLE]


Thanks!!

 

[AlanY]

try this

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

 

[shivshev]

try this

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

Thank you so much! Also... do you know the formula I would use to populate the individual "SDLT Payable" cell, based on the property price?

e.g. for a £350,000 property:

[TABLE="width: 361"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Band [/TD]
[TD]SDLT rates[/TD]
[TD]SDLT payable (£)[/TD]
[/TR]
[TR]
[TD]£0-£125k[/TD]
[TD="align: center"]3%[/TD]
[TD="align: center"]3,750[/TD]
[/TR]
[TR]
[TD]£125k-£250k[/TD]
[TD="align: center"]5%[/TD]
[TD="align: center"]6,250[/TD]
[/TR]
[TR]
[TD]£250-£925k[/TD]
[TD="align: center"]8%[/TD]
[TD="align: center"]8,000[/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD="align: center"][/TD]
[TD="align: center"]18,000[/TD]
[/TR]
</tbody>[/TABLE]

 

[oldbrewer]

[TABLE="width: 1004"]
<colgroup><col><col><col><col><col><col><col span="9"></colgroup><tbody>[TR]
[TD="colspan: 2"]e.g. for a £350,000 property:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]tier[/TD]
[TD]baldeduct[/TD]
[TD]rate[/TD]
[TD]prevtier(s)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.03[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Band[/TD]
[TD]SDLT rates[/TD]
[TD]SDLT payable (£)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]125000[/TD]
[TD="align: right"]125000[/TD]
[TD="align: right"]0.05[/TD]
[TD="align: right"]3750[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]£0-£125k[/TD]
[TD="align: right"]3%[/TD]
[TD="align: right"]3,750[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]250000[/TD]
[TD="align: right"]250000[/TD]
[TD="align: right"]0.08[/TD]
[TD="align: right"]10000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]£125k-£250k[/TD]
[TD="align: right"]5%[/TD]
[TD="align: right"]6,250[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]925000[/TD]
[TD="align: right"]925000[/TD]
[TD="align: right"]0.08[/TD]
[TD="align: right"]64000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]£250-£925k[/TD]
[TD="align: right"]8%[/TD]
[TD="align: right"]8,000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD][/TD]
[TD="align: right"]18,000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]once you set up this lookup table[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]any amount can be entered[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]and total tax due calculated[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]amount[/TD]
[TD]tax due[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]109000[/TD]
[TD="align: right"]3270[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]125000[/TD]
[TD="align: right"]3750[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]126000[/TD]
[TD="align: right"]3800[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]note the last amount double checks it[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]250000[/TD]
[TD="align: right"]10000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]251000[/TD]
[TD="align: right"]10080[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]925000[/TD]
[TD="align: right"]64000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]926000[/TD]
[TD="align: right"]64080[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]927000[/TD]
[TD="align: right"]64160[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1000000[/TD]
[TD="align: right"]70000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]350000[/TD]
[TD="align: right"]18000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Biz]

[TABLE="width: 1004"]
<tbody>[TR]
[TD="colspan: 2"]e.g. for a £350,000 property:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]tier[/TD]
[TD]baldeduct[/TD]
[TD]rate[/TD]
[TD]prevtier(s)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.03[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Band[/TD]
[TD]SDLT rates[/TD]
[TD]SDLT payable (£)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]125000[/TD]
[TD="align: right"]125000[/TD]
[TD="align: right"]0.05[/TD]
[TD="align: right"]3750[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]£0-£125k[/TD]
[TD="align: right"]3%[/TD]
[TD="align: right"]3,750[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]250000[/TD]
[TD="align: right"]250000[/TD]
[TD="align: right"]0.08[/TD]
[TD="align: right"]10000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]£125k-£250k[/TD]
[TD="align: right"]5%[/TD]
[TD="align: right"]6,250[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]925000[/TD]
[TD="align: right"]925000[/TD]
[TD="align: right"]0.08[/TD]
[TD="align: right"]64000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]£250-£925k[/TD]
[TD="align: right"]8%[/TD]
[TD="align: right"]8,000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD][/TD]
[TD="align: right"]18,000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]once you set up this lookup table[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]any amount can be entered[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]and total tax due calculated[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]amount[/TD]
[TD]tax due[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]109000[/TD]
[TD="align: right"]3270[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]125000[/TD]
[TD="align: right"]3750[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]126000[/TD]
[TD="align: right"]3800[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]note the last amount double checks it[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]250000[/TD]
[TD="align: right"]10000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]251000[/TD]
[TD="align: right"]10080[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]925000[/TD]
[TD="align: right"]64000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]926000[/TD]
[TD="align: right"]64080[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]927000[/TD]
[TD="align: right"]64160[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1000000[/TD]
[TD="align: right"]70000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]350000[/TD]
[TD="align: right"]18000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Try below

Sheet1

	A	B	C	D
amount	tax due

<colgroup><col style="WIDTH: 30px; FONT-WEIGHT: bold"><col style="WIDTH: 243px"><col style="WIDTH: 176px"><col style="WIDTH: 64px"><col style="WIDTH: 107px"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="bgcolor: #ffffcc, align: center"]Property Price is Greater Than or Equal To:[/TD]
[TD="bgcolor: #ffffcc, align: center"]But Less Than or Equal to:[/TD]
[TD="bgcolor: #ffffcc, align: center"]Tax Rate[/TD]
[TD="bgcolor: #ffffcc, align: center"]Differential rate[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: center"]£0.00[/TD]
[TD="align: center"]£125,000.00[/TD]
[TD="align: center"]3.0%[/TD]
[TD="align: center"]3.0%[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: center"]£125,000.01[/TD]
[TD="align: center"]£250,000.00[/TD]
[TD="align: center"]5.0%[/TD]
[TD="align: center"]2.0%[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: center"]£250,000.01[/TD]
[TD="align: center"]£925,000.00[/TD]
[TD="align: center"]8.0%[/TD]
[TD="align: center"]3.0%[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: center"]£925,000.01[/TD]
[TD="align: center"]£1,500,000.00[/TD]
[TD="align: center"]13.0%[/TD]
[TD="align: center"]5.0%[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: center"]£1,500,000.01[/TD]

[TD="align: center"]15.0%[/TD]
[TD="align: center"]2.0%[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]
[TD="align: right"]£109,000.00[/TD]
[TD="align: right"]£3,270.00[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]
[TD="align: right"]£125,000.00[/TD]
[TD="align: right"]£3,750.00[/TD]

[TD="bgcolor: #cacaca, align: center"]16[/TD]
[TD="align: right"]£126,000.00[/TD]
[TD="align: right"]£3,800.00[/TD]

[TD="bgcolor: #cacaca, align: center"]17[/TD]
[TD="align: right"]£250,000.00[/TD]
[TD="align: right"]£10,000.00[/TD]

[TD="bgcolor: #cacaca, align: center"]18[/TD]
[TD="align: right"]£251,000.00[/TD]
[TD="align: right"]£10,080.00[/TD]

[TD="bgcolor: #cacaca, align: center"]19[/TD]
[TD="align: right"]£925,000.00[/TD]
[TD="align: right"]£64,000.00[/TD]

[TD="bgcolor: #cacaca, align: center"]20[/TD]
[TD="align: right"]£926,000.00[/TD]
[TD="align: right"]£64,130.00[/TD]

[TD="bgcolor: #cacaca, align: center"]21[/TD]
[TD="align: right"]£927,000.00[/TD]
[TD="align: right"]£64,260.00[/TD]

[TD="bgcolor: #cacaca, align: center"]22[/TD]
[TD="align: right"]£1,000,000.00[/TD]
[TD="align: right"]£73,750.00[/TD]

[TD="bgcolor: #cacaca, align: center"]23[/TD]
[TD="align: right"]£350,000.00[/TD]
[TD="align: right"]£18,000.00[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula D2 =C2 A3 =B2+0.01 D3 =C3-C2 A4 =B3+0.01 D4 =C4-C3 A5 =B4+0.01 D5 =C5-C4 A6 =B5+0.01 D6 =C6-C5 B14 =SUMPRODUCT(--(A14>$A$2:$A$6),(A14-$A$2:$A$6),$D$2:$D$6) B15 =SUMPRODUCT(--(A15>$A$2:$A$6),(A15-$A$2:$A$6),$D$2:$D$6) B16 =SUMPRODUCT(--(A16>$A$2:$A$6),(A16-$A$2:$A$6),$D$2:$D$6) B17 =SUMPRODUCT(--(A17>$A$2:$A$6),(A17-$A$2:$A$6),$D$2:$D$6) B18 =SUMPRODUCT(--(A18>$A$2:$A$6),(A18-$A$2:$A$6),$D$2:$D$6) B19 =SUMPRODUCT(--(A19>$A$2:$A$6),(A19-$A$2:$A$6),$D$2:$D$6) B20 =SUMPRODUCT(--(A20>$A$2:$A$6),(A20-$A$2:$A$6),$D$2:$D$6) B21 =SUMPRODUCT(--(A21>$A$2:$A$6),(A21-$A$2:$A$6),$D$2:$D$6) B22 =SUMPRODUCT(--(A22>$A$2:$A$6),(A22-$A$2:$A$6),$D$2:$D$6) B23 =SUMPRODUCT(--(A23>$A$2:$A$6),(A23-$A$2:$A$6),$D$2:$D$6) <tbody> </tbody>

<tbody>
</tbody>

 

[shivshev]

wow, thank you oldbrewer and biz!

 

[Biz]

Thank you for your feedback.

 

[isdoo]

[COLOR=#000000][FONT=Inconsolata]=[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]SUMPRODUCT[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]-[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]-[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata](A[/FONT][/COLOR][FONT=Inconsolata]1[/FONT][COLOR=#000000][FONT=Inconsolata]>[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]{[/FONT][/COLOR][COLOR=#1155CC][FONT=Inconsolata]125000[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata];[/FONT][/COLOR][COLOR=#1155CC][FONT=Inconsolata]250000[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata];[/FONT][/COLOR][COLOR=#1155CC][FONT=Inconsolata]925000[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata];[/FONT][/COLOR][COLOR=#1155CC][FONT=Inconsolata]1500000[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]}[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata] [/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][FONT=Inconsolata]A1[/FONT][COLOR=#000000][FONT=Inconsolata]-[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]{[/FONT][/COLOR][COLOR=#1155CC][FONT=Inconsolata]125000[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata];[/FONT][/COLOR][COLOR=#1155CC][FONT=Inconsolata]250000[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata];[/FONT][/COLOR][COLOR=#1155CC][FONT=Inconsolata]925000[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata];[/FONT][/COLOR][COLOR=#1155CC][FONT=Inconsolata]1500000[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]}[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata] [/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]{[/FONT][/COLOR][COLOR=#1155CC][FONT=Inconsolata]0.02[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata];[/FONT][/COLOR][COLOR=#1155CC][FONT=Inconsolata]0.03[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata];[/FONT][/COLOR][COLOR=#1155CC][FONT=Inconsolata]0.05[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata];[/FONT][/COLOR][COLOR=#1155CC][FONT=Inconsolata]0.02[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]}[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]+[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][FONT=Inconsolata]A1[/FONT][COLOR=#000000][FONT=Inconsolata]*[/FONT][/COLOR][COLOR=#1155CC][FONT=Inconsolata]0.03[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR]/CODE]

is great.

However how would we ensure that the additional 3% is not charged where a property has a sale price of under 40,000? 

Over 40,000 then the stamp duty is 3% if a second property