Tax slab calculation (range between) then multiply only that amount with fall in

[naharwals]

I HAVE A $3,00,000/- OF AMOUNT

AND

	₹ 3,00,000.00
	if Taxable amt fall in below range			then remaining Balance mean only that amount fall in this range is taxable of 5%	I want this amount
5%	₹ 2,50,001	₹ 5,00,000		₹ 49,999	₹ 2,499.95	Multiply by 5%
10%	₹ 5,00,001	₹ 7,50,000
15%	₹ 7,50,001	₹ 10,00,000
20%	₹ 10,00,001	₹ 12,50,000
25%	₹ 12,50,001	₹ 15,00,000
30%	₹ 15,00,001	---->
	Taxable amount	₹ 6,00,000.00
	if Taxable amt fall in below range			then remaining Balance	I want this amount
5%	₹ 2,50,001	₹ 5,00,000		₹ 2,49,999	₹ 12,500	Multiply by 5%
10%	₹ 5,00,001	₹ 7,50,000		₹ 99,999	₹ 10,000	Multiply by 10%
15%	₹ 7,50,001	₹ 10,00,000		so on
20%	₹ 10,00,001	₹ 12,50,000
25%	₹ 12,50,001	₹ 15,00,000
30%	₹ 15,00,001	---->

its something like this

Income Tax Slab	Income Tax Rate	Income Tax Slab	Income Tax Rate
Up to ₹ 2,50,000	Nil	Up to ₹ 2,50,000	Nil
₹ 2,50,001 - ₹ 5,00,000	5% above ₹ 2,50,000	₹ 2,50,001 - ₹ 5,00,000	5% above ₹ 2,50,000
₹ 5,00,001 - ₹ 10,00,000	₹ 12,500 + 20% above ₹ 5,00,000	₹ 5,00,001 - ₹ 7,50,000	₹ 12,500 + 10% above ₹ 5,00,000
Above ₹ 10,00,000	₹ 1,12,500 + 30% above ₹ 10,00,000	₹ 7,50,001 - ₹ 10,00,000	₹ 37,500 + 15% above ₹ 7,50,000
		₹ 10,00,001 - ₹ 12,50,000	₹ 75,000 + 20% above ₹ 10,00,000
		₹ 12,50,001 - ₹ 15,00,000	₹ 1,25,000 + 25% above ₹ 12,50,000
		Above ₹ 15,00,000	₹ 1,87,500 + 30% above ₹ 15,00,000

something like this

Income Tax Slab	Income Tax Rate
Up to ₹ 2,50,000	Nil
₹ 2,50,001 - ₹ 5,00,000	5% above ₹ 2,50,000
₹ 5,00,001 - ₹ 7,50,000	₹ 12,500 + 10% above ₹ 5,00,000
₹ 7,50,001 - ₹ 10,00,000	₹ 37,500 + 15% above ₹ 7,50,000
₹ 10,00,001 - ₹ 12,50,000	₹ 75,000 + 20% above ₹ 10,00,000
₹ 12,50,001 - ₹ 15,00,000	₹ 1,25,000 + 25% above ₹ 12,50,000
Above ₹ 15,00,000	₹ 1,87,500 + 30% above ₹ 15,00,000

please help me

 

[Dave Patton]

You did not name the Rate Differential information correctly.
I named the Rate Differential aR ={0.05;0.15;0.1}

You can look at the brackets or select the rate in D21 select $B$23:$B$25-$B$22:$B$24 and press F9

Formula Evaluate may help you to understand the formula.

Commission2022.xlsm
	A	B	C	D	E
21	3	CTC/Year	1100000	142500	142500
22
23		5.00%	250000
24		20.00%	500000
25		30.00%	1000000
6bbb

Cell Formulas
Range	Formula
D21	D21	=SUMPRODUCT(--($C$21>$C$23:$C$25),$C$21-$C$23:$C$25,$B$23:$B$25-$B$22:$B$24)
E21	E21	=SUMPRODUCT(--(C21>aB),C21-aB,aR)

 

[Dave Patton]

If you want to retain the bracket and rate range of data, you can use the following variation of the solution.

The named ranges are optional.

Commission2022.xlsm
	A	B	C	D
15	Gross	1,100,000.00	142,500.00	142,500.00
16		Bracket	Rate	Rate Differential
17		0	0.00	0.00
18		250,000	0.05	0.05
19		500,000	0.20	0.15
20		1,000,000	0.30	0.10
21
6bbb

Cell Formulas
Range	Formula
C15	C15	=SUMPRODUCT(--($B$15>$B$17:$B$20),$B$15-$B$17:$B$20,$D$17:$D$20)
D15	D15	=SUMPRODUCT(--($B$15>Brackets),$B$15-Brackets,RateD)
D17:D20	D17	=C17-N(C16)

Named Ranges
Name	Refers To	Cells
Brackets	='6bbb'!$B$17:$B$20	C15:D15
RateD	='6bbb'!$D$17:$D$20	C15:D15