Formula for Calculating Withholding Tax in Excel (4 Effective Variants)

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 Withholding Tax in Excel


Download Practice Workbook


4 Effective Variants of Formula for Calculating Withholding Tax in Excel

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-C7

Here,
C5 = Gross Income
C6 = Total Deductions
C7 = Total Exemptions

Formula for Withholding Tax in Excel

  • Press ENTER to have the taxable income.

  • Now, input the formula mentioned below to calculate the withholding tax.
=C9*C10

Here,
C9 = Taxable Income
C10 = Tax rate

Formula for Withholding Tax in Excel

  • Finally, press ENTER to have the withholding tax value.

Read More: Reverse Tax Calculation Formula in Excel (Apply with Easy Steps)


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-C7

Here,
C5 = Gross Income
C6 = Total Deductions
C7 = Total Exemptions

Formula for Withholding Tax in Excel

  • 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)*D10

Here, The amount is calculated by subtracting the upper value and lower value in the range and multiplying it by the tax rate.

Formula for Withholding Tax in Excel

  • Hit ENTER to have the tax amount in that range.

  • AutoFill till the second last range of the taxable income.

Formula for Withholding Tax in Excel

  • After that, use the following formula to calculate the tax in the range of the tax amount.
=(C15-B13)*D13

Here,
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.

Formula for Withholding Tax in Excel

  • 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.

Formula for Withholding Tax in Excel

Read More: How to Calculate Income Tax in Excel Using IF Function (With Easy Steps)


Similar Readings


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-C7

Here,
C5 = Gross Income
C6 = Total Deductions
C7 = Total Exemptions

Formula for Withholding Tax in Excel

  • 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

Formula for Withholding Tax in Excel

  • 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
    Use the following formula to do so in cell E11:
=D11-D10

Here, I subtracted the tax rate close to one another

Formula for Withholding Tax in Excel

  • Press ENTER to have the difference in rates.

Formula for Withholding Tax in Excel

  • Then, calculate the Taxable Income. For this, input the following formula to do so.
=C5-C6-C7

Where,
C5 = Gross Income
C6 = Total Deductions
C7 = Total Exemptions

  • Along that, hit the ENTER button.

Formula for Withholding Tax in Excel

  • 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-C10

Here,
C15 = Taxable income
C10
= Highest amount in the first range

  • Press ENTER.

Formula for Withholding Tax in Excel

  • 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.

Formula for Withholding Tax in Excel

  • 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.

Formula for Withholding Tax in Excel


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. You can visit our Exceldemy site for further information on Excel.


Related Articles

Naimul Hasan Arif

Naimul Hasan Arif

Hello, I am Arif. I am an Engineer who graduated from BUET. I want to pursue an enterprising career in a progressive environment where my skills & knowledge can be enhanced to their maximum potential to contribute to the overall success and growth of the organization.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo