Tax is a compulsory contribution to the state’s revenue imposed by the government on workers’ income or business profit. It is also added to the cost of some goods, services, and transactions. As an ideal citizen of a country, it is a must to give a certain amount of tax in time. So, we need a way to calculate the withholding tax. In this article, I am going to discuss 4 effective ways to calculate withholding tax with formula in Excel.
For more clarification, I will use a Dataset with incomes and related deducted values. There is also a chart regarding the tax rate with the income amount.
Formula for Calculating Withholding Tax in Excel: 4 Effective Variants
1. Using Arithmetic Formula to Calculate Withholding Tax
We can calculate the withholding tax with simple arithmetic formula. It is a matter of great concern that this method is only applicable for a fixed amount of tax rate.
Steps:
- Input the tax rate first(i.e. 50%) as this method is only applicable for the fixed tax rate.
- Next, deduce the taxable income by subtracting the related factors. Input the following formula to do so.
=C5-C6-C7Here,
C5 = Gross Income
C6 = Total Deductions
C7 = Total Exemptions
- Press ENTER to have the taxable income.
- Now, input the formula mentioned below to calculate the withholding tax.
=C9*C10Here,
C9 = Taxable Income
C10 = Tax rate
- Finally, press ENTER to have the withholding tax value.
Read More: Reverse Tax Calculation Formula in Excel
2. Formula with VLOOKUP Function to Calculate Withholding Tax
The VLOOKUP function is another option to calculate withholding tax. The steps are mentioned below with a clear explanation.
Steps:
- First of all, calculate the Taxable Income. Input the following formula to do so.
=C5-C6-C7Here,
C5 = Gross Income
C6 = Total Deductions
C7 = Total Exemptions
- Next, press on the ENTERÂ button.
- Add a new column (i.e. Cumulative Amount) to have the Tax amount.
- Input the following formula to have the tax amount. But keep in mind this formula is applicable to the second last range of your taxable income.
=(C10-B10)*D10Here, The amount is calculated by subtracting the upper value and lower value in the range and multiplying it by the tax rate.
- Hit ENTER to have the tax amount in that range.
- AutoFill till the second last range of the taxable income.
- After that, use the following formula to calculate the tax in the range of the tax amount.
=(C15-B13)*D13Here,
C15 = Taxable Income
B13 = Lower limit of the range where the taxable amount lies
D13 = Tax rate
- Now, press the ENTER button to have the output.
- Now, input the following formula to calculate the withholding tax in the selected cell.
=VLOOKUP(C15,B10:D13,4,TRUE)+(C15-VLOOKUP(C15,B10:D13,1,TRUE))*VLOOKUP(C15,B10:D13,3,TRUE)
Formula Breakdown
VLOOKUP(C15,B10:D13,3,TRUE) —> returns the tax value from the range matched to the taxable income.
Output: 1802.25
C15-VLOOKUP(C15,B10:D13,1,TRUE) —> The VLOOKUP function looks for C15 in the range B10:D13. Returns the value from the 1st column. Next, deducts the output from C15.
Output: 7209
VLOOKUP(C15,B10:D13,3,TRUE) —> returns the rate of interest
Output: 0.25
VLOOKUP(C15,B10:D13,3,TRUE)+(C15-VLOOKUP(C15,B10:D13,1,TRUE))*VLOOKUP(C15,B10:D13,3,TRUE)
1802.25 + 7209*0.25 —> returns the final tax amount.
Output: 3604.50
- Hit ENTER to have the result.
Read More: How to Calculate Income Tax in Excel Using IF Function
3. Calculate Withholding Tax with Nested IF Function
Nested IF function is another effective way to calculate withholding tax. The whole process is discussed below.
Steps:
- Find the Taxable Income For this, input the following formula to do so.
=C5-C6-C7Here,
C5 = Gross Income
C6 = Total Deductions
C7 = Total Exemptions
- Afterward, press on ENTER.
- Now, apply the following formula to have the tax value.
=IF(C15<=C10,"$0",IF(C15<=C11,(C15-C10)*D11,IF(C15<=C12,(C15-C11)*D12+1300,(C15-C12)*D13+3500)))Here, IF function searches for the condition related to the taxable income value and returns the Tax amount. The IF functions traverse until the conditions meet. Each IF checks whether the value presents within the range
- Finally, press ENTER to have the tax amount.
4. Using SUMPRODUCT Function to Calculate Withholding Tax
In order to calculate withholding tax, we can use the SUMPRODUCT function too.
Steps:
- First of all, add 2 new columns to the dataset. Here, I named them Differential, and Amount.
- Find the difference in rate in the Differential column.
Use the following formula to do so in cell E11:
=D11-D10Here, I subtracted the tax rate close to one another
- Press ENTER to have the difference in rates.
- Use Fill Handle to AutoFill the rest cells.
- Then, calculate the Taxable Income. For this, input the following formula to do so.
=C5-C6-C7Where,
C5 = Gross Income
C6 = Total Deductions
C7 = Total Exemptions
- Along that, hit the ENTERÂ button.
- Now, input the income amount related to the range in the first cell of the Amount column (i.e. $43210.00).
- Then, calculate the tax amount in the first range where the tax is allowed.
Use the following formula for this:
=$C$15-C10Here,
C15 = Taxable income
C10 = Highest amount in the first range
- Press ENTER.
- AutoFill the remaining cells.
- Now, use the following formula to have the tax amount.
=SUMPRODUCT(E10:E13,F10:F13)Here, Two arrays ranging E10:E13 and F10:F13 are multiplied first and then added the multiplied result.
- Then, hit ENTER to have the result.
Read More: How to Calculate Income Tax on Salary with Old Regime in Excel
Practice Section
You can practice here for further expertise.
Download Practice Workbook
Conclusion
I have tried to explain 4 effective ways to calculate withholding tax with formula in Excel. It will be a matter of great pleasure for me if this article could help any Excel user even a little. For any further queries, comment below.
