Income Tax Computation in Excel Format (4 Suitable Solutions)

It is mandatory for every eligible citizen to pay income tax on time. Sometimes, it is hard to determine the amount of tax based on one’s income. In this article, we will show you four easy ways of income tax computation in Excel format.


Download Practice Workbook

You can download the free Excel workbook from here and practice on your own.


4 Suitable Solutions for Computation of Income Tax in Excel Format

An income tax is a kind of tax that the government of one country imposes on its citizens based on their individual income. One of the main duties of  citizens is to pay their taxes regularly. In this article, you will see the use of taxable income, tax brackets, the IF function, and the VLOOKUP function for the computation of income tax in Excel format. You can see a sample data set given below that will help us compute income tax in Excel format.

Handy Ways for Computation of Income Tax in Excel Format

1. Utilizing Taxable Income for Computation of Income Tax in Excel Format

This is the easiest method to compute income tax in Excel format. You can calculate income tax from taxable income. The steps for this method are as follows.

Step 1:

  • Firstly, you will calculate taxable income from gross income.

Handy Ways for Computation of Income Tax in Excel Format

Step 2:

  • To calculate the taxable income, subtract total deductions and total exemptions from gross income in cell E5. Here is the following formula:
=B5-C5-D5

Handy Ways for Computation of Income Tax in Excel Format

  • Then, after pressing Enter, you will see the taxable income in cell E5.

Handy Ways for Computation of Income Tax in Excel Format

Step 3:

  • Now, considering an income below $10,500, the tax rate is 10%.
  • As the taxable income in cell E5 is $10,200, calculate the tax in cell E6 using the following formula.
=E5*10/100

Handy Ways for Computation of Income Tax in Excel Format

  • Finally, after pressing Enter, you will compute the payable tax in cell E6.

Handy Ways for Computation of Income Tax in Excel Format

Read More: Computation of Income Tax Format in Excel for Companies


2. Using Tax Brackets for Computation of Income Tax in Excel Format

Now, you will see another method to compute income tax progressively using tax brackets in Excel format. Tax brackets are determined by the federal government for different ranges of income. To compute income tax in Excel by using this method, see the following step.

Step 1:

  • Firstly, we will calculate taxable income from gross income like the previous method.

Handy Ways for Computation of Income Tax in Excel Format

  • To calculate the taxable income, we will subtract the total deductions and total exemptions from gross income in cell B8. Write down the following formula for the calculation.
=B5-C5-D5

Handy Ways for Computation of Income Tax in Excel Format

  • Then, after pressing Enter, we will see the taxable income in cell B8.

Handy Ways for Computation of Income Tax in Excel Format

Step 2:

  • Now, we will calculate the tax according to the tax bracket.
  • To do so, we will divide the taxable income according to the tax bracket.
  • Our taxable income is between $89,075 and $170,050 (4th tax bracket) in the taxable income bracket.
  • So, the tax will be calculated till the 4th bracket, that is 10%, 12%, 22%, and then 24% on income.
  • First, we will calculate the tax for the first $10,275 with a tax rate of 10% from the taxable income in cell F11 using the following formula.
=10275*10/100

Handy Ways for Computation of Income Tax in Excel Format

  • Secondly, we will subtract $10,275 from $41,775 and then calculate the tax with the associated tax rate in cell F12 with the formula written below.
=31500*20/100

Handy Ways for Computation of Income Tax in Excel Format

  • Thirdly, we will subtract $41,775 from $89,075 and calculate the tax with the associated tax rate in cell F13 just like the previous step by using the formula below.
=47300*22/100

Handy Ways for Computation of Income Tax in Excel Format

  • Finally, for the last tax bracket, we will subtract $89,075 from $11,5000.
  • We won’t subtract $89,075 from $170,050 according to the tax bracket. Because the last limit of the taxable income is $115,000.
  • Then, we will calculate the tax with the associated tax rate in cell F14 using the following formula.
=25925*24/100

Handy Ways for Computation of Income Tax in Excel Format

Step 3:

  • Now, we will add all the tax values from the previous step and get the total tax by using the following equation in cell E8.
=F11+F12+F13+F14

Handy Ways for Computation of Income Tax in Excel Format

  • Finally, after pressing Enter, you will compute the total tax in cell C8.

Handy Ways for Computation of Income Tax in Excel Format

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


Similar Readings


3. Applying IF Function for Computation of Income Tax in Excel Format

To compute income tax in Excel format, you can also use the IF function. The formula for this function becomes quite lengthy but works quite fast. Let us see the following steps to compute income tax in the Excel format by using the IF function.

Step 1:

  • First, take the following data set to compute tax by using the IF function.

Handy Ways for Computation of Income Tax in Excel Format

Step 2:

  • Then, use the following formula of the IF function to compute the income tax for Nathan in cell D5.
=IF(C5<$C$12,"$0",IF(C5<=$C$13,(C5-8000)*$D$13,IF(C5<=$C$14,(C5-15000)*$D$14,IF(C5<=$C$15,(C5-22000)*$D$15,IF(C5<=$C$16,(C5-29000)*$D$16,IF(C5>$C$16,(C5-36000)*$D$17))))))

