New Stamp Duty Calculations

Question

JP1789 Author

Level 1

0 points

New Stamp Duty Calculations

How can I use numbers to calculate the new stamp duty charge? The new stamp duty rates are:

nothing on the first £125k

2% on the next £125k

5% on the next £675K

10% on the next £575k

12% on the rest (above £1.5M)

In excel this formula worked:

=SUMPRODUCT(--(A1>{125000;250000;925000;1500000}), (A1-{125000;250000;925000;1500000}), {0.02;0.03;0.05;0.02})

Note the rates at the end are marginal interest rates, and A1 is the property value.

However, the formula SUMPRODUCT does not seem to work in numbers. Is there a way round this? Any help massively appreciated. Thank you!

Posted on Jan 13, 2015 7:13 AM

Reply

Answer 1

Jan 13, 2015 10:25 AM in response to JP1789

JP,

Numbers supports SUMPRODUCT, but not Array Formulas.

When the number of breakpoints in the calculation is very large, Lookups are the way to go. With only a few, you can get by with Ifs.

IF statements can be easier to setup and understand, so I'll use an example of that.

I color-coded the expression to make it easier to follow:

=IF(A>1500000, 93750+(A−1500000)×12%,IF(A>925000, 36250+(A−925000)×10%, IF(A>250000, 2500+(A−250000)×5%,IF(A>125000, (A−125000)×2%, 0))))

That's the Total With Fee column.

Jerry

Reply

Answer 2

Jan 13, 2015 10:43 AM in response to Jerrold Green1

Regarding the previous post, please note that the column C in the fee table wasn't used for any purpose. Also note that the entire fee table was only used as a manual reference for building the expressions in the Fee Examples.

Jerry

Reply

Answer 3

JP1789 Author

Level 1

0 points

Jan 14, 2015 7:51 AM in response to Jerrold Green1

Hi Jerrold

Thank you so much for your time and effort. It works and I am very grateful!

Julien

Reply

Answer 4

JP1789 Author

Level 1

0 points

Jan 14, 2015 8:09 AM in response to JP1789

Ah there is a problem after all. If A is greater than £925k it is also greater than £250k and £125k. It therefore doesn't know which formula to process and I get an error message.

Reply

Answer 5

Jan 14, 2015 8:17 AM in response to JP1789

JP,

It is possible that there is another problem. Nested IF-statements are processed in the order that they are encountered and found to be true. That is why I began with the highest threshold.

If the amount is greater than 925K, it will apply the if-true argument and skip the rest. Tell me more about how it failed.

Jerry

Reply

Answer 6

JP1789 Author

Level 1

0 points

Jan 14, 2015 9:01 AM in response to Jerrold Green1

Hi Jerrod

Thank you for your quick response. All it says is that there is a syntax error which isn't very helpful.

Regards, Julien

Reply

Answer 7

Jan 14, 2015 9:59 AM in response to JP1789

Julien,

That means that you have entered an invalid formula. Too many, or not enough, parentheses, etc.

Please either send me the file, or Copy the formula and Paste it in your reply here, and maybe we can spot the error.

Jerry

Reply

Answer 8

JP1789 Author

Level 1

0 points

Jan 27, 2015 8:24 AM in response to Jerrold Green1

Hi Jerrold

Apologies for very delayed response. The formula structure is correct. However, for some reason I had to change the multiply sign from X to *. I also changed the percentage figures to decimal equivalents. I am not sure if both these changes were necessary but it does now work. Thank you very much for your help!

Reply

Answer 9

Jan 27, 2015 2:13 PM in response to JP1789

JP,

Thanks for the update. Version 3 alters some of the expression entries from normal keyboard characters to arithmetic symbols, which can be confusing. Version 3 is also intolerant of extra spaces when pasted into an expression.

Keeps us on our toes.

Jerry

Reply

Answer 10

Feb 3, 2015 6:29 AM in response to Jerrold Green1

Thanks, Jerrold - extremely useful. Not being a spreadsheet wiz I'd hunted around for a formula for this for ages (even resorting to Excel forums). Your formula works like a dream. Cheers, mate. 🙂

Reply

Answer 11

ASH2468

Level 1

0 points

May 10, 2015 3:28 PM in response to Jerrold Green1

Hi Jerrold,

I have been searching for a formula like this for hours! Yours seemed to be the perfect soulution (with a few x to * changes etc). I have encountered an error however. I think you touch on it above but not sure where I am going wrong. I am sure it is something that I have done wrong and its not your formula.

This is the formula I have put in excel:

=IF(M10>1500000,93750+(M10-1500000)*0.012,IF(M10>925000,36250+(M10-925000)*0.01, IF(M10>250000,2500+(M10-250000)*0.05,IF(M10>125000,(M10-125000)*0.02,0))))

M10 is the cell I am using as price of property.

I have followed your 'Fee Examples' table and everything is correct up until 950,000+.

If I enter 950000 it gives me 36500 however I believe it should be 38750

Any help you could give me would be hugely appreciated.

Thanks.

Reply