Computation of Income Tax Format in Excel for Companies

Get FREE Advanced Excel Exercises with Solutions!

Every eligible citizen must timely pay their income taxes. Not only citizens but also individual companies also have to pay income tax. But many of us don’t know the calculation of income tax. So, the income tax may remain unpaid. The amount of tax due based on one’s income might be tricky at times. So, It’s essential to know the calculation of income tax. With the help of Microsoft Excel, we can easily do this work. In this article, we will demonstrate the computation of income tax format in Excel for companies.


What Is Income Tax?

An income tax is a tax that is put on people or organizations in relation to the types of income. A government may impose an income tax as a direct tax on the earnings of its people. Or, the national government levies income tax on individuals’ earnings during the fiscal year. The word income tax is a category of tax that authorities apply to income. And this is generated by organizations and people under their control. So, an income tax is a charge levied against people or organizations in relation to their earnings.


Computation of Income Tax Format in Excel for Companies: 4 Ways

Companies are liable to give income tax. The government uses taxes to fund a variety of social programs, including job initiatives. In many regions, companies take the income tax from the employee. We will now look at the approaches from different perspectives for the computation of income tax format in Excel for companies.


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

We use the taxable income to determine our tax liability for a certain tax year. This is the friction of our gross income. The basis of an income tax system is referred to as taxable income. A government may impose an income tax as a direct tax on the earnings of its people. After deductions and exemptions, taxable income is the amount of income that is liable to tax. Let’s utilize the calculation of income tax for a small company that just launched its business.

STEPS:

  • Firstly, place the Gross Income. This is the whole amount received from all sources, including refunds, discounts, and allowances, before subtracting any costs or taxes.
  • Secondly, insert the Total Deductions and the Total Exemptions.

4 Handy Approaches for Computation of Income Tax in Excel Format for Companies

  • Thirdly, select the cell to compute the taxable income. To calculate the taxable income, subtract total deductions and total exemptions from gross income into that selected cell.
  • Put the subtraction formula into the cell.
=C4-C5-C6
  • Further, press Enter to see the result.

4 Handy Approaches for Computation of Income Tax in Excel Format for Companies

  • Suppose, with regard to income below $10,500, the tax rate is now 10%.
  • Now, calculate the tax using the formula below.
=C7*10/100
  • Then, hit the Enter button on your keyboard. And, the formula will appear in the formula bar.

4 Handy Approaches for Computation of Income Tax in Excel Format for Companies

  • This is how we can calculate the payable tax.

2. Computation of Excel Income Tax with Tax Brackets

We will show another approach to computing income tax progressively by utilizing tax brackets in Excel format. For various income categories, the federal government establishes tax brackets. A range of incomes subject to a specific income tax rate is referred to as a tax bracket. The tax rate that we will pay on each component of the income is displayed in tax brackets. Let’s look at the steps down.

STEPS:

  • In the first place, put all the Gross Income, Total Deductions also Total Exemptions.
  • Then, set the information of the Taxable Income Bracket and the Tax Rate for every range for Taxable Income.

4 Handy Approaches for Computation of Income Tax in Excel Format for Companies

  • Subsequently, we will now compute the tax based on the tax bracket. In order to achieve this, we shall split the taxable income into tax brackets. Assume that our taxable income is within the range of $90,000 and $180,000 which is included in the 4th tax bracket.
  • The fourth tax band, which has rates of 10%, 12%, 22%, and 24% on income, is where the tax is computed from now on.
  • Here, we get the Tax Rate of Taxable Amount by subtracting the upper limit and the lower limit of the Taxable Income Bracket.
  • For 10% tax rate the taxable income bracket ranges $0 to $10,275. So, ($10,275 – $0) = $10,275.
  • Thus, to calculate the tax with a tax rate of 10%, the taxable amount. Select a cell and put the formula into that cell.
=10275*10/100
  • Further, press the Enter button.

4 Handy Approaches for Computation of Income Tax in Excel Format for Companies

  • The taxable income range for a 20% tax rate is $10,275 to $41,775. So, ($41,775 $10,275) equals $31,500.
  • Furthermore, for a tax rate of 20%, input the formula in cell C6.
=31500*20/100
  • Afterward, hit Enter on the keyboard.

4 Handy Approaches for Computation of Income Tax in Excel Format for Companies

  • The range for 22% tax rate is $41,775 to $89,075. And the result of the subtraction is ($89,075 $41,775) = $47,300.
  • Likewise the previous one, now for the tax rate of 22%, Place the formula in cell C7.
=47300*22/100
  • To finish the computation, hit the Enter key.

4 Handy Approaches for Computation of Income Tax in Excel Format for Companies

  • Finally, for a 24% tax rate the taxable income bracket ranges $89,075 to $170,050. So, ($10,275 – $89,075) = $25,925.
  • Similarly, for 24% of the tax rate, put the formula into the selected cell.
=25925*24/100
  • Lastly, to finish the calculation, press the Enter key.

4 Handy Approaches for Computation of Income Tax in Excel Format for Companies

  • Then, to calculate the Taxable Income, select the specified cell and insert the formula of subtraction.
=C4-C5-C6
  • Press Enter.

4 Handy Approaches for Computation of Income Tax in Excel Format for Companies

  • Finally, to compute the Total Tax type the formula of summation.
=F5+F6+F7+F8
  • Lastly, press the Enter key to finish the procedure.


3. Insert IF Function for Income Tax Calculation in Excel

One of the most used tools in Excel is the IF function, which enables us to compare values logically to expectations. Thus, an IF statement can have two outcomes. If the evaluation is True, the first outcome is what we get; if it is False, we get the second outcome. We may also utilize the IF function to calculate income tax in Excel format. Although the formula for this function is relatively long, it operates quickly. Let’s go over the processes to calculate income tax format in Excel.

STEPS:

  • Firstly, put the necessary information. The information is the Name of the employees and their Total Income.
  • Secondly, the Lower Limit and the Upper Limit of the Tax Rate.

  • At present, select the cell and put the formula to calculate the Income Tax.
=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))))))
  • Hit the Enter key to complete the process.

4 Handy Approaches for Computation of Income Tax in Excel Format for Companies

  • Now, drag the Fill Handle down to duplicate the formula over the range. Or, to AutoFill the range, double-click on the plus (+) symbol.

4 Handy Approaches for Computation of Income Tax in Excel Format for Companies

  • Finally, you can see the Income Tax result.

4 Handy Approaches for Computation of Income Tax in Excel Format for Companies

🔎 How Does the Formula Work?

  • IF(C5>$C$16,(C5-36000)*$D$17): This will compare the upper limit and the income tax. And then, abstract the upper limit from the income tax. After that, multiply the tax rate with the result.
  • 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))))): Similarly, every part of the IF function will evaluate the income tax and the upper limit. After that, disregard the income tax’s higher limit. Multiply the outcome by the tax rate after that.
  • 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)))))): Finally, with this we will compare with $0 and get the result of income tax using this formula.
  • Subsequently, select the cell where you want to get the Highest Tax.
  • Consequently, insert the formula into that cell.
=(B13-B12)*D12
  • Hit the Enter key on your keyboard.
  • The result will now display in the selected cell, along with the formula in the formula bar.

  • Further, to copy the formula over the range, drag the Fill Handle down. Or, double-click on the plus (+) sign to AutoFill the range.

  • Thus, we are able to see the result of the Highest Tax.
  • After that, we need to find out the Cumulative Tax. The cumulative tax is the tax payable on an employee’s total income from January 1 until the present. For this, select the cell that we are creating for the cumulative tax.
  • Next, time the formula for the calculation of the first cumulative tax.
=(C13-C12)*E12
  • Press Enter.

4 Handy Approaches for Computation of Income Tax in Excel Format for Companies

  • For the second cumulative tax, we are using a different formula.
  • So, type the formula into the cell where you want to see the result.
=E12+E13
  • Then, press the Enter key on your keyboard.

  • Now, you will be able to see the result in the selected cell and the formula will appear in the formula bar.
  • Further, to copy the formula over the range, drag the Fill Handle down. Alternatively, double-click on the plus (+) sign to AutoFill the range.

  • Thus, we can see the result of the cumulative tax.

  • Afterward, we have to calculate the individual cumulative tax.
  • For this, select a cell where the result will be displayed.
  • Then, simply just put the formula which is the summation of Cumulative Tax and Income Tax.
