This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Privacy Policy. Click Preferences to customize your cookie settings.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Airtable Community
- Discussions
- Ask A Question
- Formulas
- Re: Income Tax Bracket Calculation

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Solved

Jump to Solution

0
524
0

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Sep 19, 2020 08:23 PM

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.

Solved! Go to Solution.

Reply

1 Solution

Accepted Solutions

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Sep 21, 2020 10:09 PM

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.

Reply

4 Replies 4

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Sep 19, 2020 09:29 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Sep 20, 2020 04:12 AM

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.

Reply

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Sep 21, 2020 10:09 PM

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.

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Sep 23, 2020 02:12 AM

Thanks again Justin!! It works perfectly well.