Excel formula for Tax calculation in india

12

ii-

Leave encashment

13

iii-

VRS

14

iv-

Gratuity

15

v-

Professional Tax

16

vi-

Any Other Exem. U/s 10

17

vii-

Standard Deduction

50,000.00

18

Total

50,000.00

19

Net Total Income

7,40,074.00

20

Savings

21

i-

U/s 80C

1,79,855.00

1,50,000.00

22

ii-

U/s 80D

23

iii-

U/s 80DD

24

iv-

U/s 80E

25

v-

U/s 88E

26

vi-

U/s 80G

200.00

27

vii-

U/s 80U

28

viii-

Other (s) if any

29

Total

1,80,055.00

1,50,200.00

30

Net Taxable Income

5,60,019.00

5,89,874.00

31

32

Tax Calculation

33

Tax at OLD Rate

Tax at NEW Rate

34

Tax

24,504.00

Tax

70,515.00

35

Rebate U/s 807A

Rebate U/s 807 A

36

Net Tax

24,504.00

Net Tax

70,515.00

37

Cess

980.16

Cess

38

Total Tax

25,484.16

Total Tax

70,515.00

Tax Calculation

Cell Formulas
Range	Formula
D22:E28,E21,D11:E17,D6:E8	D6	=C6
C9:E9	C9	=SUM(C5:C8)
C18:E18	C18	=SUM(C11:C17)
C19:E19	C19	=SUM(C9-C18)
D21	D21	=IF($C$21<150000,$C$21,150000)
C29:E29	C29	=SUM(C21:C28)
C30:E30	C30	=SUM(C19-C29)
E34	E34	=ROUND(IF($C9<=250000,0,IF($C9<=500000,($C9-250000)5%,IF($C9<=1000000,($C9-500000)20%+12500,($C9-1000000)*30%+112500))),0)
E36,C36	E36	=SUM(E34-E35)
C34	C34	=ROUND(IF($C30<=250000,0,IF($C30<=500000,($C30-250000)5%,IF($C30<=1000000,($C30-500000)20%+12500,($C30-1000000)*30%+112500))),0)
C35	C35	=ROUND(IF($C$30<=350000,MIN(2500,$C$30),0),0)
C37	C37	=SUM(C36*0.04)
C38,E38	C38	=SUM(C36+C37)

RK Singh · Feb 5, 2021

Income Tax Calculator with Old & New Tax Slabxls.xls

A

B

C

D

3

Income Tax Slab

Rate %

Tax

Rate

4

Up to Rs. 2,50,000

0

Nil

0

5

Rs. 2,50,001 - Rs. 5,00,000

5%

2,50,000 * 5%

5%

6

5,00,001 - 7,50,000

10%

12,500+(5,00,000) * 10%

10%

7

7,50,001 - 10,00,000

15%

37,500+(7,50,000) * 15%

15%

8

10,00,001 - 12,50,000

20%

75,000+(10,00,000) * 20%

20%

9

12,50,001 - 15,00,000

25%

1,25,000+(12,50,000) * 25%

25%

10

15,00,001 and above

30%

1,87,500+(15,00,000) * 30%

30%

New Tax Slab 2020-21

RK Singh · Feb 5, 2021

Based on the above tax slab to calculate tax at new tax rates, please build an excel formula to help me.

Dave Patton · Feb 6, 2021

You can calculate the Tax in a variety of ways including arithmetic, sumproduct, and Vlookup.
You can name the ranges or reference the ranges in the sheet.
I named the array of values for the Tax Brackets and the array of rate differentials.
You could also include a column to calculate the rate differential.
Named ranges:
Bracket aB = ={0;250000;500000;1000000}
Rate Differentials aR ={0;0.05;0.15;0.1}

You can use the same logic with your new brackets and rates.

T201904b.xlsm

A

B

C

D

E

1

Amount

790,074.00

Brackets

Rate

2

Tax

3

0

0%

4

250,000

5%

5

500,000

20%

6

1,000,000

30%

7

Tax_

Cell Formulas
Range	Formula
B2	B2	=SUMPRODUCT(--(B1>D3:D6),B1-D3:D6,E3:E6-E2:E5)
B3	B3	=SUMPRODUCT(--(B1>aB),B1-aB,aR)
B4	B4	=(B1>D4)(B1-D4)E4+(B1>D5)(B1-D5)(E5-E4)+(B1>D6)(B1-D6)(E6-E5)

Named Ranges
Name	Refers To	Cells
rB	=Tax_!$D$3:$D$6	B2
rL	=Tax_!$D$3:$F$9	B2

Dave Patton · Feb 6, 2021

T201904b.xlsm

A

B

C

D

E

1

Amount

800,000.00

Brackets

Rate

2

Tax

45,000.00

3

45,000.00

0

0%

4

250,000

5%

5

500,000

10%

6

750,000

15%

7

1,000,000

20%

8

1,250,000

25%

9

1,500,000

30%

Tax_ 2

Cell Formulas
Range	Formula
B2	B2	=SUMPRODUCT(--(B1>D3:D9),B1-D3:D9,E3:E9-E2:E8)
B3	B3	=SUMPRODUCT(--(B1>aB),B1-aB,aR)

Named Ranges
Name	Refers To	Cells
'Tax_ 2'!rB	='Tax_ 2'!$D$3:$D$7	B2
'Tax_ 2'!rL	='Tax_ 2'!$D$3:$F$13	B2

It is often useful to review the logic and calculations by using Excel's Formula Evaluation.

N.B. The named ranges are based on the new brackets and rates. Ignore the named range reference cited in the post; XL2BB included names that are not relevant to the post.

RK Singh · Feb 7, 2021

Dave Patton said:
You can calculate the Tax in a variety of ways including arithmetic, sumproduct, and Vlookup.
You can name the ranges or reference the ranges in the sheet.
I named the array of values for the Tax Brackets and the array of rate differentials.
You could also include a column to calculate the rate differential.
Named ranges:
Bracket aB = ={0;250000;500000;1000000}
Rate Differentials aR ={0;0.05;0.15;0.1}

You can use the same logic with your new brackets and rates.

T201904b.xlsm
A B C D E
1 Amount 790,074.00 Brackets Rate
2 Tax 70,514.80
3 70,514.80 0 0%
4 70,514.80 250,000 5%
5 500,000 20%
6 1,000,000 30%
7
Tax_
Cell Formulas
Range Formula
B2 B2 =SUMPRODUCT(--(B1>D3:D6),B1-D3:D6,E3:E6-E2:E5)
B3 B3 =SUMPRODUCT(--(B1>aB),B1-aB,aR)
B4 B4 =(B1>D4)*(B1-D4)*E4+(B1>D5)*(B1-D5)*(E5-E4)+(B1>D6)*(B1-D6)*(E6-E5)
Named Ranges
Name Refers To Cells
rB =Tax_!$D$3:$D$6 B2
rL =Tax_!$D$3:$F$9 B2

Sir
Sorry, I am unable to understand the array formula
Bracket aB = ={0;250000;500000;1000000}
Rate Differentials aR ={0;0.05;0.15;0.1}
may rate slab is 0-250000,0%,250000-500000,5%,500000-750000,10%,750000-1000000,15%,1000000-1250000,20%,1250000-1500000,25%,1500000+,30%

Please share in details. If possible upload excel file for example.
Thanks
R K Singh

MARK858 · Feb 7, 2021

Have a look at the link below which explains the method (Dave Patton has already supplied examples that you can copy/paste to Excel)

McGimpsey & Associates : Excel : Using SUMPRODUCT() with variable rates

RK Singh · Feb 8, 2021

please solve another issue to find out how many invoices issued using different series numbers like as:

S1500001,S1500002,S1500003 = From S1500001 To S1500003 = 03
S2100005,S2100006,S2100007,S2100008 = From S2100005 To S21000007 = 04

please help to build excel forumla.

Thanks & Regards,

Dave Patton · Feb 8, 2021

Did you try the suggestions? You could use the formula =SUMPRODUCT(--(B1>D3:D9),B1-D3:D9,E3:E9-E2:E8).
If you choose, you can use Name Manager and name the ranges or name the arrays. I left it up to you in the second example to create and name the array.
It is often useful to review the logic and calculations by using Excel's Formula Evaluation.
We can calculate Tax with arithmetic, Sumproduct, Vlookup, etc.. We can name the ranges or reference the ranges in the sheet. I named the array of values for the Tax Brackets (aB_) and the array of rate differentials (aR_). We could alternatively calculate the rate differential in a distinct column. We can look at the data and type the array information or we can use a formula like B10 (highlight the range D11:D17, press F9, copy the array, and then name that array with Name Manager).

Since you didn't try the second example that I posted above. I edited this post and we post the information again.
I also completed the arithmetic versions.
You can try whichever alternative that you prefer. Since I am posted 2 distinct examples, the second set of named arrays are aB_ and aR_.
You can click on the icon below the F(x) and the paste the information into a clean sheet.
Cells D7 and D18 have a number; it is a very large number. It was named BigNum.

Named ranges:
Bracket aB_ = {{0;250000;500000;750000;1000000;1250000;1500000}
Rate Differentials aR_ ={0;0.05;0.05;0.05;0.05;0.05;0.05}

Tax 2021.xlsm

A

B

C

D

E

F

G

1

Income

790,074.00

Brackets

Rate

2

Tax

E2 is blank

3

or

0

0%

4

or

250,000

5%

12,500.00

5

or

500,000

20%

58,014.80

6

1,000,000

30%

0.00

7

1.E+308

8

9

Income

1,500,000.00

Brackets

Rate

10

Tax

E10 is blank

11

or

0

0%

12

or

250,000

5%

12,500.00

13

or

500,000

10%

25,000.00

14

750,000

15%

37,500.00

15

1,000,000

20%

50,000.00

16

1,250,000

25%

62,500.00

17

1,500,000

30%

0.00

18

1.E+308