1 Reply Latest reply on Jul 27, 2011 10:48 AM by philmodjunk

    Calculating Income Tax



      Calculating Income Tax


      Hi folks...
      I know there has to be a way to do this using either CASE or IF statements but I'm going in circles here.  I need to create a field that will  calculate CURRENT INCOME TAX DEDUCTIONS base on the following tax rates:

      15% on the first $41,544 of taxable income, PLUS...
      22% on the next $41,544 of taxable income (on the portion of taxable income between $41,544 and $83,088), PLUS...
      26% on the next $45,712 of taxable income (on the portion of taxable income between $83,088 and $128,800), PLUS...
      29% of taxable income over $128,800.

      Also, that "taxable income" needs to be a YTD GROSS INCOME calculation field that will total CURRENT EARNINGS with the YTD GROSS INCOME value from the preceding record.  I've tried to do this with the following calcluation but it just gives me a ? in the field.  I suspect I needs to be tweaked so it stops looking at the first  record but I'm not sure how you do this as a calculated value.

      CURRENT EARNINGS + (GetNthRecord ( YTD GROSS INCOME; Get(RecordNumber)-1))

      I've looked on the forum but I can't find anything similar to this problem though I'm sure I'm not the first one to try to calculate income tax on an employee pay sheet. 
      Any help would be much appreciated.  Gotta love income tax law..... Undecided

        • 1. Re: Calculating Income Tax

          CURRENT EARNINGS + (GetNthRecord ( YTD GROSS INCOME; Get(RecordNumber)-1))

          Looks correct, but the results you get will vary depending on your found set. It refers to the previous record in your found set so you'll get a ? if your currrent record is the first or only record in your found set--that's one possibility. You can also get a ? if your field is too narrow to display the fully formatted value. If this is the case, you can click into the field and then see the value. That's a quick test to tell you if you need to resize the field or to take another look at your found set, sort order and current record again.

          Once that's resolved, here's the calculation you wanted:

           ≤  ≥

          Let ( [ A = YourTable::TaxableIncomeField ; Tier1 = 41544 ; Tier2 = 45712 ; Tier3 = 138,800 ] ;
                  0.15 * Min ( A ; Tier1 )  + 0.22 * Min ( A - Tier1 ; Tier1 ) * ( A ≥ tier1 ) + 0.26 * Min ( A - 2 * Tier1 ; Tier2 ) * ( A ≥ 2 * Tier1 ) + 0.29 * ( A - Tier3 ) * ( A > Tier3 )

          The boolean expressions such as A > Tier3 will evaluate as 0 when false and this converts each such term to a 0 if the expression is false to keep the Min functions from returning negative values when they should be zero.

          I wouldn't actually implement such a calculation however. Tax rates and the "tiers" can and will change and I wouldn't want to create solution where I need to modify such a calculation everytime the powers that be modify tax law. Instead, I'd load the rates, tiers and possibly a text field with an expression in a table and then craft an expression that uses such a table of information instead of such "hardwired" values. That way, the users can adapt to changing rates, etc. by updating this table instead of having to contact the developer.