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

 

[SanjayGMusafir]

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

Change the cell reference and also check the accuracy of formula as per your need

=ROUND(IFS(N1<=250000,0,N139<=500000,(N1-250000)*5%,N1<=750000,12500+(N1-500000)*10%,N1<=1000000,37500+((N139-750000)*15%),N1<=1250000,75000+((N139-1000000)*20%),N1<=1500000,125000+((N139-1250000)*15%),TRUE,187500+((N139-1500000)*30%)),0)

 

[Dave Patton]

The formula in C2 uses named arrays. The array of Bracket data and the array of Rate Differentials are named.
See Formulas Name Manger
- The array of Bracket data is named aB Refers to ={0;250000;500000;750000;1000000;1250000;1500000}
- The array of Rate Differentials is named aR Refers to ={0;0.05;0.05;0.05;0.05;0.05;0.05}
Advantages
The formula is easier to read.
The table is not required if the formula in C2 is used.
N.B.
The names are not necessary; it is a personal preference.
The names can be assigned to be applicable to the sheet or to the workbook.
We can determine the numbers by looking at the tier specifications or by reviewing the ranges.
For example with the formula in B2, select B8:B14-B7:B13 and press F9.

Try the version that you prefer.

Commission2022.xlsm
	A	B	C	D
1	Income	1,000,000.00
2	Tax	75,000.00	75,000.00
3
4
5
6	Brackets	Rates	--- Arithmetic ---
7	cell b7 is blank		By Bracket	Cumulative
8	0	0%	0.00	0.00
9	250,000	5%	12,500.00	12,500.00
10	500,000	10%	25,000.00	37,500.00
11	750,000	15%	37,500.00	75,000.00
12	1,000,000	20%	0.00	75,000.00
13	1,250,000	25%	0.00	75,000.00
14	1,500,000	30%	0.00	75,000.00
15	1.E+308
16
6b

Cell Formulas
Range	Formula
B2	B2	=SUMPRODUCT(--(B1>A8:A14),B1-A8:A14,B8:B14-B7:B13)
C2	C2	=SUMPRODUCT(--(B1>aB),B1-aB,aR)
C8:C14	C8	=MAX(0,MIN($B$1,A9)-A8)*B8
D8:D14	D8	=MAX(0,MIN($B$1,A9)-A8)*B8+N(D7)
A15	A15	=BigNum

 

[Alex Blakenburg]

I would typically use Vlookup with True for looking up tax tables.

Book4
	A	B	C	D
1	Income	510,000
2	Tax	13,500
3
4
5
6	Brackets	Rates	Bracket Base
7	0	0.00%	0
8	250,000	5.00%	0
9	500,000	10.00%	12,500
10	750,000	15.00%	37,500
11	1,000,000	20.00%	75,000
12	1,250,000	25.00%	125,000
13	1,500,000	30.00%	187,500
14
Sheet2

Cell Formulas
Range	Formula
B2	B2	=VLOOKUP(B1,A7:C13,3,TRUE)+VLOOKUP(B1,A7:C13,2,TRUE)*(B1-VLOOKUP(B1,A7:C13,1,TRUE))
C7:C13	C7	=(A7-N(A6))*N(B6)+N(C6)

 

[naharwals]

