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

Create the required Headers.
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.

Download Practice Workbook

Download the following workbook for practice.

Calculate Income Tax Using IF Function.xlsx

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

Get FREE Advanced Excel Exercises with Solutions!

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

Step 1 – Set up Income Tax Slab

Step 2 – Input Income Data

Step 3: Use Excel IF Function

Final Output

Some Other Suitable Ways to Calculate Income Tax in Excel

Method 1 – Apply Excel VLOOKUP Function to Calculate Income Tax

Method 2 – Income Tax Calculation with SUMPRODUCT Function in Excel

Related Articles

Leave a reply Cancel reply