Look up in Tax Table for any given Tax Year

[apurk45]

Hello
As you know in USA we have progressive income tax system. As taxable income increases, so does the tax rate. I'm trying to calculate my amount of Federal Income Taxes during retirement years according to this progressive system.
I have WB with "Tax Brackets" WS where I have this Tax Rates and Brackets table for future tax years (I know, I know - they need to be updated)

In another WS "Income Tax" (in the same WB)
E$6 - Filing Status
E$30 - Taxable Income
E$34 - Tax Year

In E$35 - I am trying calculate amount of income tax in the 1st tax bracket (10%)
E$36 - amount of income tax in the 2nd tax bracket (12%)
E$37 - amount of income tax in the 3rd tax bracket (22%)
E$38 - amount of income tax in the 4th tax bracket (24%)
E$39 - amount of income tax in the 5th tax bracket (32%)
etc...

Here is the WS layout where I would like to have my calculations. (This is are made up number just for ilustrations)

Searching internet I found this various formulas that I managed to string into what I have currently in E35. Unfortunately I am getting "#REF!" error and I'm having really hard time to make it work.
Eventually I would like to have this type formula in E35 - E41

"=SUMIFS(INDEX('Tax Brackets'!$B$3:$E$38, , MATCH(E$6, 'Tax Brackets'!$B$3:$E$3, 0)), 'Tax Brackets'!$A$3:$A$38, "<="&E$34, INDEX('Tax Brackets'!$A$3:$A$38, , MATCH(E$6, 'Tax Brackets'!$B$3:$E$3, 0)), ">"&E$34) * E$30"

I would greatly appreciate your help pointing what I am do doing wrong

Regards.

 

[KRice]

What version of Excel are you using? You can update your account details to show that. Also, it would be helpful (if you are amenable) to copy the year entries down the column. Doing so makes it very convenient to extract the rates and thresholds anywhere the year matches.

 

[Dave Patton]

N.B. Please post the tax brackets and rates that are applicable. The forum provides a tool named XL2BB that lets one post an extract of a sheet to the forum.
Do you need the formula for more than one filing status?
I am not from the USA; consequently, I have minimal knowledge of USA Income taxes.
My suggestion will probably require Excel 365.

 

[KRice]

On the Tax Brackets worksheet, I created an official Excel table called "TaxBrackets" and carried the tax year down the column. I also structured the brackets to use the lower bound taxable income for each bracket (that variable is called lbtiers...lower bound of tiers in the formula). The final part of the formula then...

1. determines which brackets (tiers) matter (where you'll see >lbtiers),
2. computes the difference between the total taxable income and the lower bound thresholds for each tier (which may not be intuitive), and then
3. applies a differential marginal rate to that tier (the step difference in tax rates as one moves through the brackets).

MrExcel_20240505a.xlsx
	A	B	C	D	E	F
1	Taxable Income Greater Than
2
3	Year	Tax Rate	S	MFJ	MFS	HoH
4	2019	10%	$0	$0	$0	$0
5	2019	12%	$9,700	$19,400	$9,700	$13,850
6	2019	22%	$39,475	$78,950	$39,475	$52,850
7	2019	24%	$84,200	$168,400	$84,200	$84,200
8	2019	32%	$160,725	$321,450	$160,725	$160,700
9	2019	35%	$204,100	$408,200	$204,100	$204,100
10	2019	37%	$510,300	$612,350	$306,175	$510,300
11	2020	10%	$0	$0	$0	$0
12	2020	12%	$9,875	$19,750	$9,875	$14,100
13	2020	22%	$40,125	$80,250	$40,125	$53,700
14	2020	24%	$85,525	$171,050	$85,525	$85,500
15	2020	32%	$163,300	$326,600	$163,300	$163,300
16	2020	35%	$207,350	$414,700	$207,350	$207,350
17	2020	37%	$518,400	$622,050	$311,025	$518,400
18	2021	10%	$0	$0	$0	$0
19	2021	12%	$9,950	$19,900	$9,950	$14,200
20	2021	22%	$40,525	$81,050	$40,525	$54,200
21	2021	24%	$86,375	$172,750	$86,375	$86,350
22	2021	32%	$164,925	$329,850	$164,925	$164,900
23	2021	35%	$209,425	$418,850	$209,425	$209,400
24	2021	37%	$523,600	$628,301	$314,150	$523,600
25	2022	10%	$0	$0	$0	$0
26	2022	12%	$10,275	$20,550	$20,275	$14,650
27	2022	22%	$41,775	$83,550	$41,775	$55,900
28	2022	24%	$89,075	$178,150	$89,075	$89,050
29	2022	32%	$170,050	$340,100	$170,050	$170,050
30	2022	35%	$215,950	$431,900	$215,950	$215,950
31	2022	37%	$539,900	$647,850	$323,925	$539,900
32	2023	10%	$0	$0	$0	$0
33	2023	12%	$11,000	$22,000	$11,000	$15,700
34	2023	22%	$44,725	$89,450	$44,725	$59,850
35	2023	24%	$95,375	$190,750	$95,375	$95,350
36	2023	32%	$182,100	$364,200	$182,100	$182,100
37	2023	35%	$231,250	$462,500	$231,250	$231,250
38	2023	37%	$578,125	$693,750	$346,875	$578,100
Tax Brackets

Then on the Income Tax worksheet, if you don't really care to see the tax apportionments among the brackets (it isn't necessary), here is one approach for computing the total tax:

