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

Get FREE Advanced Excel Exercises with Solutions!

We can perform numerous mathematical tasks in MS Excel. Again, a lot of individuals like to store Income and Expenses details in Excel worksheets. Every once in a while, you may need to calculate the Income Tax. In some countries, company accountants take away the income tax from the salary. But, you have to find that out by yourself where the above system doesn’t prevail. We can easily calculate income tax in Excel by applying simple formulas and built-in functions. In this article, we’ll show you the step-by-step procedures to Calculate Income Tax in Excel Using the IF Function.


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

A fixed tax amount imposed on a person’s income by the central government is called Income Tax. It’s usually deducted from a person’s salary during the financial year. However, you may also need to determine and deposit your own tax. Therefore, go through the below steps carefully to use the IF function for calculating Income Tax in Excel.


STEP 1: Set up Income Tax Slab

We need to prepare the Income tax slab first. There are various Tax Rates that exist depending on the country and region. To illustrate, we’ll use the following tax rate sample.

  • For instance, a flat 7% of the total earning will be applicable for incomes 0 to $10,000.
  • Then, $750 + 12% of the incomes greater than $10,000 is set for $10,001 to $15,000.
  • After that, $1000 + 18% of the earnings larger than $15,000 is fixed for $15,001 to $20,000.
  • Subsequently, $1,350 + 27% of the incomes larger than $20,000 is applicable for $20,001 to $30,000.
  • Furthermore, $1,700 + 32% of the earnings higher than $30,000 is set for $30,001 to above.


STEP 2: Input Income Data

In this step, we’ll input the Name and their Salary.

  • Firstly, create the required Headers.
  • Next, type the Names.
  • Afterward, input the precise Salary amounts.

calculate income tax in excel using if function


STEP 3: Use Excel IF Function

  • Now, select cell D13.
  • Then, 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))))
  • Consequently, press Enter.
  • Thus, it’ll return the accurate Tax value.

calculate income tax in excel using if function


Final Output

  • At last, use the AutoFill tool to return the other income taxes.
  • As a result, you’ll get the desired tax amounts.

calculate income tax in excel using if function

🔎 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.

Read More: How to Calculate Income Tax on Salary with Old Regime in Excel


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.


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:

  • First of all, select cell D13.
  • There, 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.

Apply Excel VLOOKUP Function to Calculate Income Tax

  • Finally, use AutoFill to complete the rest.
  • Thus, you’ll get the income tax of other salary amounts.

Apply Excel VLOOKUP Function to Calculate Income Tax

🔎 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.

Read More: How to Calculate Social Security Tax in Excel


2. Income Tax Calculation with SUMPRODUCT Function in Excel

We can also insert the SUMPRODUCT function to carry out the operation. Hence, learn the process below for Income Tax Calculation.

STEPS:

  • Select cell D14 at last.
  • Then, type the formula:
=SUMPRODUCT($D$7:$D$11-$D$6:$D$10,C14-$B$7:$B$11,N(C14>$B$7:$B$11))
  • Subsequently, press Enter.

  • Use AutoFill to fill the rest of the series.
  • Thus, you’ll see the desired tax amounts.

🔎 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.

Read More: Formula for Calculating Withholding Tax in Excel


Download Practice Workbook

Download the following workbook to practice by yourself.


Conclusion

Henceforth, you will be able to Calculate Income Tax in Excel using the IF Function following the above-described procedures. Keep using them and let us know if you have more ways to do the task. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Aung Shine
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

2 Comments
  1. thank you very much.

    • Hello, Mohan!

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

      Regards
      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo