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.
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.
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
- Then, after pressing Enter, you will see the taxable income in cell E5.
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
- Finally, after pressing Enter, you will compute the payable tax in cell E6.
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.
- 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
- Then, after pressing Enter, we will see the taxable income in cell B8.
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
- 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
- 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
- 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
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
- Finally, after pressing Enter, you will compute the total tax in cell C8.
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)
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.
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))))))
- Then, press Enter to see the value of income tax in cell D5.
- Finally, use the Autofill to drag the formula to the lower cell until you get the output.
Step 3:
- Now, compute the highest tax in cell E12 by using the following formula.
=(B13-B12)*D12
- Then, press Enter to see the result in cell E12.
- Finally, use the Autofill to drag the formula to the lower cell until you obtain the output.
Step 4:
- Now, compute the cumulative tax in cell F12 by applying the following formula.
=(C13-C12)*E12
- Then, press Enter to see the result in cell F12.
- 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
- Then, press Enter to get the value.
- Finally, use the Autofill to drag the formula to the lower cell until you obtain the output.
Step 5:
- To compute the individual cumulative tax for Nathan, type the following formula in cell E5.
=F12+D5
- Then, press Enter to get the individual cumulative tax for Nathan in cell E5.
- Finally, use the Autofill to drag the formula to the lower cell for computing individual income tax.
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.
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)
- Then, press Enter to see the value of income tax in cell D5.
- Finally, use the Autofill to drag the formula to the lower cell until you want the output.
Step 3:
- Now, compute the highest tax in cell E12 using the following formula.
=(B13-B12)*D12
- Then, press Enter to see the result in cell E12.
- Finally, use the Autofill to drag the formula to the lower cell until you get the output.
Step 4:
- Now, compute the cumulative tax in cell F12 using the following formula.
=(C13-C12)*E12
- Then, press Enter to see the result in cell F12.
- 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
- Then, press Enter to get the value.
- Finally, use the Autofill to drag the formula to the lower cell till you want the output.
Step 5:
- To compute the individual cumulative tax for Nathan, type the following formula in cell E5.
=F12+D5
- Then, press Enter to get the individual cumulative tax for Nathan in cell E5.
- Finally, use the Autofill to drag the formula to the lower cell for computing individual income tax.
- 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.