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

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

This is the calculation from 1 March 2025 Onwards for additional properties.

in g sheet, create an Apps Script and reference it with =StampDuty(A1) where A1 has the price of the house.

function StampDuty(price) {
  if (price <= 125000) {
    return price * 0.05;
  } else if (price <= 250000) {
    return (125000 * 0.05) + ((price - 125000) * 0.07);
  } else if (price <= 925000) {
    return (125000 * 0.05) + (125000 * 0.07) + ((price - 250000) * 0.10);
  } else if (price <= 1500000) {
    return (125000 * 0.05) + (125000 * 0.07) + (675000 * 0.10) + ((price - 925000) * 0.15);
  } else {
    return (125000 * 0.05) + (125000 * 0.07) + (675000 * 0.10) + (575000 * 0.15) + ((price - 1500000) * 0.17);
  }
}

This is the calculation from 1 March 2025 Onwards for additional properties.

in g sheet, create an Apps Script and reference it with =StampDuty(A1) where A1 has the price of the house.

function StampDuty(price) {
  if (price <= 125000) {
    return price * 0.05;
  } else if (price <= 250000) {
    return (125000 * 0.05) + ((price - 125000) * 0.07);
  } else if (price <= 925000) {
    return (125000 * 0.05) + (125000 * 0.07) + ((price - 250000) * 0.10);
  } else if (price <= 1500000) {
    return (125000 * 0.05) + (125000 * 0.07) + (675000 * 0.10) + ((price - 925000) * 0.15);
  } else {
    return (125000 * 0.05) + (125000 * 0.07) + (675000 * 0.10) + (575000 * 0.15) + ((price - 1500000) * 0.17);
  }
}

This code didnt work for me, but this did:

function stampDuty(price) {
  if (isNaN(price) || price <= 0) return "Invalid Price";

  let tax = 0;

  const bands = [
    { threshold: 1500000, rate: 0.12 },
    { threshold: 925000, rate: 0.10 },
    { threshold: 250000, rate: 0.05 },
    { threshold: 125000, rate: 0.02 },
    { threshold: 0, rate: 0.00 },
  ];

  for (let i = 0; i < bands.length; i++) {
    const band = bands[i];
    if (price > band.threshold) {
      tax += (price - band.threshold) * band.rate;
      price = band.threshold;
    }
  }

  return tax;
}