explanation please

[Holger]

Hi everyone,

I have found the below LET formula to calculate income tax in cell F2. While it works, I struggle to comprehend the Lambda part. Lambda uses income, start, end, rate as input. income refers to cell E2. What I don't understand is, how can the function calculate correct tax, if start, end, rate are nowhere defined.

What am I missing?

Thanks for anyone's help.

Book7
	A	B	C	D	E	F
1	Low	High	Rate		income	tax
2	0	10,000	10%		82,500	15,030
3	1,001	40,000	12%
4	40,001	85,000	22%
5	85,001	160,000	24%
6	160,001		32%
Sheet1

Cell Formulas
Range	Formula
F2	F2	=LET( income, E2, brackets, A2:C6, bracketStart, INDEX(brackets, 0, 1), bracketEnd, INDEX(brackets, 0, 2), taxRate, INDEX(brackets, 0, 3), numBrackets, ROWS(brackets), taxCalculation, LAMBDA(income,start,end,rate, IF(income > start, MIN(income - start, end - start) * rate, 0) ), totalTax, SUM( MAP(bracketStart, bracketEnd, taxRate, LAMBDA(start,end,rate, taxCalculation(income, start, end, rate)) ) ), totalTax )

 

[Holger]

@Peter_SSs firstly, sorry about not using the formula tag.....still getting the hang of everything on this forum.

Below is the latest version and it looks there are still some queries:

* I have inserted manual calls for the 2 values you suggested (L2 and P2 blocks)
* I have aligned the brackets
* I added a new formula version in H3
* F2 and F3 still show minor differences
* to match to manual calls simply change E2 to either value

Book9
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Low	High	Rate		income	tax
2	0	10,000	10%		82,499	13,949.78	Holger	Income	Rate	Tax		Manual	82,499			Manual	163,000
3	10,000	40,000	12%			13,950.00	Peter	10,000	10%	1,000		10,000	10%	1,000		10,000	10%	1,000
4	40,000	85,000	22%					30,000	12%	3,600		30,000	12%	3,600		30,000	12%	3,600
5	85,000	160,000	24%					42,499	22%	9,350		42,499	22%	9,350		45,000	22%	9,900
6	160,000	100,000,000	32%					0	24%	0				0		75,000	24%	18,000
7								0	32%	0						3,000	32%	960
8								82,499		13,949.78		82,499		13,949.78		163,000		33,460.00
Sheet1

Cell Formulas
Range	Formula
H2:J8	H2	=LET( income,E2, brackets,A2:C6, bracketStart,INDEX(brackets,0,1), bracketEnd,INDEX(brackets,0,2), taxRate,INDEX(brackets,0,3), income_by_bracket,IF(income<=bracketStart,0, IF(income>bracketEnd,bracketEnd-bracketStart,income-bracketStart)), tax_by_bracket,income_by_bracket*taxRate, VSTACK( HSTACK("Income","Rate","Tax"), HSTACK(income_by_bracket,taxRate,tax_by_bracket), HSTACK(SUM(income_by_bracket),"",SUM(tax_by_bracket) )))
F2	F2	=LET( income, E2, brackets, A2:C6, bracketStart, INDEX(brackets, 0, 1), bracketEnd, INDEX(brackets, 0, 2), taxRate, INDEX(brackets, 0, 3), numBrackets, ROWS(brackets), taxCalculation, LAMBDA(income,start,end,rate, IF(income >start, MIN(income - start, end - start) * rate, 0) ), totalTax, SUM( MAP(bracketStart, bracketEnd, taxRate, LAMBDA(start,end,rate, taxCalculation(income, start, end, rate)) ) ), totalTax )
F3	F3	=LET(income,E2,low,A2:A6,high,B2:B6,rate,C2:C6,totalTax,SUM(FILTER((high-low+1)*rate,high<=income,0)) +(income-MAXIFS(high,high,"<="&income))*MINIFS(rate,high,">="&income),totalTax)
L5	L5	=M2-A4
N3:N6,R3:R7	N3	=+M3*L3
L8,R8,P8,N8	L8	=SUM(L3:L7)
P7	P7	=+Q2-A6
Dynamic array formulas.

 

[Peter_SSs]

Below is the latest version and it looks there are still some queries:

Yes, it is still not right. My formula is irrelevant because you have changed the initial table in multiple ways. My formula is designed for the original table layout with only the two changes I had mentioned:

For consistency and accuracy is does require the first bracket to start with a 1 (all the other brackets start with a "...1" number) and some large number for the high of the top bracket

If you test on a fresh sheet with that table and my formula I think you will find the results accurate.

I have not yet had a chance to study/test your new formula. Will do that in a day or so when I have some more time.

 

