Calculate UK Stamp Duty in Excel with a £40,000 Threshold
Question details
The user wants a stamp duty formula to return zero when the property price in F2 is below £40,000.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet uses SUMPRODUCT to calculate a tiered amount from a property purchase price in F2.
- Observed behavior
- The desired formula returns zero below £40,000 and applies the supplied tiered calculation at £40,000 or more.
Enter the purchase price as a number in F2 and choose a different cell for the formula. The supplied bands and rates are illustrative; check the current rules for the property's location and transaction before using the result for tax purposes.
Add an IF condition to the Excel SUMPRODUCT formula
Use this formula to return zero below £40,000 while retaining the supplied tiered calculation at higher prices.
In Excel, select F2 and enter the property price as a number, such as 40000.
Select the cell where you want the result, such as G2, and enter =IF(F2<40000,0,SUMPRODUCT(--(F2>{0;250000;925000;1500000}),(F2-{0;250000;925000;1500000}),{0.03;0.05;0.05;0.02})).
Enter 39999 in F2 to check that the result is 0. Enter 40000 to check that the supplied calculation returns 1200.
Select the result cell and use Home > Number to apply a currency format with the £ symbol.

Calculate the Same Tiered Amount in WPS Spreadsheets
Use an IF formula with MAX in WPS Spreadsheets to apply the £40,000 threshold and the same illustrative rate increments without an array constant.
- 1. Open the spreadsheet: Open WPS Office, select Spreadsheets, and open your workbook or create a blank sheet.
- 2. Enter the price: Select F2 and enter the property purchase price as a number.
- 3. Add the calculation: Select G2 and enter =IF(F2<40000,0,F2*0.03+MAX(0,F2-250000)*0.05+MAX(0,F2-925000)*0.05+MAX(0,F2-1500000)*0.02).
- 4. Test and format the result: Check F2 values of 39999 and 40000, then use the Home number-format controls to display G2 as currency.

Frequently Asked Questions
Does the formula return zero when the price is exactly £40,000?
No. The condition F2<40000 excludes exactly £40,000, so the supplied calculation returns £1,200. To include £40,000 in the zero range, change the condition to F2<=40000.
Where should I enter the formula?
Enter the purchase price in F2 and put the formula in a different cell, such as G2, to avoid a circular reference.
Are these the current UK stamp duty rates?
Not necessarily. The formula reproduces the supplied illustrative bands and rates. Check current rules for the property's location, transaction type, and date before relying on the result.
Why does Excel reject my formula?
Check that F2 contains a number and that the formula was copied completely. Excel's regional settings can also affect formula separators; adjust them to match your Excel settings if needed.




