# How to Calculate Income Tax in Excel Using IF Function (With Easy Steps)

### Step 1 – Set up Income Tax Slab

To illustrate how to calculate taxes, weâ€™ll use the following tax rate sample:

• A flat 7%Â for incomes 0 to \$10,000.
• \$750 + 12% for income fromÂ \$10,001 to \$15,000.
• \$1000 + 18% for income fromÂ \$15,001 to \$20,000.
• \$1,350 + 27% for income fromÂ \$20,001 to \$30,000.
• \$1,700 + 32% for income fromÂ \$30,001 and higher.

### Step 2 – Input Income Data

• Type the Names.
• Input the precise Salary amounts.

### Step 3: Use Excel IF Function

• Select cell D13.
• Type the following formula:
`=IF(C13<=\$C\$6,C13*\$D\$6,IF(C13<=\$C\$7,(C13-\$C\$6)*\$D\$7+750,IF(C13<=\$C\$8,(C13-\$C\$7)*\$D\$8+1000,IF(C13<=\$C\$9,(C13-\$C\$8)*\$D\$9+1350,(C13-\$C\$9)*\$D\$10+1700))))`
• Press Enter. The function willÂ return the accurate Tax value.

### Final Output

• Use the AutoFill tool to return the other income taxes.

How Does the Formula Work?

The IF function tests a logical operation. If itâ€™s True, the formula returns a value. Otherwise, returns another value.

• Here, if C13 is equal to or less than C6, itâ€™ll give C13*\$D\$6 output.
• If C13 is greater than C6 but equal to or less than C7, it returns (C13-\$C\$6)*\$D\$7+750 output.
• However, when C13 is greater than C7 but equal to or less than C8, it returns the output of (C13-\$C\$7)*\$D\$8+1000.
• Again, when C13 is greater than C8 but equal to or less than C9, it returns (C13-\$C\$8)*\$D\$9+1350 outcome.
• Lastly, If C13 is greater than C9, it returns (C13-\$C\$9)*\$D\$10+1700) output.

## Some Other Suitable Ways to Calculate Income Tax in Excel

Moreover, we have other methods to calculate income tax in Excel besides the IF function. Here, weâ€™ll show you 2 other functions that you can use for determining the income tax. Here we are going to use the same dataset above.

### Method 1 – Apply Excel VLOOKUP Function to Calculate Income Tax

In this method, weâ€™ll apply the VLOOKUP function. This function looks for a value in a range and returns a value from the specified column. The tax rate here is not like the earlier sample. So, follow the steps below to perform the task.

Steps:

• Select cell D13.
• Type the formula:
`=VLOOKUP(C13,\$B\$6:\$D\$10,3,TRUE)+(C13-VLOOKUP(C13,\$B\$6:\$D\$10,1,TRUE))*VLOOKUP(C13,\$B\$6:\$D\$10,3,TRUE)`
• Press Enter to return the result.

• Use AutoFill to complete the rest.

How Does the Formula Work?

• VLOOKUP(C13,\$B\$6:\$D\$10,3,TRUE)

Firstly, this part of the formula looks for C13 in the range \$B\$6:\$D\$10. Then, returns the rate from column 3.

• (C13-VLOOKUP(C13,\$B\$6:\$D\$10,1,TRUE))

The VLOOKUP function looks for C13 in the range \$B\$6:\$D\$10. Returns the value from the 1st column. Next, deducts the output from C13.

• VLOOKUP(C13,\$B\$6:\$D\$10,3,TRUE)+(C13-VLOOKUP(C13,\$B\$6:\$D\$10,1,TRUE))*VLOOKUP(C13,\$B\$6:\$D\$10,3,TRUE)

At last, it multiplies the outputs of VLOOKUP(C13,\$B\$6:\$D\$10,3,TRUE) and (C13-VLOOKUP(C13,\$B\$6:\$D\$10,1,TRUE)). After that, add VLOOKUP(C13,\$B\$6:\$D\$10,3,TRUE) output to it.

### Method 2 – Income Tax Calculation with SUMPRODUCT Function in Excel

Steps:

• Select cell D14.
• Type the formula:
`=SUMPRODUCT(\$D\$7:\$D\$11-\$D\$6:\$D\$10,C14-\$B\$7:\$B\$11,N(C14>\$B\$7:\$B\$11))`
• Press Enter.

• Use AutoFill to fill the rest of the series.

How Does the Formula Work?

• N(C14>\$B\$7:\$B\$11)

Firstly, this formula returns 0. The N function changes texts to number values, dates to serial numbers, and True to 1. Other than that, it returns 0.

• \$D\$7:\$D\$11-\$D\$6:\$D\$10

In this part, the formula finds out the differential rates.

• C14-\$B\$7:\$B\$11

Weâ€™ll get the amount to each differential rate here.

• SUMPRODUCT(\$D\$7:\$D\$11-\$D\$6:\$D\$10,C14-\$B\$7:\$B\$11,N(C14>\$B\$7:\$B\$11))

Eventually, the formula will add all the product outputs.

## Related Articles

<< Go Back to Excel Formulas for Finance | Excel for Finance | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Aung Shine

Aung Shine completed his bachelorâ€™s in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. It has been almost 2 years since he joined SOFTEKO and actively working on the ExcelDemy project. Currently he works as a Team Leader where he guides his team members to create technical content. He has published 150+ articles and reviewed 50+ articles. He has also solved various user problems before. He has interests in Data Analysis, Power Query, Advanced Excel,... Read Full Bio

1. thank you very much.

• Hello, Mohan!

Thanks for your appreciation. Stay in touch with ExcelDemy for more helpful content.

Regards
ExcelDemy

Advanced Excel Exercises with Solutions PDF