[Peter_SSs]

I have not yet had a chance to study/test your new formula.

The new formula seems to be working fine. My preference though would be to have the initial brackets table reflect the actual low and high figures, assuming that the Income would always be a whole number. If that is the case then, for example, 10,000 is not the actual low value for the 12% bracket.

A couple of small tweaks to your formula would allow for that though. Below,
- your table & formulas in the top section,
- my table, F12 formula and my tweak of your formula in H12.

Holger.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Low	High	Rate		income	tax
2	0	10,000	10%		82,499	13,949.78		Income	Rate	Tax
3	10,000	40,000	12%					10,000	10%	1,000.00
4	40,000	85,000	22%					30,000	12%	3,600.00
5	85,000	160,000	24%					42,499	22%	9,349.78
6	160,000	100,000,000	32%					0	24%	0.00
7								0	32%	0.00
8								82,499		13,949.78
9
10
11	Low	High	Rate		income	tax
12	1	10,000	10%		82,499	13,949.78		Income	Rate	Tax
13	10,001	40,000	12%					10,000	10%	1,000.00
14	40,001	85,000	22%					30,000	12%	3,600.00
15	85,001	160,000	24%					42,499	22%	9,349.78
16	160,001	100,000,000	32%					0	24%	0.00
17								0	32%	0.00
18								82,499		13,949.78
Tax (4)

Cell Formulas
Range	Formula
F2	F2	=LET( income, E2, brackets, A2:C6, bracketStart, INDEX(brackets, 0, 1), bracketEnd, INDEX(brackets, 0, 2), taxRate, INDEX(brackets, 0, 3), numBrackets, ROWS(brackets), taxCalculation, LAMBDA(income,start,end,rate, IF(income >start, MIN(income - start, end - start) * rate, 0) ), totalTax, SUM( MAP(bracketStart, bracketEnd, taxRate, LAMBDA(start,end,rate, taxCalculation(income, start, end, rate)) ) ), totalTax )
H2:J8	H2	=LET( income,E2, brackets,A2:C6, bracketStart,INDEX(brackets,0,1), bracketEnd,INDEX(brackets,0,2), taxRate,INDEX(brackets,0,3), income_by_bracket,IF(income<=bracketStart,0, IF(income>bracketEnd,bracketEnd-bracketStart,income-bracketStart)), tax_by_bracket,income_by_bracket*taxRate, VSTACK( HSTACK("Income","Rate","Tax"), HSTACK(income_by_bracket,taxRate,tax_by_bracket), HSTACK(SUM(income_by_bracket),"",SUM(tax_by_bracket) )))
E12	E12	=E2
F12	F12	=LET(income,E12,low,A12:A16,high,B12:B16,rate,C12:C16,totalTax,SUM(FILTER((high-low+1)*rate,high<=income,0)) +(income-MAXIFS(high,high,"<="&income))*MINIFS(rate,high,">="&income),totalTax)
H12:J18	H12	=LET( income,E12, brackets,A12:C16, bracketStart,INDEX(brackets,0,1), bracketEnd,INDEX(brackets,0,2), taxRate,INDEX(brackets,0,3), income_by_bracket,IF(income<bracketStart,0, IF(income>=bracketEnd,bracketEnd-bracketStart+1,income-bracketStart+1)), tax_by_bracket,income_by_bracket*taxRate, VSTACK( HSTACK("Income","Rate","Tax"), HSTACK(income_by_bracket,taxRate,tax_by_bracket), HSTACK(SUM(income_by_bracket),"",SUM(tax_by_bracket) )))
Dynamic array formulas.

 

[Peter_SSs]

Don't know if you might be interested but a simpler way to calculate the actual tax would be to add a column to the initial table and then use a much simpler formula to calculate the tax.

Holger.xlsm
	A	B	C	D	E	F
21	Low	High	Rate	Base	income	tax
22	1	10,000	10%	0	82,499	13,949.78
23	10,001	40,000	12%	1000
24	40,001	85,000	22%	4600
25	85,001	160,000	24%	14500
26	160,001	100,000,000	32%	32500
Tax (4)

Cell Formulas
Range	Formula
F22	F22	=LET(b,INDEX(A22:D26,MATCH(E22,A22:A26),0),TAKE(b,,-1)+(E22-TAKE(b,,1)+1)*INDEX(b,3))
D23:D26	D23	=SUMPRODUCT(B$22:B22-A$22:A22+1,C$22:C22)

 

[Holger]

Thanks Peter, much appreciated your thoughts and experience. Let and Lambda are still posing huge learning curves for me especially when wrapped with other functions. Each time I see one I have to break each component down into separate cells and see the result so I can understand what each step does and how the final output is achieved. Feedback like yours and others through diff versions feeds the learning process…. Slowly in my end

