Income Tax Bracket Calculation

[Sydney_Oduro]

Hi everyone,

Im trying to put together a payroll base for my small start-up but Im finding it quite challenging dealing with the income tax calculation bit which requires the use of a progressive tax bracket system.

The income tax bracket system has the following rules -
Chargeable Income Rate (%)
First 319.00 0.0%
Next 100 5.0%
Next 120 10.0%
Next 3,000 17.5%
Next 16,461 25%
Exceeding 20,000 30%

Since there isnt any vlookup in Airtable like in Excel making an if statement is impossible. I am pretty sure there is a simpler way to go about it but I just dont have the insight yet.

Any help is totally appreciated.

[Justin_Barrett]

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.

[Sydney_Oduro]

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.

[Justin_Barrett · Accepted solution]

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.

[Sydney_Oduro]

Thanks again Justin!! It works perfectly well.