That can be calculated by doing progressive tests as a series of nested IF() functions. I’ll use a field named {Value} to test; replace that with your actual field name when copying this into your base.
IF(Value <= 319, 0, IF(Value <= 419, .05, IF(Value <= 539, .1, IF(Value <= 3539, .175, IF(Value <= 20000, .25, .3)))))
That will give you a decimal value that you can use in other formulas.
That can be calculated by doing progressive tests as a series of nested IF() functions. I’ll use a field named {Value} to test; replace that with your actual field name when copying this into your base.
IF(Value <= 319, 0, IF(Value <= 419, .05, IF(Value <= 539, .1, IF(Value <= 3539, .175, IF(Value <= 20000, .25, .3)))))
That will give you a decimal value that you can use in other formulas.
Thanks for your response Justin. I’m now getting a percentage rate which falls within the third tier of the income bracket. However, it cant be calculated with just that percentage.
Here is an example of how it should be done with a gross salary of 1,000.
Taxable Amount Rate Tax Paid
319 - 0% - 0.00
100 - 5% - 5.00
120 - 10% - 12.00
406 - 17.5% - 71.05
Therefore the total tax amount paid is 88.05 on a gross salary amount of 1,000
There is a reducing balance sequence here, so the deductions have to be made like this.
Thanks for your response Justin. I’m now getting a percentage rate which falls within the third tier of the income bracket. However, it cant be calculated with just that percentage.
Here is an example of how it should be done with a gross salary of 1,000.
Taxable Amount Rate Tax Paid
319 - 0% - 0.00
100 - 5% - 5.00
120 - 10% - 12.00
406 - 17.5% - 71.05
Therefore the total tax amount paid is 88.05 on a gross salary amount of 1,000
There is a reducing balance sequence here, so the deductions have to be made like this.
Thanks for the clarification. I haven’t seen that type of calculation before (which isn’t terribly surprising), so your initial description got me thinking in the wrong direction. It took a while to wrap my head around the correct logic, but I got it down to a single formula, using an {Amount} field as the source value:
IF(Amount != BLANK(), (MIN(Amount, 319) * 0) +
(MAX(MIN(419, Amount) - 319, 0) * .05) +
(MAX(MIN(539, Amount) - 419, 0) * .1) +
(MAX(MIN(3539, Amount) - 539, 0) * .175) +
(MAX(MIN(20000, Amount) - 3539, 0) * .25) +
(MAX(MAX(20000, Amount) - 20000, 0) * .3))
While working on that formula, I also found that your most recent example was incorrect. Those amounts only add up to 945. That 17.5% tier amount should have been 461, not 406. It threw me at first because I thought my formula was messing things up, but after double-checking each tier, I found that everything was correct.
To verify, I broke each tier out into its own formula field, ensured that those were calculating correctly, then added those tiers together. In the end, it matches the single formula version.
The {Tax} field in this screenshot is the single formula version, while {Tier Total} is the sum of the individual tier fields.
Thanks again Justin!! It works perfectly well.
