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
Bookmarks