Complex if statement returns incorrect results

Hi, this is my first post, please be gentle.

I am building a fairly detailed taxation system model for a project. There are thousands of formulas (and pages), and the goal is to measure the effects of about 7 taxes across 8000 taxation units based on generalised price increases. Basically it is a really big tax calculator. This is just context, and the reason I can't post the entire file (it is big).

One element I am using is a layered if statement to estimate taxes (that use a progressive scale, like an income tax). In a nutshell the goal is to say 'the tax value of n taxation units at $x is $y million, based on the taxation system thresholds' - a fairly normal tax calc. Problem is, the excel result differs from a simple manual test- for example, manual calculation for one transaction produces a result of $9.24m, excel yields $7.86m. I need to repeat this calculation for up to 1,000 taxation units, over a 50 year horizon (that includes price changes), and for the results to automatically adjust to changed assumptions for the end user!

The statement is:
=IF(GQ2=0,0,IF((GQ2/$AA2)>=TaxAss!D$12,(GQ2/$AA2)*TaxAss!D$31*$AA2,IF(AND((GQ2/$AA2)>=TaxAss!D$7,(GQ2/$AA2)<TaxAss!D$14),(((GQ2/$AA2)*TaxAss!D$26)+TaxAss!D$19)*$AA2,IF(AND((GQ2/$AA2)>=TaxAss!D$8,(GQ2/$AA2)<TaxAss!D$15),((((GQ2/$AA2)-TaxAss!D$8)*TaxAss!D$27)+TaxAss!D$20)*$AA2,IF(AND((GQ2/$AA2)>=TaxAss!D$9,(GQ2/$AA2)<TaxAss!D$16),((((GQ2/$AA2)-TaxAss!D$9)*TaxAss!D$28)+TaxAss!D$21)*$AA2,IF(AND((GQ2/$AA2)>=TaxAss!D$10,(GQ2/$AA2)<TaxAss!D$17),((((GQ2/$AA2)-TaxAss!D$10)*TaxAss!D$29)+TaxAss!D$22)*$AA2,IF(AND((GQ2/$AA2)>=TaxAss!D$11,(GQ2/$AA2)<TaxAss!D$18),((((GQ2/$AA2)-TaxAss!D$11)*TaxAss!D$30)+TaxAss!D$23)*$AA2,IF((GQ2/$AA2)<=TaxAss!D$13,(GQ2/$AA2)*TaxAss!D$25*$AA2))))))))

I don't think I can simplify it, while maintaining the functionality. As a reference GQ2(total value)/$AA2(#value units) is required to allow for underlying price shocks. 'TaxAss' is the taxation system variables worksheet, the cell refs are the thresholds, marginal rates and the 'cumulative dollar values and I have designed the tax time series to run left-to-right with the ability to change the tax regime (ie the reference above relates to 2014, in 2015 the taxass column is e).