[

Thank You so much @Dave Patton

Dave Patton​

TDS on Salary.xlsx
	A	B	C	D	E	F
1		Example 02 Testing
2	CTC	₹ 10,00,000	₹ 75,000	Tax Payable Accord to Slab
3
4	5%	₹ 2,50,001	₹ 5,00,000	₹ 12,500.00
5	10%	₹ 5,00,001	₹ 7,50,000	₹ 25,000.00
6	15%	₹ 7,50,001	₹ 10,00,000	₹ 37,500.00
7	20%	₹ 10,00,001	₹ 12,50,000	₹ 0.00
8	25%	₹ 12,50,001	₹ 15,00,000	₹ 0.00
9	30%	₹ 15,00,001	0	₹ 0.00
10				₹ 75,000	Total Tax Liablity
11				₹ 6,250.00	TDS To be deduct/M
12
13
14	CTC	₹ 20,00,000	₹ 3,37,500
15				Tax Payable Accord to Slab
16	5%	₹ 2,50,001	₹ 5,00,000	₹ 12,500.00
17	10%	₹ 5,00,001	₹ 7,50,000	₹ 25,000.00
18	15%	₹ 7,50,001	₹ 10,00,000	₹ 37,500.00
19	20%	₹ 10,00,001	₹ 12,50,000	₹ 50,000.00
20	25%	₹ 12,50,001	₹ 15,00,000	₹ 62,500.00
21	30%	₹ 15,00,001	₹ 20,00,000	₹ 1,50,000.00
22				₹ 3,37,500	Tax Payable Amount
23				₹ 28,125.00	TDS To be deduct/M
24
25
Sheet2

Cell Formulas
Range	Formula
C2	C2	=ROUNDUP(SUMPRODUCT(--($B$2>$B$4:$B$9),$B$2-$B$4:$B$9,$A$4:$A$9-$A$3:$A$8),0)
C9	C9	=IF($B$2>=B9,$B$2,"0")
D4:D9	D4	=ROUNDUP(MAX(0,MIN($B$2,C4)-B4)*A4,0)
D10,D22	D10	=SUM(D4:D9)
D11,D23	D11	=D10/12
C14	C14	=ROUNDUP(SUMPRODUCT(--($B$14>B16:B21),$B$14-B16:B21,A16:A21-A15:A20),0)
C21	C21	=IF($B$14>=B21,$B$14,"0")
D16:D21	D16	=ROUNDUP(MAX(0,MIN($B$14,C16)-B16)*A16,0)

In my Excel 2007 no have (=BigNum) Function

 

[Dave Patton]

BigNum stands for 9.99999999999999E+307

With Name Manager
New name BigNum value =9.99999999999999E+307

N.B. I Edited your Bracket information.

If you use the named information and the formula in C2, you do not need the table or the other formulas.

 

[naharwals]

BigNum stands for 9.99999999999999E+307

With Name Manager
New name BigNum value =9.99999999999999E+307

N.B. I Edited your Bracket information.

If you use the named information and the formula in C2, you do not need the table or the other formulas.

It's Working Great Thank you So much @Dave Patton

But I will keep both styles, 02 for slab view, (Max, Mini function really saving my day)
Thank Again

TDS on Salary.xlsx
	A	B	C	D	E	F
1		Final 02
2			Employee Code:	10
3			Employee Name:	Manish Kumar
4	01	CTC/Year	₹ 12,70,000	₹ 1,30,000.00	Tax Payable Accord to Slab
5				₹ 10,833.33	TDS To be deduct/Month
6
7
8
9	02	CTC/Year	₹ 12,70,000	₹ 1,30,000	₹ 1,30,000	Tax Payable Accord to Slab
11		5%	₹ 2,50,001	₹ 5,00,000	₹ 12,500.00	Slab
12		10%	₹ 5,00,001	₹ 7,50,000	₹ 25,000.00
13		15%	₹ 7,50,001	₹ 10,00,000	₹ 37,500.00
14		20%	₹ 10,00,001	₹ 12,50,000	₹ 50,000.00
15		25%	₹ 12,50,001	₹ 15,00,000	₹ 5,000.00
16		30%	₹ 15,00,001	0	₹ 0.00
17					₹ 1,30,000.00	Tax Payable Amount
18					₹ 10,833.33	TDS To be deduct/Month
Tax Calculate

Cell Formulas
Range	Formula
D3	D3	=VLOOKUP(D2,'Staff Salary'!B2:Z90,2,0)
D4	D4	=SUMPRODUCT(--(C4>aB),C4-aB,aR)
D5,E18	D5	=D4/12
D9	D9	=ROUNDUP(SUMPRODUCT(--($C$9>C11:C16),$C$9-C11:C16,B11:B16-B10:B15),0)
E9	E9	=SUMPRODUCT(--(C9>aB),C9-aB,aR)
D16	D16	=IF($C$9>=C16,$C$9,"0")
E11:E16	E11	=ROUNDUP(MAX(0,MIN($C$9,D11)-C11)*B11,0)
E17	E17	=SUM(E11:E16)

 

[naharwals]

@Dave Patton if Rate Difference (Differentials) 5%, 20%, 30% then how the name manager will work (IN NUMBER 03)
number 02 is perfect

TDS on Salary.xlsx
	A	B	C	D	E	F
9	02	CTC/Year	₹ 11,00,000	₹ 95,000	₹ 95,000	Tax Payable Accord to Slab
11		5%	₹ 2,50,001	₹ 5,00,000	₹ 12,500.00	Slab
12		10%	₹ 5,00,001	₹ 7,50,000	₹ 25,000.00
13		15%	₹ 7,50,001	₹ 10,00,000	₹ 37,500.00
14		20%	₹ 10,00,001	₹ 12,50,000	₹ 20,000.00
15		25%	₹ 12,50,001	₹ 15,00,000	₹ 0.00
16		30%	₹ 15,00,001	0	₹ 0.00
17				Grand Total	₹ 95,000.00	Tax Payable Amount
18					₹ 7,916.67	TDS To be deduct/Month
19
20	03	CTC/Year	₹ 11,00,000	₹ 1,42,500	₹ 57,500	Tax Payable Accord to Slab
21
22		5%	₹ 2,50,001	₹ 5,00,000	₹ 12,500.00	Slab
23		20%	₹ 5,00,001	₹ 10,00,000	₹ 1,00,000.00
24		30%	₹ 10,00,001	₹ 11,00,000	₹ 30,000.00
25				Grand Total	₹ 1,42,500.00	Tax Payable Amount
26					₹ 11,875.00	TDS To be deduct/Month
Tax Calculate

Cell Formulas
Range	Formula
C9	C9	=C20
D9	D9	=ROUNDUP(SUMPRODUCT(--($C$9>C11:C16),$C$9-C11:C16,B11:B16-B10:B15),0)
E9	E9	=SUMPRODUCT(--(C9>NewSlab),C9-NewSlab,NewRate)
D16	D16	=IF($C$9>=C16,$C$9,"0")
E22:E24,E11:E16	E11	=ROUNDUP(MAX(0,MIN($C$9,D11)-C11)*B11,0)
E17	E17	=SUM(E11:E16)
E18,E26	E18	=E17/12
D20	D20	=ROUNDUP(SUMPRODUCT(--($C$20>C22:C24),$C$20-C22:C24,B22:B24-B21:B23),0)
E20	E20	=SUMPRODUCT(--(C20>OldSlab),C20-OldSlab,OldRate)
D24	D24	=IF($C$20>=C24,$C$20,"0")
E25	E25	=SUM(E22:E24)

 

[Dave Patton]

Did you name the slabs correctly?
What is the correct result if you complete the calculation manually?
What is the first slab ? Is it 0-250000? at what rate?
Did you try Formulas Evaluate Formula ?
You ignored my comment about the brackets.
You did not show what is in your named brackets and rate differentials.

 

[naharwals]

Did you name the slabs correctly?
What is the correct result if you complete the calculation manually?
What is the first slab ? Is it 0-250000? at what rate?
Did you try Formulas Evaluate Formula ?
You ignored my comment about the brackets.
You did not show what is in your named brackets and rate differentials.

03 is an old slab, and the rate Below:

5%​	₹ 2,50,001	₹ 5,00,000
20%​	₹ 5,00,001	₹ 10,00,000
30%​	₹ 10,00,001	₹ 11,00,000

You did not show what is in your named brackets and rate differentials
Did you name the slabs correctly?
Name
OldSlab= {0;250000;500000;1000000}
OldRate= {0;0.05;0.02;0.03}

What is the correct result if you complete the calculation manually?
Yes it's correct = ₹142500

What is the first slab?
It is
0-250000? at 0%
2500001-500000 at 5%
500001-1000000 at 20%
1000000- BigNum at 30%

Did you try Formulas Evaluate Formula?
No

You ignored my comment about the brackets.
No, I did not ignore it, it works great.

Only Name manager, I am not able to do. now the only problem in the Name manager. is think rate differential is not working.

TDS on Salary.xlsx
	A	B	C	D	E	F
19
20				This is correct	this is not correct
21	03	CTC/Year	₹ 11,00,000	₹ 1,42,500	₹ 57,500	Tax Payable Accord to Slab
22			slab		Calculation
23		5%	₹ 2,50,001	₹ 5,00,000	₹ 12,500.00	Slab
24		20%	₹ 5,00,001	₹ 10,00,000	₹ 1,00,000.00
25		30%	₹ 10,00,001	1E+308	₹ 30,000.00
26		Rate ^		Grand Total	₹ 1,42,500.00	Tax Payable Amount
27					₹ 11,875.00	TDS To be deduct/Month
28
29
Tax Calculate

Cell Formulas
Range	Formula
D21	D21	=ROUNDUP(SUMPRODUCT(--($C$21>$C$23:$C$25),$C$21-$C$23:$C$25,$B$23:$B$25-$B$22:$B$24),0)
E21	E21	=SUMPRODUCT(--(C21>OldSlab),C21-OldSlab,OldRate)
D25	D25	=BigNum
E23:E25	E23	=ROUNDUP(MAX(0,MIN($C$9,D23)-C23)*B23,0)
E26	E26	=SUM(E23:E25)
E27	E27	=E26/12