MrExcel_20240505a.xlsx
	D	E	F	G	H	I
6	Filing Status	S	S	S	S	S
29
30	Taxable Income	$ 62,000	$ 72,000	$ 78,000	$ 110,000	$ 92,000
31
32
33
34	Tax Year	2019	2020	2021	2022	2023
35
36
37
38
39
40
41
42	Total Tax	$ 9,499	$ 11,630	$ 12,909	$ 20,236	$ 15,548
Income Tax

Cell Formulas
Range	Formula
E42:I42	E42	=LET(tbl,FILTER(TaxBrackets,TaxBrackets[[Year]:[Year]]=E34),rates,INDEX(tbl,,2),lbtiers,INDEX(tbl,,MATCH(E6,TaxBrackets[#Headers],0)), seq,SEQUENCE(COUNTA(rates)),margrates,IF(seq=1,INDEX(rates,1),INDEX(rates,seq)-INDEX(rates,seq-1)),SUMPRODUCT(--(E30>lbtiers),(E30-lbtiers),margrates))

Named Ranges
Name	Refers To	Cells
'Tax Brackets'!_FilterDatabase	='Tax Brackets'!$A$3:$F$38	E42:I42

Cells with Data Validation
Cell	Allow	Criteria
E6:I6	List	S,MFJ,MFS,HoH

Let me know if you really need the bracket apportionments.

 

[Dave Patton]

An ordinary calculation (rate differential) for Single Status and the selected year.

Use Name Manager to
a) add a New Name that you prefer; I used IncomeTax
b) add the Lambda information to the Value area in Name Manager

The function prompts for the input information

T202405.xlsm
	A	B	C	D
1
2	Income	Year	Status	Tax
3	95,375.00	2023	Single	16,290.00
4
5
6
7	Year	Bracket	Rate
8	2023	0	10%
9	2023	11,000	12%
10	2023	44,725	22%
11	2023	95,375	24%
12	2023	182,100	32%
13	2023	231,250	35%
14	2023	579,125	37%
1a

Cell Formulas
Range	Formula
D3	D3	=IncomeTax(B3,A7:C14,A3)

Lambda Functions
Name	Formula
IncomeTax	=LAMBDA(Year,rngRateInformation,Income,LET(n,Year,Inc,Income,slab,rngRateInformation,cslab,FILTER(slab,CHOOSECOLS(slab,1)=n),csslab,CHOOSECOLS(cslab,2),ccom,CHOOSECOLS(cslab,3),ccom_shft,VSTACK(0,DROP(ccom,-1)),SUM((Inc>csslab)*(Inc-csslab)*(ccom-ccom_shft))))

T202405.xlsm
	A	B	C	D	E
1
2	Income	Year	Status	Tax
3	95,375.00	2023	Single	16,290.00	16,290.00
4
1a

Cell Formulas
Range	Formula
D3	D3	=IncomeTax(B3,A7:C14,A3)
E3	E3	=LET(TI,A3, b, {0;11000;44725;95375;182100;231250;579125}, r, {0.1;0.02;0.1;0.02;0.08;0.03;0.02}, SUM((TI > b) * (TI - b) * r) )

Lambda Functions
Name	Formula
IncomeTax	=LAMBDA(Year,rngRateInformation,Income,LET(n,Year,Inc,Income,slab,rngRateInformation,cslab,FILTER(slab,CHOOSECOLS(slab,1)=n),csslab,CHOOSECOLS(cslab,2),ccom,CHOOSECOLS(cslab,3),ccom_shft,VSTACK(0,DROP(ccom,-1)),SUM((Inc>csslab)*(Inc-csslab)*(ccom-ccom_shft))))

 

[MARK858]

Just in case anyone hasn't noticed it the OP has updated their version to Office 2021

 

[Dave Patton]

With older versions of Excel, use SumProduct.
I do not have Excel 2021, the Sum may be OK.

You can build the relevant formula from a Rate Table.

T202405.xlsm
	A	B	C	D	E
1
2	Income	Year	Status	Tax	Tax
3	95,375.00	2023	Single	16,290.00	16,290.00
4
5
6	Year	Bracket	Rate
7	blank --->
8	2023	0	10%
9	2023	11,000	12%
10	2023	44,725	22%
11	2023	95,375	24%
12	2023	182,100	32%
13	2023	231,250	35%
14	2023	579,125	37%
1a

Cell Formulas
Range	Formula
D3	D3	=SUMPRODUCT(--(A3>B8:B14),(A3-B8:B14),(C8:C14-C7:C13))
E3	E3	=SUM((A3>B8:B14)*(A3-B8:B14)*(C8:C14-C7:C13))