google sheets - uk stamp duty calculator, new rate (2015)

google-sheets-formula.vb

// put this into a cell and then name a range 'housePrice'
=MIN(MAX(0,housePrice-250000),250000-125000)*0.02 + MIN(MAX(0,housePrice - 250000), 925000-250000) * 0.05 + MIN(MAX(0,housePrice - 9250000), 1500000-925000) * 0.1

Thanks for posting this!

Unfortunately I couldn't get it to work in my sheet though, but this formula will also work:

// Assuming the house price is in cell A1
=SUMPRODUCT(--(A1>{125000;250000;925000;1500000}), (A1-{125000;250000;925000;1500000}), {0.02;0.03;0.05;0.02})

Thanks, very useful. Formula below is adapted for stamp duty on second homes:

// Assuming the house price is in cell A1
=SUMPRODUCT(--(A1>{0;125000;250000;925000;1500000}), (A1-{0;125000;250000;925000;1500000}), {0.03;0.02;0.03;0.05;0.02})

@dalepotter & @trbaldwin Thank you!

This saved me a bunch of time, Thanks :)

This isn't working for me. Can I ask what the--does?

This isn't working for me. Can I ask what the--does?

It changes a true or false value into a 1 or 0.

There is an error in the original formula: there's an extra 0 in the third band calculation (9.5M instead of 950k). I updated the formula to match the rates before October 1st 2021:

=MIN(MAX(0,HousePrice-250000),250000-125000)*0.02 + MIN(MAX(0,HousePrice - 250000), 925000-250000) * 0.05 + MIN(MAX(0,HousePrice - 925000), 1500000-925000) * 0.1 + MAX(0,HousePrice - 1500000) * 0.12

A small mistake there (that makes a big difference), it should be:

=MIN(MAX(0,HousePrice-125000),250000-125000)*0.02 + MIN(MAX(0,HousePrice - 250000), 925000-250000) * 0.05 + MIN(MAX(0,HousePrice - 925000), 1500000-925000) * 0.1 + MAX(0,HousePrice - 1500000) * 0.12

This is very helpful, thank you. Question if I may?
My sheet shows the purchase price of the property as a negative number EG (1,000,000) , that allows me to work out costs and income for a project, costs be negative numbers, is there a way to flip the formula, as this show 0 against a negative number (of course). Works brilliantly for positive number!

Im currently thinking to use ABS() to convert a negative to a positive and then bringing it back again. But feels a bit...heavy