Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

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
Question marked as Top-ranking reply

Posted on Jan 13, 2015 10:25 AM

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.


User uploaded file


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

11 replies
Sort By: 
Question marked as Top-ranking reply

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.


User uploaded file


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

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

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

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

New Stamp Duty Calculations

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.