INCOME TAX CALCULATION WITH IF_ELSE FORMULA

baidya91

Board Regular
Joined
Jun 1, 2016
Messages
147
Suppose someone has a taxable income of Rs. 1177810/-. The income tax slab is as follows:
Sl No.Taxable income Range (Rs)Income Tax
10 - 2500000
2250000 - 5000000 + 5% of Taxable Income
3500000 - 100000012500 + 20% of Taxable Income
4>1000000112500 + 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.........
 
Last edited:

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
C1 is the method @arthurbr has suggested (and the way I normally do it), C2 is a different method

Book6
ABC
11177810165843
2165843
Sheet2
Cell Formulas
RangeFormula
C1C1=SUMPRODUCT(--(A1>{250000;500000;1000000}),A1-{250000;500000;1000000},{0.05;0.15;0.1})
C2C2=A1*30%-MIN(A1,1000000)*(30%-20%)-MIN(A1,500000)*(20%-5%)-MIN(A1,250000)*(5%-0%)
 
Upvote 0
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
10 - 2500000
2250000 - 5000000 + 5% of Taxable Income
3500000 - 75000012500 + 10% of Taxable Income
4750000 - 100000037500 + 15% of Taxable Income
51000000 - 125000075000 + 20% of Taxable Income
61250000 -1500000125000 + 25% of Taxable Income
7>1500000187500 + 30 of Taxable Income

In this case the result will be Rs. 157553/- but it does not come, Sir
 
Upvote 0
What changes did you make to the formula to cater for your changes to the bands/slabs?
 
Upvote 0
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​
C1FORMULA=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})
C2FORMULA=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....
 
Upvote 0
The 110562 is correct.

Book1
ABC
11177810110562
2110562
Sheet1
Cell Formulas
RangeFormula
C1C1=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})
C2C2=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
612500
725000
837500
935562
10110562
Sheet1
Cell Formulas
RangeFormula
B6B6=250000*0.05
B7B7=250000*0.1
B8B8=250000*0.15
B9B9=177810*0.2
B10B10=SUM(B5:B9)
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,818
Messages
6,181,152
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top