Handy Ways for Computation of Income Tax in Excel Format

  • Then, press Enter to see the value of income tax in cell D5.

Handy Ways for Computation of Income Tax in Excel Format

  • Finally, use the Autofill to drag the formula to the lower cell until you get the output.

Handy Ways for Computation of Income Tax in Excel Format

Step 3:

  • Now, compute the highest tax in cell E12 by using the following formula.
=(B13-B12)*D12

Handy Ways for Computation of Income Tax in Excel Format

  • Then, press Enter to see the result in cell E12.

Handy Ways for Computation of Income Tax in Excel Format

  • Finally, use the Autofill to drag the formula to the lower cell until you obtain the output.

Handy Ways for Computation of Income Tax in Excel Format

Step 4:

  • Now, compute the cumulative tax in cell F12 by applying the following formula.
=(C13-C12)*E12

Handy Ways for Computation of Income Tax in Excel Format

  • Then, press Enter to see the result in cell F12.

Handy Ways for Computation of Income Tax in Excel Format

  • As the cumulative summation will begin from the following cell, type the following formula in cell F13 to get the cumulative tax value.
=E12+E13

Handy Ways for Computation of Income Tax in Excel Format

  • Then, press Enter to get the value.

Handy Ways for Computation of Income Tax in Excel Format

  • Finally, use the Autofill to drag the formula to the lower cell until you obtain the output.

Handy Ways for Computation of Income Tax in Excel Format

Step 5:

  • To compute the individual cumulative tax for Nathan, type the following formula in cell E5.
=F12+D5

Handy Ways for Computation of Income Tax in Excel Format

  • Then, press Enter to get the individual cumulative tax for Nathan in cell E5.

Handy Ways for Computation of Income Tax in Excel Format

  • Finally, use the Autofill to drag the formula to the lower cell for computing individual income tax.

Handy Ways for Computation of Income Tax in Excel Format


4. Inserting VLOOKUP Function for Computation of Income Tax in Excel Format

You can compute income tax using the VLOOKUP function of Excel. It is a bit of a lengthy process. Let us show you the procedure to compute income tax in Excel format by inserting the VLOOKUP function.

Step 1:

  • First, take the following data set to compute tax using the VLOOKUP function.

Handy Ways for Computation of Income Tax in Excel Format

Step 2:

  • Then, use the following formula of the VLOOKUP function to compute the income tax for Nathan in cell D5.
=VLOOKUP(C5,$B$12:$D$17,3,TRUE)+(C5-VLOOKUP(C5,$B$12:$D$17,1,TRUE))*VLOOKUP(C5,$B$12:$D$17,3,TRUE)

Handy Ways for Computation of Income Tax in Excel Format

  • Then, press Enter to see the value of income tax in cell D5.

Sample Data Set

  • Finally, use the Autofill to drag the formula to the lower cell until you want the output.

Sample Data Set

Step 3:

  • Now, compute the highest tax in cell E12 using the following formula.
=(B13-B12)*D12

Sample Data Set

  • Then, press Enter to see the result in cell E12.

Sample Data Set

  • Finally, use the Autofill to drag the formula to the lower cell until you get the output.

Sample Data Set

Step 4:

  • Now, compute the cumulative tax in cell F12 using the following formula.
=(C13-C12)*E12

Sample Data Set

  • Then, press Enter to see the result in cell F12.

Sample Data Set

  • As the cumulative summation will begin from the following cell, type the following formula in cell F13 to get the cumulative tax value.
=E12+E13

Sample Data Set

  • Then, press Enter to get the value.

Sample Data Set

  • Finally, use the Autofill to drag the formula to the lower cell till you want the output.

Sample Data Set

Step 5:

  • To compute the individual cumulative tax for Nathan, type the following formula in cell E5.
=F12+D5

Sample Data Set

  • Then, press Enter to get the individual cumulative tax for Nathan in cell E5.

Sample Data Set

  • Finally, use the Autofill to drag the formula to the lower cell for computing individual income tax.

Sample Data Set


Things to Remember:
  • While computing income tax by using the VLOOKUP function, you must lock the value of the table array using F4. Otherwise, you will not get the real value when you use Autofill to drag the formula in the lower cells.
  • Similarly, while using the IF function, you must lock the value of the logical test using F4. Else you will face the same problem.

Conclusion

That’s the end of this article. I hope you find this article helpful. After reading this article, you will be able to compute income tax in Excel format by using any of the methods. Please share any further queries or recommendations with us in the comments section below.


Related Articles

Araf

Araf

I am Araf. I have completed my B.Sc in Industrial and Production Engineering from Ahsanullah University of Science and Technology. Currently I am working as an Excel & VBA Content Developer in Softeko. With proper guideline and aid of Softeko I want to be a flexible data analyst. With my acquired knowledge and hard work, I want to contribute to the overall growth of this organization.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo