Tax Rate Schedule Formula

[andrew_brase]

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
)))))))

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!

[ScottWorld · Accepted solution]

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

[andrew_brase]

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
)))))))