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 )

 

[Dave Patton]

A bit more concise version that you can try.

Income Tax 2025a.xlsm
	A	B	C	D
1
2
3		Income	Tax	Tax
4		10,000.00	1,000.00	1,000.00
5		82,499.00	13,949.78	13,949.78
6		200,000.00	46,900.00	46,900.00
7		1,000,000.00	334,900.00	334,900.00
8
1b

Cell Formulas
Range	Formula
C4:C7	C4	=Tax25a(B4)
D4:D7	D4	=LET(i,B4,b,{0;10000;40000;85000;160000},r,{0.1;0.02;0.1;0.02;0.12},SUM((i>b)*(i-b)*r))

Lambda Functions
Name	Formula
Tax25a	=LAMBDA(Income,LET(i,Income,b,{0;10000;40000;85000;160000},r,{0.1;0.02;0.1;0.02;0.12},SUM((i>b)*(i-b)*r)))

Income Tax 2025a.xlsm
	A	B	C	D	E	F	G
1
2	Income	$82,499.00		$13,949.78		From	Tax rate
3						0	10%
4						10,000	12%
5						40,000	22%
6						85,000	24%
7						160,000	36%
8
9
10
11		Income
12		5,000.00				500.00	500.00
13		10,000.00				1,000.00	1,000.00
14		10,000.50				1,000.06	1,000.06
15		82,499.00				13,949.78	13,949.78
16		85,000.00				14,500.00	14,500.00
17		200,000.00				46,900.00	46,900.00
18		1,000,000.00				334,900.00	334,900.00
19
1a

Cell Formulas
Range	Formula
D2	D2	=Tax25a(B2)
F12:F18	F12	=Tax25a(B12)
G12:G18	G12	=LET(i,B12,b,{0;10000;40000;85000;160000},r,{0.1;0.02;0.1;0.02;0.12},SUM((i>b)*(i-b)*r))

Lambda Functions
Name	Formula
Tax25a	=LAMBDA(Income,LET(i,Income,b,{0;10000;40000;85000;160000},r,{0.1;0.02;0.1;0.02;0.12},SUM((i>b)*(i-b)*r)))

 

[Dave Patton]

Income Tax 2025a.xlsm
	A	B	C	D	E	F	G
1
2	Income	$82,499.00	$13,949.78	$13,949.78		From	Tax rate
3						0	10%
4						10,000	12%
5						40,000	22%
6						85,000	24%
7						160,000	36%
8
9
10
11		Income	Tax
12		5,000.00	500.00	500.00	500.00	500.00	500.00
13		10,000.00	1,000.00	1,000.00	1,000.00	1,000.00	1,000.00
14		10,000.50	1,000.06	1,000.06	1,000.06	1,000.06	1,000.06
15		82,499.00	13,949.78	13,949.78	13,949.78	13,949.78	13,949.78
16		85,000.00	14,500.00	14,500.00	14,500.00	14,500.00	14,500.00
17		200,000.00	46,900.00	46,900.00	46,900.00	46,900.00	46,900.00
18		1,000,000.00	334,900.00	334,900.00	334,900.00	334,900.00	334,900.00
1a

Cell Formulas
Range	Formula
C2,C12:C18	C2	=Tax2025a($F$3:$G$7,B2)
D2	D2	=Tax25a(B2)
D12:D18	D12	=LET(rng,$F$3:$G$7,i,B12,b,INDEX(rng,,1),r,INDEX(rng,,2),ro,VSTACK(0,DROP(r,-1)),SUM((i>b)*(i-b)*(r-ro)))
E12:E18	E12	=SUM((B12>{0;10000;40000;85000;160000})*(B12-{0;10000;40000;85000;160000})*({0.1;0.02;0.1;0.02;0.12}))
F12:F18	F12	=Tax25a(B12)
G12:G18	G12	=LET(i,B12,b,{0;10000;40000;85000;160000},r,{0.1;0.02;0.1;0.02;0.12},SUM((i>b)*(i-b)*r))

Lambda Functions
Name	Formula
Tax2025a	=LAMBDA(rngRateData,TaxableIncome,LET(rng,rngRateData,i,TaxableIncome,b,INDEX(rng,,1),r,INDEX(rng,,2),ro,VSTACK(0,DROP(r,-1)),SUM((i>b)*(i-b)*(r-ro))))
Tax25a	=LAMBDA(Income,LET(i,Income,b,{0;10000;40000;85000;160000},r,{0.1;0.02;0.1;0.02;0.12},SUM((i>b)*(i-b)*r)))

 

[Holger]

Awesome Dave and great addition. So much to wrap my head around.

Cheers