The underlying tax unit value is $450k and by rights the component: IF(AND((GQ2/$AA2)>=TaxAss!D$8,(GQ2/$AA2)<TaxAss!D$15),((((GQ2/$AA2)-TaxAss!D$8)*TaxAss!D$27)+TaxAss!D$20)*$AA2; should work as it has thresholds $301k-$501k. But the result is out, as noted...

This may be abstract, but does anyone have an idea about what might be going wrong???

Regards
amk73

Thanks. What I have tried to do is isolate the sample and provide the full detail of taxass. See attached.

The first sheet gives the AA2 value (a calculated parameter), the context for GQ2 (etc, this will be different for each row), and shows how the statement is applied (the links are broken). To demonstrate the issue, I have included the manual calc based on the legislation.

Hope this helps.
amk

Thanks, this is an interesting approach. I will have a play with this inside the model.

Originally Posted by martindwilson

your sample has links to another workbook and there are ref errors in your if formula
but where are those figures coming from and where is zone 1?

Yes. Unfortunately, other than being very large, I am unable to share each part of the model, as it identifies information I am not able to share. What I have tried to do is isolate the relevant bits for the tax calculation. You can be assured that all inputs into the if statement are performing as required and the AA2 sample is a live example. If you refer to the initial post, the if statement is intact (the ref errors and links in the example are for context - the calculations start at IR2 and go through to KQ1458). The 'zone' language is poor on my part, what I meant is that the tier for a 450000 tax unit is <as shown>. This is based on the part of the if statement: IF(AND((GQ2/$AA2)>=TaxAss!D$8,(GQ2/$AA2)<TaxAss!D$15),((((GQ2/$AA2)-TaxAss!D$8)*TaxAss!D$27)+TaxAss!D$20)*$AA2; where in the example the links would actually be D7, D14, D19 and D26 respectively (and these contain the values I have used in the manual calc).

Hope that helps.

So the goal is to automate the tax calculation for each year, based on whether there is a value in that year, and based on the taxation system (thresholds, values etc) that prevail in that year, over a 50 year horizon.

Sorry to pike, I have to sleep (225am Australian time), will check thoughts in the morning. Thanks

Originally Posted by martindwilson

can you just attach a sheet with say 3 years the thing is all boundaries c6:bc15 appear to be the same
also you understand
Logical results: Value 450000
in zone 301-500, so 8100 + (450k-301k)*4.5%
= 14805
Total for 624 9238320
i have no idea wher you get those values from

Hello again. So the fields you refer to (c6:bc15) are the same at this moment. One of the issues to test when he model is complete is teh senstivity of the results to changes in the tax thresholds over time. Without data in cells b12:b17 and b24:b30 the data will be the same; alterativley any data in those cells will adjust the entire system. It is not beautiful I admit, but it will achieve the objective.

With the calculation: the value of $450,000 is derived in the 'Value Sample' sheet at I4 (just repeated at BJ6. 'zone 301-500' is as mentioned just the tier for a $450,000 tax event. The $8,100 is in TaxAss at d19. The $301k is in TaxAss at D7 (the upper boundary of 500k is at d14). The 4.5% is at TaxAss d26. The number 624 is the number of taxation units or events (it is the same as the AA2 reference which in the example is at A3.

What the manual calculation does is what the i statement should do, which is to select the 301-500k tier and calculate as shown. Cell BJ3 is the paste of the result the model calculates ($7.86m), cell bj9 is the manual result ($9.24m); and I have caluclated the difference 'Error? at $1.38m.

Hope that helps.

Originally Posted by amk73

Hello again. So the fields you refer to (c6:bc15) are the same at this moment. One of the issues to test when he model is complete is teh senstivity of the results to changes in the tax thresholds over time. Without data in cells b12:b17 and b24:b30 the data will be the same; alterativley any data in those cells will adjust the entire system. It is not beautiful I admit, but it will achieve the objective.

With the calculation: the value of $450,000 is derived in the 'Value Sample' sheet at I4 (just repeated at BJ6. 'zone 301-500' is as mentioned just the tier for a $450,000 tax event. The $8,100 is in TaxAss at d19. The $301k is in TaxAss at D7 (the upper boundary of 500k is at d14). The 4.5% is at TaxAss d26. The number 624 is the number of taxation units or events (it is the same as the AA2 reference which in the example is at A3.

What the manual calculation does is what the i statement should do, which is to select the 301-500k tier and calculate as shown. Cell BJ3 is the paste of the result the model calculates ($7.86m), cell bj9 is the manual result ($9.24m); and I have caluclated the difference 'Error? at $1.38m.

Hope that helps.

Thanks for help. Upon reflection it turns out to be an ID-10-T error. Manual calculation did not draw on the advancing
tax scales, so the apples don't compare to the oranges.

I will resume my love affair with excel now.

Regards
amk73

Originally Posted by arlu1201

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.

Thanks for the tip. Yes, am new to this forum idea. Perhaps I am missing it, but I can't see the stars? Will follow procedure next time.