Thanks again

 

[Holger]

@Peter_SSs ...I'm still struggling to get your latest simplified formula to work. Please see below.

* Your simplified formula from post #14 is Option 5 in cell F21
* I have deconstructed it in cells N11:Q22
* My result is tax of approx. 23k
* I think the issue is, that N13 picks up the 3rd row in the array (14,500) instead of row 2 with 4,600

Any chance to have a look please.

Thanks again for any help.

LET Function - Tax 1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Low	High	Rate	Base Tax	income
2	0	10,000	10%	1,000	82,499
3	10,000	40,000	12%	4,600
4	40,000	85,000	22%	14,500
5	85,000	160,000	24%	32,500
6	160,000	100,000,000	32%
7
8
9
10
11					Result as sum only			Result as table						40,000	85,000	0.22	14,500
12
13					Option 1	13,949.78		Income	Rate	Tax				14,500	3
14								10,000	10%	1,000
15					Option 2	13,949.78		30,000	12%	3,600				42,500
16								42,499	22%	9,350
17					Option 3	13,949.78		0	24%	0				0.22
18								0	32%	0
19					Option 4	13,949.78		82,499		13,949.78				9,350
20
21					Option 5	23,849.78
22														23,850.00
Sheet1

Cell Formulas
Range	Formula
D2:D5	D2	=SUMPRODUCT(B$2:B2-A$2:A2,C$2:C2)
N11:Q11	N11	=INDEX(A2:D6,MATCH(E2,A2:A6),0)
F13	F13	=LET( income, E2, brackets, A2:C6, bracketStart, INDEX(brackets, 0, 1), bracketEnd, INDEX(brackets, 0, 2), taxRate, INDEX(brackets, 0, 3), numBrackets, ROWS(brackets), taxCalculation, LAMBDA(income,start,end,rate, IF(income >start, MIN(income - start, end - start) * rate, 0) ), totalTax, SUM( MAP(bracketStart, bracketEnd, taxRate, LAMBDA(start,end,rate, taxCalculation(income, start, end, rate)) ) ), totalTax )
H13:J19	H13	=LET( income,E2, brackets,A2:C6, bracketStart,INDEX(brackets,0,1), bracketEnd,INDEX(brackets,0,2), taxRate,INDEX(brackets,0,3), income_by_bracket,IF(income<=bracketStart,0, IF(income>bracketEnd,bracketEnd-bracketStart,income-bracketStart)), tax_by_bracket,income_by_bracket*taxRate, VSTACK( HSTACK("Income","Rate","Tax"), HSTACK(income_by_bracket,taxRate,tax_by_bracket), HSTACK(SUM(income_by_bracket),"",SUM(tax_by_bracket) )))
N13	N13	=TAKE(INDEX(A2:D6,MATCH(E2,A2:A6),0),,-1)
O13	O13	=MATCH(E2,A2:A6)
F15	F15	=LET( income,E2, low,A2:A6, high,B2:B6, rate,C2:C6, totalTax,SUM(FILTER((high-low)*rate,high<=income,0)) +(income-MAXIFS(high,high,"<="&income))*MINIFS(rate,high,">="&income), totalTax)
N15	N15	=(E2-TAKE(INDEX(A2:D6,MATCH(E2,A2:A6),0),,1)+1)
F17	F17	=LET( income, E2, brackets, A2:C6, bracketStart, INDEX(brackets, 0, 1), bracketEnd, INDEX(brackets, 0, 2), taxRate, INDEX(brackets, 0, 3), income_by_bracket,IF(income<=bracketStart,0, IF(income>bracketEnd,bracketEnd-bracketStart,income-bracketStart)), tax_by_bracket,income_by_bracket*taxRate, SUM(tax_by_bracket) )
N17	N17	=INDEX(INDEX(A2:D6,MATCH(E2,A2:A6),0),3)
F19	F19	=LET( income,E2, tax_rates, C2:C6, upper,B2:B6, lower,DROP(VSTACK(0,upper),-1), income_by_bracket,IF(income<=lower,0, IF(income>upper,upper-lower,income-lower)), tax_by_bracket,income_by_bracket*tax_rates, SUM(tax_by_bracket) )
N19	N19	=+N15*N17
F21	F21	=LET(b,INDEX(A2:D6,MATCH(E2,A2:A6),0),TAKE(b,,-1)+(E2-TAKE(b,,1))*INDEX(b,3))
N22	N22	=+N13+N19
Dynamic array formulas.

 

[Holger]

please ignore my last post. I resolved it already. Had the base tax amount in the wrong row.

 

[Peter_SSs]

