Aung Shine
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.
Download Practice Workbook
Download the following workbook to practice by yourself.
Step by Step Procedures to Calculate Income Tax in Excel Using IF Function
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.
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.
Final Output
- At last, use the AutoFill tool to return the other income taxes.
- As a result, you’ll get the desired tax amounts.
🔎 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
Similar Readings
- How to Calculate Social Security Tax in Excel
- Formula for Calculating Withholding Tax in Excel (4 Effective Variants)
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.
- Finally, use AutoFill to complete the rest.
- Thus, you’ll get the income tax of other salary amounts.
🔎 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: Income Tax Computation in Excel Format (4 Suitable Solutions)
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: Computation of Income Tax Format in Excel for Companies
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. Follow the ExcelDemy website for more articles like this. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.
thank you very much.
Hello, Mohan!
Thanks for your appreciation. Stay in touch with ExcelDemy for more helpful content.
Regards
ExcelDemy