Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Tax Rate Schedule Formula

Topic Labels: Formulas
Solved
Jump to Solution
1179 2
cancel
Showing results for 
Search instead for 
Did you mean: 
andrew_brase
4 - Data Explorer
4 - Data Explorer

Hello Airtable community!

I’ve been cobbling together a Base for managing my freelance business which includes tracking bank transactions, expense categories, clients, projects, billable hours, etc.

I’m attempting to recreate the 2021 Tax Rate Schedule for the Single filing status from the 1040-ES. I believe I’ve gotten most of the income tax bracket arguments to trigger properly but am stuck for any amount over $209,425.00.

Adding even $0.01 to that quantity ends up with a seemingly empty result.

Where am I goofing up my formula?

IF(AND(INCOME >= 0, INCOME <= 9950), (INCOME - 0) * .1 + 0,
	IF(AND(INCOME > 9950, INCOME <= 40525), (INCOME - 9950) * .12 + 995,
	IF(AND(INCOME > 40525, INCOME <= 86375), (INCOME - 40525) * .22 + 4664,
	IF(AND(INCOME > 86375, INCOME <= 164925), (INCOME - 86375) * .24 + 14751,
	IF(AND(INCOME > 164925, INCOME <= 209425), (INCOME - 164925) * .32 + 33603,
	IF(AND(INCOME > 209425, INCOME <= 523,600), (INCOME - 209425) * .35 + 47843,
	IF(INCOME > 523600, (INCOME - 523600) * .37 + 157804.25
)))))))

Screen Shot 2021-07-27 at 1.08.29 PM

After manually checking the formula with the upper end value of each bracket, I did notice that the last couple result in floating numbers and am wondering if that has anything to do with it. Tweaking displayed precision didn’t seem to affect anything and I didn’t get anywhere inserting the {ROUND()} function.

{INCOME} - {INCOME OVER) * {PERCENTAGE} + {TAX BASE VALUE} = {TOTAL INCOME TAX}

9950 - 0 * .10 + 0 = 995
40525 - 9950 * .12 + 995 = 4664
86375 - 40525 * .22 + 4664 = 14751
164925 - 86375 * .24 + 14751 = 33603
209425 - 164925 * .32 + 33603 = 47843
523600 - 209425 * .35 + 47843 = 157804.25
523601 - 523600 * .37 + 157804.25 = 157804.62

I doubt I’ll come across the problematic sections of the formula in practical use but I don’t want to lose track of the buggy code.

I’d appreciate any insight. Apologies if the formatting is unclear, I’m learning a lot through this community and hopefully this will help someone else out down the line.

Thanks!

1 Solution

Accepted Solutions
ScottWorld
18 - Pluto
18 - Pluto

You need to get rid of the comma in 523,600.

See Solution in Thread

2 Replies 2
ScottWorld
18 - Pluto
18 - Pluto

You need to get rid of the comma in 523,600.

andrew_brase
4 - Data Explorer
4 - Data Explorer

Ha! I’ve been racking my brain and digging through the forums trying to solve this all afternoon, figures!

Thanks @ScottWorld!

FYI for anyone trying to accomplish something similar, this works (identical to above without the stray “,”)

IF(AND(INCOME >= 0, INCOME <= 9950), (INCOME - 0) * .1 + 0,
	IF(AND(INCOME > 9950, INCOME <= 40525), (INCOME - 9950) * .12 + 995,
	IF(AND(INCOME > 40525, INCOME <= 86375), (INCOME - 40525) * .22 + 4664,
	IF(AND(INCOME > 86375, INCOME <= 164925), (INCOME - 86375) * .24 + 14751,
	IF(AND(INCOME > 164925, INCOME <= 209425), (INCOME - 164925) * .32 + 33603,
	IF(AND(INCOME > 209425, INCOME <= 523600), (INCOME - 209425) * .35 + 47843,
	IF(INCOME > 523600, (INCOME - 523600) * .37 + 157804.25
)))))))