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.

 

[uu_26]

Ah OK, I'm afraid I'm stuck with using OpenOffice at work, will try on Excel when I get home, have always just assumed they were a copy of one another, as everything else I have thrown at it has been identical to excel, thanks again...

 

[makingbillions]

Hi

Thank you all for the wonderful code i have found on this thread. really good, but I am struggling to get this formula to work the way I'd like.

I am hoping to recreate the following table in my excel spreadsheet.

Hope you can help display the fields, I am avoiding doing it with a look up table, just straight formula.

Actual HMRC table is here: HM Revenue & Customs: Land and property Stamp Duty Tax calculator

Thanks
Faisal

 

[oldbrewer]

												lookup table = mytable
											0	0	0	0
											10000	0.2	0	10000
											12000	0.25	400	12000
											40000	0.4	7400	40000
				prevbands	this band	thisrate	thistax	tottax
		salary	45000	7400	5000	0.4	2000	9400
	this is for income tax type bands
	but easy to alter it for stamp duty
	a simple lookup table and a few helper cells
	which can be hidden elsewhere in the spreadsheet
	enter salary here	45000
	tax due is		9400

<colgroup><col width="64" span="16" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[makingbillions]

thanks oldbrewer,

i also found this: =SUMPRODUCT(--(A1>{125000;250000;925000;1500000}), (A1-{125000;250000;925000;1500000}), {0.02;0.03;0.05;0.02})
put your purchase price in A1.

found here: http://answers.microsoft.com/en-us/office/forum/office_2010-excel/formula-to-calculate-new-stamp-duty/035b06aa-90d3-4f41-a90b-156696a8cbc9

 

[MARK858]

i also found this

@makingbillions, that is the same formula that is in posts #8 and #9 of this thread so not much searching was needed.

 

[makingbillions]

Is there any way to do this without a look up table?

 

[oldbrewer]

yes - with nested if after nested if, if you see what I mean.....

I give you a start

if(a1<10000,0,if(a1<12000,a1*.2,if(a1<40000,((a1-12000)*.25)+400,((a1-40000)*.4)+400+7000))))

 

[makingbillions]

Thanks

 

[carlosp_uk]

thanks oldbrewer,

i also found this: =SUMPRODUCT(--(A1>{125000;250000;925000;1500000}), (A1-{125000;250000;925000;1500000}), {0.02;0.03;0.05;0.02})
put your purchase price in A1.

I used this formula, and tested it out with your purchase price of £1,750,000 and it works correctly, giving the stamp duty as £123,750.

So you should definitely check you've input the formula correctly.

Note that the values at the end of the formula (0.02, 0.03, 0.05 and 0.02) are not the same as the interest rates in each band, instead they're the differentials, i.e. how much higher each band is than the previous one.

 

[petertenthije]

nvm