Had the base tax amount in the wrong row.

That it.
Your choice of course, but I would still revert the table to have accurate figures in the "Low' column as, for example,10,000 does not belong in the second bracket in my view.

Your simplified formula from post #14 is Option 5 in cell F21

Not quite - apart from modifying it for the different range the table is in, you have also modified it (removed a +1) due to my point above about the different 'Low' values.

 

[Holger]

@Peter_SSs I knew that issue would come up again so I added it back in and it should now work with all options showing the same result. Below is the final one incl. the deconstruction for others to learn form if needed.

Thanks again

Let Function - Tax 1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Low	High	Rate	Base Tax	Income
2	1	10,000	10%	0	82,499
3	10,001	40,000	12%	1,000
4	40,001	85,000	22%	4,600
5	85,001	160,000	24%	14,500
6	160,001	100,000,000	32%	32,500
7
8
9
10
11					Result as sum only			Result as table				Deconstruct of cell F21
12
13					Option 1	13,949.78		Income	Rate	Tax		40,001	85,000	0.22	4,600
14								10,000	10%	1,000
15					Option 2	13,949.78		30,000	12%	3,600		4,600
16								42,499	22%	9,350
17					Option 3	13,949.78		0	24%	0		42,499
18								0	32%	0
19					Option 4	13,949.78		82,499		13,949.78		0.22
20
21					Option 5	13,949.78						9,349.78
22
23												13,949.78
Sheet1

Cell Formulas
Range	Formula
D3:D6	D3	=SUMPRODUCT(B$2:B2-A$2:A2+1,C$2:C2)
F13	F13	=LET( income, E2, brackets, A2:C6, bracketStart, INDEX(brackets, 0, 1), bracketEnd, INDEX(brackets, 0, 2), taxRate, INDEX(brackets, 0, 3), numBrackets, ROWS(brackets), taxCalculation, LAMBDA(income,start,end,rate, IF(income >start, MIN(income - start+1, end - start+1) * rate, 0) ), totalTax, SUM( MAP(bracketStart, bracketEnd, taxRate, LAMBDA(start,end,rate, taxCalculation(income, start, end, rate)) ) ), totalTax )
H13:J19	H13	=LET( income,E2, brackets,A2:C6, bracketStart,INDEX(brackets,0,1), bracketEnd,INDEX(brackets,0,2), taxRate,INDEX(brackets,0,3), income_by_bracket,IF(income<=bracketStart,0, IF(income>bracketEnd,bracketEnd-bracketStart+1,income-bracketStart+1)), tax_by_bracket,income_by_bracket*taxRate, VSTACK( HSTACK("Income","Rate","Tax"), HSTACK(income_by_bracket,taxRate,tax_by_bracket), HSTACK(SUM(income_by_bracket),"",SUM(tax_by_bracket) )))
L13:O13	L13	=INDEX(A2:D6,MATCH(E2,A2:A6),0)
F15	F15	=LET( income,E2, low,A2:A6, high,B2:B6, rate,C2:C6, totalTax,SUM(FILTER((high-low+1)*rate,high<=income,0)) +(income-MAXIFS(high,high,"<="&income))*MINIFS(rate,high,">="&income), totalTax)
L15	L15	=TAKE(INDEX(A2:D6,MATCH(E2,A2:A6),0),,-1)
F17	F17	=LET( income, E2, brackets, A2:C6, bracketStart, INDEX(brackets, 0, 1), bracketEnd, INDEX(brackets, 0, 2), taxRate, INDEX(brackets, 0, 3), income_by_bracket,IF(income<=bracketStart,0, IF(income>bracketEnd,bracketEnd-bracketStart+1,income-bracketStart+1)), tax_by_bracket,income_by_bracket*taxRate, SUM(tax_by_bracket) )
L17	L17	=(E2-TAKE(INDEX(A2:D6,MATCH(E2,A2:A6),0),,1)+1)
F19	F19	=LET( income,E2, tax_rates, C2:C6, upper,B2:B6, lower,DROP(VSTACK(0,upper),-1), income_by_bracket,IF(income<=lower,0, IF(income>upper,upper-lower,income-lower)), tax_by_bracket,income_by_bracket*tax_rates, SUM(tax_by_bracket) )
L19	L19	=INDEX(INDEX(A2:D6,MATCH(E2,A2:A6),0),3)
F21	F21	=LET(b,INDEX(A2:D6,MATCH(E2,A2:A6),0),TAKE(b,,-1)+(E2-TAKE(b,,1)+1)*INDEX(b,3))
L21	L21	=+L17*L19
L23	L23	=+L15+L21
Dynamic array formulas.

 

[Peter_SSs]

I added it back in and it should now work with all options showing the same result.