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 )

 

[coulombevin]

Hi Holger,

In your formula, the first LAMBDA is defined as a new method (aka function) which need an income, a start, an end and a rate. This new method is called by using the name 'taxCalculation'. This LAMBDA will then get its arguments values when it will be called.

Once this new method is defined, you are using another LAMBDA with only start, end and rate arguments. Those arguments are then going to be passed to the method 'taxCalculation' previously defined with the 'income' variable which has been defined in first row of the LET. This LAMBDA associated to totalTax variable will get the arguments values from the MAP function which loop the bracketStart, bracketEnd and taxRate arrays. totalTax could be read like so:

1. Do the SUM of :
   1. For each item in backetStart, bracketEnd and taxRate, pass the ith item to LAMBDA as start, end and rate argument
   2. LAMBDA, once you recieve the ith element, call the taxCalculation method using income variable and your 3 arguments
   3. next ith element

I hope I was clear enought and want to apologize in advance for typing errors since I'm french.

Bests regards,

Vincent

 

[Holger]

Hi Holger,

In your formula, the first LAMBDA is defined as a new method (aka function) which need an income, a start, an end and a rate. This new method is called by using the name 'taxCalculation'. This LAMBDA will then get its arguments values when it will be called.

Once this new method is defined, you are using another LAMBDA with only start, end and rate arguments. Those arguments are then going to be passed to the method 'taxCalculation' previously defined with the 'income' variable which has been defined in first row of the LET. This LAMBDA associated to totalTax variable will get the arguments values from the MAP function which loop the bracketStart, bracketEnd and taxRate arrays. totalTax could be read like so:

1. Do the SUM of :
   1. For each item in backetStart, bracketEnd and taxRate, pass the ith item to LAMBDA as start, end and rate argument
   2. LAMBDA, once you recieve the ith element, call the taxCalculation method using income variable and your 3 arguments
   3. next ith element

I hope I was clear enought and want to apologize in advance for typing errors since I'm french.

Bests regards,

Vincent

Merci beaucoup Vincent . What I was missing is, that the first Lambda call is merely the definition and it is rather the 2nd Lambda with the MAP that provides the input parameters...... Thanks again.

 

[coulombevin]

Nice french!

You're welcome!

Vincent

 

[Peter_SSs]

While it works,

What am I missing about how the tax should be calculated?

25 01 29.xlsm
	A	B	C	D	E	F	G
1	Low	High	Rate	Income in bracket	Tax in bracket	income	tax
2	0	10,000	10%	10,000	1,000	82,500	15,030
3	1,001	40,000	12%	30,000	3,600
4	40,001	85,000	22%	42,500	9,350
5	85,001	160,000	24%	0	0
6	160,001		32%	0	0
7				82,500	13,950
Tax

Cell Formulas
Range	Formula
G2	G2	=LET( income, F2, 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 )
E2:E6	E2	=D2*C2
D7:E7	D7	=SUM(D2:D6)

 

[Holger]

Thanks Peter for the follow up. I marked this already as solved but it does not seem to stick to the post. Secondly, my table above had an error, where cell A3 should read 10,001 instead of 1,001. Tax will then be correctly calucated. Vincent feedback helped me with better understanding the entire formula. Thanks again.

 

[Peter_SSs]

I marked this already as solved but it does not seem to stick to the post.

Post #2 is marked as the solution so that is fine. I just couldn't see that the formula gave the correct solution but I was just blind to that obvious typo. I just looked at the 'High' column to see what was in the brackets.

 

[Peter_SSs]

Despite my poor reading earlier, I still question the accuracy of your formula. In a number of cases I think it is slightly ($1) out. Try your formula and a manual calculation with say $163,000 or $82,499

Also wondering if you would contemplate a considerably shorter approach. 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 - or this could be built into the formula if required.

25 01 29.xlsm
	A	B	C	D	E	F
1	Low	High	Rate		income	tax
2	1	10,000	10%		82,500	13,950
3	10,001	40,000	12%			13,950
4	40,001	85,000	22%
5	85,001	160,000	24%
6	160,001	100,000,000	32%
Tax

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

 

[Holger]

Thanks Peter and I ended up with this formula after more trial and error:

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

 

[Peter_SSs]

When you post a formula by itself in the forum, please use the formula tags:

Whilst that formula mostly produces approximately correct results, they are still not correct. I don't think that you did the manual calculations that I suggested previously.

If you adjust or post again, can you please do so with XL2BB so I can be sure I am looking at the same thing you are?