=F12+D5
  • Press the Enter key.

  • Drag the Fill Handle to the bottom to reproduce the formula throughout the whole range. Instead of doing this, you can just double-click the plus (+) sign to AutoFill the range.

4 Handy Approaches for Computation of Income Tax in Excel Format for Companies

  • And, that’s it! By just following the above steps we can calculate the income tax of each employee of a company.


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

We use the VLOOKUP function when we need to search by row in a table or a range. Use the VLOOKUP function if we frequently need to discover a certain type of information or if we need to search through a huge spreadsheet for it. We can compute the income tax using VLOOKUP. Let’s follow the procedures to utilize the function to calculate the income tax.

STEPS:

  • To begin with, select the cell for calculating the income tax for an individual employee.
  • Next, write down the formula in that cell.
=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)
  • Hit the Enter key to complete the process.

4 Handy Approaches for Computation of Income Tax in Excel Format for Companies

  • Then, by dragging the Fill Handle to the bottom we can reproduce the formula throughout the whole range. An alternative way is to double-click the plus (+) sign to AutoFill the range.

  • You may now view the Income Tax outcome.

🔎 How Does the Formula Work?

  • (C5-VLOOKUP(C5,$B$12:$D$17,1,TRUE))*VLOOKUP(C5,$B$12:$D$17,3,TRUE): It is for getting the cell information of each specified cell and then multiplying it with another specified cell.
  • 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): This will result in the income tax with the summation of usable cells.
  • Further, select the cell where you wish to get the highest tax rate after that.
  • Put the formula into that cell as a result.
=(B13-B12)*D12
  • On your keyboard, press the Enter key.

  • The formula in the formula bar will now appear in the selected cell along with the result.
  • Then, drag the Fill Handle down to duplicate the formula throughout the whole range. Alternatively, you may AutoFill the range by double-clicking the addition (+) symbol.

  • We can thus see the outcome of the Highest Tax.
  • Then, to determine the cumulative tax, choose the cell that we will be creating for the cumulative tax for this.
  • Next, calculate the first cumulative tax using the timing formula.
=(C13-C12)*E12
  • Enter the key.

  • Our calculation for the second cumulative tax is different.
  • Next, put simply, enter the formula in the cell where you wish the outcome to appear.
=E12+E13
  • Further, press the Enter key on your keyboard after that.
  • The outcome will now be displayed in the selected cell, and the formula will be visible in the formula bar.

  • Drag the Fill Handle down to duplicate the formula throughout the whole range. Alternatively, you may AutoFill the range by double-clicking on the addition (+) symbol.

  • As a consequence, we can observe how the cumulative tax has turned out.
  • Then, we have to calculate the individual cumulative tax.
  • Choose a cell where you want to show the result.
  • Now, insert the formula which is for the total of the cumulative tax and the income tax calculation.
=F12+D5
  • Hit the Enter key.

  • To replicate the formula throughout the whole range, drag the Fill Handle to the bottom. Simply double-click the addition (+) sign to AutoFill the range in place of doing this.

  • That’s all, then! We can compute the income tax owed by each employee of a firm by simply following the methods above.

Read More: Income Tax Computation in Excel Format


Things to Remember

  • When using the VLOOKUP function to compute income tax, you must press the F4 key to lock the value of the table array. If you use Autofill and drag the formula into the bottom cells, you won’t obtain the true number.
  • Similar to this, you must press the F4 key to lock the result of the logical test when using the IF function. If not, you will experience the same issue.

Download Practice Workbook

You can download the workbook and practice with them.


Conclusion

The above methods will assist you in executing the Computation of Income Tax Format in Excel for Companies. Hope this will help you! Please let us know in the comment section if you have any questions, suggestions, or feedback.


Related Articles

<< Go Back to Excel Tax Calculator | Finance Templates | Excel Templates

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.
Sabrina Ayon
Sabrina Ayon

Sabrina Ayon, a Computer Science and Engineering graduate from United International University, has been an integral part of the ExcelDemy project for two years. She authored 150+ articles, excelling in instructing through visually engaging Excel tutorials. With a passion for teaching, Sabrina conducted sessions on Excel VBA, sharing her knowledge and insights with others. Currently holding the position of Project Manager for the ExcelDemy Visual Development Project, she oversees various aspects of the project, ensuring its smooth operation... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo