INCOME TAX CALCULATION WITH IF_ELSE FORMULA

[baidya91]

Suppose someone has a taxable income of Rs. 1177810/-. The income tax slab is as follows:

Sl No.	Taxable income Range (Rs)	Income Tax
1	0 - 250000	0
2	250000 - 500000	0 + 5% of Taxable Income
3	500000 - 1000000	12500 + 20% of Taxable Income
4	>1000000	112500 + 30% of Taxable Income

How can I put the entire calculation in one single 'if _ else' formula in excel so that the result will be Rs. 165843/-?

Please help. Thanks in advance.........

 

[arthurbr]

This technique is explained at https://www.mcgimpsey.com/excel/variablerate1.html

 

[MARK858]

C1 is the method @arthurbr has suggested (and the way I normally do it), C2 is a different method

Book6
	A	B	C
1	1177810		165843
2			165843
Sheet2

Cell Formulas
Range	Formula
C1	C1	=SUMPRODUCT(--(A1>{250000;500000;1000000}),A1-{250000;500000;1000000},{0.05;0.15;0.1})
C2	C2	=A1*30%-MIN(A1,1000000)*(30%-20%)-MIN(A1,500000)*(20%-5%)-MIN(A1,250000)*(5%-0%)

 

[baidya91]

If the taxable income remains the same, i. e, Rs. 1177810/-. but tax slab changes in the following way, income tax calculation does not come correct:

Sl No.	Taxable income Range (Rs)	Income Tax
1	0 - 250000	0
2	250000 - 500000	0 + 5% of Taxable Income
3	500000 - 750000	12500 + 10% of Taxable Income
4	750000 - 1000000	37500 + 15% of Taxable Income
5	1000000 - 1250000	75000 + 20% of Taxable Income
6	1250000 -1500000	125000 + 25% of Taxable Income
7	>1500000	187500 + 30 of Taxable Income

In this case the result will be Rs. 157553/- but it does not come, Sir

 

[MARK858]

What changes did you make to the formula to cater for your changes to the bands/slabs?

 

[baidya91]

What changes did you make to the formula to cater for your changes to the bands/slabs?

I have made these changes, Sir...

1177810​		110562​
		76671.5​
C1	FORMULA	=SUMPRODUCT(--(A1>{250000;500000;750000;1000000;1250000;1500000}),A1-{250000;500000;750000;1000000;1250000;1500000},{0.05;0.05;0.05;0.05;0.05;0.05})
C2	FORMULA	=A1*30%-MIN(A1,1500000)*(30%-25%)-MIN(A1,1250000)*(25%-20%)-MIN(A1,1000000)*(20%-15%)-MIN(A1,750000)*(15%-10%)-MIN(A1,500000)*(10%-5%)-MIN(A1-250000)*(5%-0%)

But did not get desired result....

 

[MARK858]

The 110562 is correct.

Book1
	A	B	C
1	1177810		110562
2			110562
Sheet1

Cell Formulas
Range	Formula
C1	C1	=SUMPRODUCT(--(A1>{0;250000;500000;750000;1000000;1250000;1500000}),A1-{0;250000;500000;750000;1000000;1250000;1500000},{0;0.05;0.05;0.05;0.05;0.05;0.05})
C2	C2	=A1*30%-MIN(A1,1500000)*(30%-25%)-MIN(A1,1250000)*(25%-20%)-MIN(A1,1000000)*(20%-15%)-MIN(A1,750000)*(15%-10%)-MIN(A1,500000)*(10%-5%)-MIN(A1,250000)*(5%-0%)

Book1
	B
6	12500
7	25000
8	37500
9	35562
10	110562
Sheet1

Cell Formulas
Range	Formula
B6	B6	=250000*0.05
B7	B7	=250000*0.1
B8	B8	=250000*0.15
B9	B9	=177810*0.2
B10	B10	=SUM(B5:B9)

 

[baidya91]

What changes did you make to the formula to cater for your changes to the bands/slabs?

THANXXXX

 

[MARK858]

You're welcome