Excel Tax Calculator

0 votes

In Excel, I'm creating a tax calculator. I've used the if and command for this, but only the limited slabs' results are displayed. Both the tax table and my formula are posted. I need your help to make the formula shorter.

Tax Slabs               Tax Rate    Additional Tax
 -             400,000  0%   -   
   400,000     500,000  2.00%        -   
   500,000     750,000  5.00%        2,000 
   750,000   1,400,000  10.00%      14,500 
 1,400,000   1,500,000  12.50%      79,500 
 1,500,000   1,800,000  15.00%      92,000 
 1,800,000   2,500,000  17.50%     137,000 
 2,500,000   3,000,000  20.00%     259,500 
 3,000,000   3,500,000  22.50%     359,500 
 3,500,000   4,000,000  25.00%     472,000 
 4,000,000   7,000,000  27.50%     597,000 
 7,000,000              30.00%   1,422,000 

Tax slabs are in columns H and I. Tax Rates slabs are in column J and the Additional tax is in column K.

Here is the formula

=IF(AND($D8>$H$8,$D8<=$I$8),(($D8-$H$8)*$J$8+$K$8),IF(AND($D8>$H$9,$D8<=$I$9),(($D8-
$H$9)*$J$9+$K$9),IF(AND($D8>$H$10,$D8<=$I$10),(($D8-
$H$10)*$J$10+$K$10),IF(AND($D8>$H$11,$D8<=$I$11),(($D8-
$H$11)*$J$11+$K$11),IF(AND($D8>$H$12,$D8<=$I$12),(($D8-
$H$12)*$J$12+$K$12),IF(AND($D8>$H$13,$D8<=$I$13),(($D8-
$H$13)*$J$13+$K$13),IF(AND($D8>$H$14,$D8<=$I$14),(($D8-
$H$14)*$J$14+$K$14),IF(AND($D8>$H$15,$D8<=$I$15),(($D8-
$H$15)*$J$15+$K$15,IF(AND($D8>$H$16,$D8<=$I$16),(($D8-
$H$16)*$J$16+$K$16,IF(AND($D8>$H$17,$D8<=$I$17),(($D8-
$H$17)*$J$17+$K$17),IF(AND($D8>$H$18,$D8<=$I$18),(($D8-
$H$18)*$J$18+$K$18),IF(AND($D8>$H$19),(($D8*$J$19)+$K$19),0))))))))))))))
Oct 15 in Others by Kithuzzz
• 20,660 points
44 views

1 answer to this question.

0 votes

You can replace it with a set of vlookups:

=((D8-VLOOKUP(D8,H8:K19,1,1))*VLOOKUP(D8,H8:K19,3,1))+VLOOKUP(D8,H8:K19,4,1)
answered Oct 15 by narikkadan
• 37,660 points

Related Questions In Others

0 votes
0 answers

How to compare two excel sheets

How to compare two excel sheets , ...READ MORE

Jul 18, 2021 in Others by Sri
• 3,190 points
260 views
0 votes
1 answer

Change date format of cell in excel from dd.mm.yyyy to yyy/mm/dd ( excel version 2013 )

Hello :)   Excel’s Format Cells function can quickly ...READ MORE

answered Feb 9 in Others by gaurav
• 22,040 points
544 views
0 votes
1 answer

csv to excel conversion

So Basically u have to follow few ...READ MORE

answered Feb 9 in Others by gaurav
• 22,040 points
76 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9 in Others by gaurav
• 22,040 points
71 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,720 points
454 views
0 votes
1 answer

ImportError: openpyxl is required for loading excel format files

Forget the PsychoPy complications for the time ...READ MORE

answered Oct 3, 2018 in Python by Priyaj
• 58,100 points
405 views
0 votes
1 answer

In Blue Prism how to split excel column data into TWO columns

This is how I am doing it. Dim ...READ MORE

answered Oct 15, 2018 in RPA by Priyaj
• 58,100 points
3,384 views
0 votes
1 answer

Excel Power Query: Using List.MatchAny on a column value

try this. let TableA = ...READ MORE

answered Oct 22, 2018 in Power BI by Annie97
• 2,160 points
2,849 views
0 votes
1 answer

Reading/parsing Excel (xls) files with Python

I highly recommend xlrd for reading .xls files. But there are ...READ MORE

answered Sep 21 in Others by narikkadan
• 37,660 points
71 views
0 votes
1 answer

Excel VBA if file closed, then open and paste, else just paste data

Slightly re-worked to add full workbook/sheet qualifiers ...READ MORE

answered Sep 21 in Others by narikkadan
• 37,660 points
93 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP