Home » Excel for Finance » How to Calculate Income Tax in Excel Using IF Function (With Easy Steps)

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

Aung Shine
Written by Aung Shine

 

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.

calculate income tax in excel using if function

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.

calculate income tax in excel using if function

Final Output

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

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.

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.

Apply Excel VLOOKUP Function to Calculate Income Tax

  • Use AutoFill to complete the rest.

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

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.

Read More: Formula for Calculating Withholding Tax in Excel

Download Practice Workbook

Download the following workbook for practice.

Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!

Tags:

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. Reply
    mohan Jan 11, 2023 at 6:10 PM

    thank you very much.

    • Reply Avatar photo
      Shamima Jan 11, 2023 at 7:45 PM

      Hello, Mohan!

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

      Regards
      ExcelDemy

Leave a reply

About ExcelDemy.com

ExcelDemy is a place where you can learn Excel, and get solutions to your Excel & Excel VBA-related problems, Data Analysis with Excel, etc. We provide tips, how to guide, provide online training, and also provide Excel solutions to your business problems.

See Our Reviews at

trustpilot review

ExcelDemy Consulting Services

Submit Excel Task

ExcelDemy Online Training


Join ExcelDemy Forum
DMCA.com Protection Status

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo