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

Stamp Duty Formula

I'm currently working on a spreadsheet in iWorks Numbers.

I have a formula to work out stamp duty for buying a house that doesn't seem to work in Numbers. I have tried it in Excel online so I know it works as a formula but Numbers can't work it out.

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

Does anyone know how I can alter this to work in Numbers, or do they have an alternative formula that will work in Numbers.

Many thanks,

Michael

MacBook Pro with Retina display, OS X El Capitan (10.11.6)

Posted on Nov 7, 2016 10:45 AM

Reply
Question marked as Top-ranking reply

Posted on Nov 8, 2016 2:30 AM

Hi Michael,


LOOKUP can be your friend here. What you have is a tiered rate calculation.


Taxes on amounts from 0 to 125000 are at a 0% rate.

Amounts in the range 125000 to 250000 are taxed at 2%

Amounts in the range 250000 to 925000 are taxed at 3%

Amounts in the range 925000 to 1500000 are taxed at 5%

Amounts greater than 1500000 are taxed at 2%


Placed on a table, that information looks like this:

User uploaded file

The threshold amounts listed in column A are the amounts at which the tax rate changes.

The rates in column B show the tax rate on amounts between the threshold on that line and the next threshold on the line below.

The tax to threshold amount in column C is the total tax to be paid on amounts up to the threshold on that line.

For example, on an amount of 300000, the tax due would be 2500 + 3% of the 50000 above the 250000 threshold.


The column titles, all of the data in columns A and B, and the 0 in C2 are directly entered. The remaining four values in column C are calculated using the formula below, entered into C3 and filled down to C6:


C3: C2+(A3−A2)×B2


The Tax Rates table is used as a Lookup table by the formula in the Main table, shown below.

User uploaded file

Column A contains demonstration amounts. The first four pairs are amounts at and one more than one of the threshold amounts. the final pair contain amounts that are above the highest threshold, and respectively five hundred thousand above the amount in row 8 and five million above the amount in row 10.


the formula is entered in cell C2, and filled down to the end of column C.


C2: LOOKUP(A2,Tax Rates::A,Tax Rates::C)+(A2−LOOKUP(A2,Tax Rates::A))×LOOKUP(A2,Tax Rates::A,Tax Rates::B)


The formula has three parts:


  1. LOOKUP(A2,Tax Rates::A,Tax Rates::C)+
  2. (A2−LOOKUP(A2,Tax Rates::A))×
  3. LOOKUP(A2,Tax Rates::A,Tax Rates::B)


Part 1 looks up the amount in A2 of 'this table' in column A of Tax Rates, finds the row of column A containing the largest value equal to or less than the search value, and returns the amount of tax due on that threshold amount from column C of Tax Rates.


Part 2 gets the value in A2 of 'this table'. The LOOKUP function then looks up the value in A2 in column A of Tax Rate, and because there is no third argument in this LOOKUP, returns the value it finds. The found value is subtracted from the value retrieved from A2, giving the amount subject to the marginal tax rate for amounts above that threshold.


Part 3 performs the same lookup, but returns the marginal tax rate from the same row of column B of the Tax Rates table.


The results of parts 2 and 3 are multiplied, and the result added to the result of part 1. The end result, the total tax due on the amount in A2, is returned to B2, the cell containing the formula.


Should tax rates change, the only changes needed in the tables are to the rates in column B of Tax Rates.

Should the threshold amounts change, the only changes needed in the tables are to the threshold amounts in column A of Tax Rates.

Should a new tier be added, the only changes needed in the tables are to add a row for the new tier, fill the formula into that row, and adjust the thresholds and rates to match the new tiers and rates.


Regards,

Barry

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

Nov 8, 2016 2:30 AM in response to Saxotone

Hi Michael,


LOOKUP can be your friend here. What you have is a tiered rate calculation.


Taxes on amounts from 0 to 125000 are at a 0% rate.

Amounts in the range 125000 to 250000 are taxed at 2%

Amounts in the range 250000 to 925000 are taxed at 3%

Amounts in the range 925000 to 1500000 are taxed at 5%

Amounts greater than 1500000 are taxed at 2%


Placed on a table, that information looks like this:

User uploaded file

The threshold amounts listed in column A are the amounts at which the tax rate changes.

The rates in column B show the tax rate on amounts between the threshold on that line and the next threshold on the line below.

The tax to threshold amount in column C is the total tax to be paid on amounts up to the threshold on that line.

For example, on an amount of 300000, the tax due would be 2500 + 3% of the 50000 above the 250000 threshold.


The column titles, all of the data in columns A and B, and the 0 in C2 are directly entered. The remaining four values in column C are calculated using the formula below, entered into C3 and filled down to C6:


C3: C2+(A3−A2)×B2


The Tax Rates table is used as a Lookup table by the formula in the Main table, shown below.

User uploaded file

Column A contains demonstration amounts. The first four pairs are amounts at and one more than one of the threshold amounts. the final pair contain amounts that are above the highest threshold, and respectively five hundred thousand above the amount in row 8 and five million above the amount in row 10.


the formula is entered in cell C2, and filled down to the end of column C.


C2: LOOKUP(A2,Tax Rates::A,Tax Rates::C)+(A2−LOOKUP(A2,Tax Rates::A))×LOOKUP(A2,Tax Rates::A,Tax Rates::B)


The formula has three parts:


  1. LOOKUP(A2,Tax Rates::A,Tax Rates::C)+
  2. (A2−LOOKUP(A2,Tax Rates::A))×
  3. LOOKUP(A2,Tax Rates::A,Tax Rates::B)


Part 1 looks up the amount in A2 of 'this table' in column A of Tax Rates, finds the row of column A containing the largest value equal to or less than the search value, and returns the amount of tax due on that threshold amount from column C of Tax Rates.


Part 2 gets the value in A2 of 'this table'. The LOOKUP function then looks up the value in A2 in column A of Tax Rate, and because there is no third argument in this LOOKUP, returns the value it finds. The found value is subtracted from the value retrieved from A2, giving the amount subject to the marginal tax rate for amounts above that threshold.


Part 3 performs the same lookup, but returns the marginal tax rate from the same row of column B of the Tax Rates table.


The results of parts 2 and 3 are multiplied, and the result added to the result of part 1. The end result, the total tax due on the amount in A2, is returned to B2, the cell containing the formula.


Should tax rates change, the only changes needed in the tables are to the rates in column B of Tax Rates.

Should the threshold amounts change, the only changes needed in the tables are to the threshold amounts in column A of Tax Rates.

Should a new tier be added, the only changes needed in the tables are to add a row for the new tier, fill the formula into that row, and adjust the thresholds and rates to match the new tiers and rates.


Regards,

Barry

Reply

Nov 8, 2016 6:04 PM in response to Saxotone

Hi Saxotone,


Thanks for the green checkmark.


Looking at your nested IFs version gave me an AHA! moment…


It appears I was reading the Excel formula incorrectly—assuming that the 'rates' shown were individual rather than additive.


Your IFs example, expanded to show relationship with my Lookup table values:

IF(CELL>

1500000;93750+(CELL−1500000)×12%;

IF(CELL>

925000;36250+(CELL−925000)×10%;

IF(CELL>

250000;2500+(CELL−250000)×5%;

IF(CELL>

125000;(CELL−125000)×2%;

0))))


Here's a shot of the tables in my first post, with the tax rates edited to match those in your nested IFs example:

User uploaded file


The logic in the two approaches is similar—

  • IF tests for a value greater than the largest threshold value, and; if found, uses the 'tax to threshold' value (contained in the formula) and adds the product of (the true value minus the threshold value) and the tax rate above that value; if not found, passes to the next IF, which repeats the process with the next highest threshold value...
  • LOOKUP performs the same function as the four IFs, scanning the values in column A until it reaches the highest value for which the IFs would return 'TRUE', and returning the 'tax to threshold' value, the threshold value, and the appropriate tax rate with which to perform the same calculations as would be done by IF.


