EXCEL STAMP DUTY CALCUALTOR

Question

Hi folks, I have been using this formula to calculate stamp duty on possible UK house purchases (residential not investment):

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

... but need to update it to reflect the changes due to kick in from 1st April 2025

Up to £125,000	Zero
The next £125,000 (the portion from £125,001 to £250,000)	2%
The next £675,000 (the portion from £250,001 to £925,000)	5%
The next £575,000 (the portion from £925,001 to £1.5 million)	10%
The remaining amount (the portion above £1.5 million)	12%

Could one of you lovely people that has much more experience than I confirm what i need to tweak please?

Many thanks, Mark

Answer 1

This formula never worked.

• The duty on a purchase of 126,000 should be 2% of the 1,000 over the 125,000 limit which is 20. Your formula yields 0.
• The duty on a purchase of 251,000 should be 2% of 125,000 (250,000-125,000) + 5% of the remaining 1,000 over 250,000. This is 2,550. Your formula yields 50.

Each percentage applies only to that portion of the price that falls within its limits as described in the second example above. The formula for calculating this portion is

MAX(0,MIN(upper_limit-lower_limit,price-lower_limit))

Therefore, the formula you would like to use is

=SUMPRODUCT(--(B12>{125000;250000;925000;1500000}),
            {MAX(0,MIN(250000-125000,B12-125000));
             MAX(0,MIN(925000-250000,B12-250000));
             MAX(0,MIN(1500000-925000,B12-925000));
             MAX(0,B12-1500000)},
            {0.02;0.05;0.10;0.12})

Each of the three arguments of SUMPRODUCT contains an array literal. Unfortunately, an array literal cannot contain function references. Thus the second argument results in a syntax error. The next best option appears to a sequence of IF expressions:

=IF(B12>125000,0.02*MIN(250000-125000,B12-125000),0)+
 IF(B12>250000,0.05*MIN(925000-250000,B12-250000),0)+
 IF(B12>925000,0.10*MIN(1500000-925000,B12-925000),0)+
 IF(B12>1500000,0.12*(B12-1500000),0)

If you are willing to use cells to hold the limits and percentages, there are LOOKUP formulas tat are easier on the eyes.

Answer 2

Many thanks. It did work because before the changes due to kick in in April 2025, the % was zero up top £250k, but very many thanks for your time, it's much appreciated.

Answer 3

If you are using a modern Excel version and want to avoid nested IF's with hard-coded limits in your formula, then consider using the formula demonstrated in the picture below.

You need to create a small table as seen in B2:C7. Then, create some named ranges as mentioned in E4:F7. Then the formula in C12 will calculate the stamp duty for all amounts listed below "Purchase Price" in one go.

=LET(
    diff, IFERROR(rate - rateshift, first),
    BYROW(
        B12:B16,
        LAMBDA(r,
            SUMPRODUCT((r - lower) * diff, --(r - lower > 0))
        )
    )
)