Either will work, of course, and using four IFs, while above my 'use this' threshold, is still within the range where nested IFs are readable and editable without much trouble.


Regards,

Barry

Reply

Nov 8, 2016 2:31 AM in response to Saxotone

Here is a Numbers equivalent for that formula that doesn't require a lot of nested IFs. Simply list the top and bottom of the relevant rate bracket in each term of the equation, e.g. 250000 and 125000, 925000 and 250000, 15000000 and 925000 and 10000000 and 1500000. I assumed 10000000 would be enough for the very top! Simply make it a larger number if needed.


=MAX(0,(MIN(250000,$A1)−125000))×0.02+MAX(0,(MIN(925000,$A1)−250000))×0.05+MAX(0 ,(MIN(1500000,$A1)−925000))×0.1+MAX(0,(MIN(10000000,$A1)−1500000))×0.12



User uploaded file


SG

Reply

Nov 7, 2016 5:52 PM in response to Saxotone

Hi Michael,


Here is a screenshot confirming that my formula suggested above is equivalent to the one in Excel. Excel is pictured on the top part of the screenshot, Numbers on the bottom. As you can see, the results are consistent.


User uploaded file

If you decide to go with a LOOKUP approach (which is bulkier, but somewhat easier to revise if the brackets and rates should change in the future, then you will need to adjust Barry's solution to have an equivalent to the Excel array formula, which effectively applies a rate of 12% (0.12) on amounts above 1,500,000).


SG

Reply

Nov 9, 2016 9:27 AM in response to Saxotone

Saxotone wrote:


I know that it is a lot of nested IF statements but actually is more logical for me to follow.



Hi Michael,


Here is an explanation of the solution posted above that –


  • —provided the correct results the first time
  • —was tested to be equivalent to the Excel formula before posting
  • —works not just in Numbers but also in Excel
  • —is easier to maintain than nested ifs because it’s just a chain of added terms, each of which can be changed, some taken out, and new ones easily added, without worrying about the structure of an entire logic chain.


The solution posted was this (with each term of the expression shown here on a separate line):


=MAX(0,(MIN(250000,$A1)−125000))×0.02

+MAX(0,(MIN(925000,$A1)−250000))×0.05

+MAX(0 ,(MIN(1500000,$A1)−925000))×0.1

+MAX(0,(MIN(10000000,$A1)−1500000))×0.12



The green numbers are the top of each range, while the red numbers are the bottom of each. No need to worry about calculating thresholds.


To see how this works assume the value in A1 is 500,000.


The first term,


MAX(0,(MIN(250000,$A1)−125000))×0.02


will take the smaller of 250000 or 500000, subtract 125000 from that, and if it’s a positive number multiply it by 0.02.


The second term,


MAX(0,(MIN(925000,$A1)−250000))×0.05


will take the smaller of 925,000 or 500,000, subtract 250,000 from that, and if the result is positive multiply it by .05.


The third term,


MAX(0 ,(MIN(1500000,$A1)−925000))×0.1


will take the smaller of 1,500,000 or 500,000, subtract 925,000 from that, and if the result is positive multiply it by 0.10. (In this case it’s not a positive number so 0 is added.)


MAX(0,(MIN(10000000,$A1)−1500000))×0.12


will take the smaller of 10,000,000 or 500,000, subtract 1,500,000 from that, and if the result is positive multiply it by 0.12. (In this case it’s not a positive number so 0 is added.)


This kind of MAX MIN combination is often used for tiered calculations like this, especially if the brackets may change from time to time. Give it a try!🙂


SG

Reply

Nov 8, 2016 2:40 AM in response to Saxotone

I actually found that the language I was using in Numbers was incorrect.


The formula as I have it working is:


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


I know that it is a lot of nested IF statements but actually is more logical for me to follow.


Thank you so much for everyones help. I am looking at both way to help me understand why and how they are different but still work.


Warm regards,


Michael

Reply

Stamp Duty Formula

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