Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

TDS Deduction on Salary Calculation in Excel Format

Excel is the most widely used tool for dealing with massive datasets. We can perform myriads of tasks of multiple dimensions in Excel. In this article, I will explain how to perform a TDS deduction on salary calculation in Excel format.


Download Practice Workbook

Download this workbook and practice while going through the article.


6 Steps for Deduction of TDS on Salary Calculation in Excel

TDS, or Tax Deducted at Source, is an amount deducted when a specific payment is made, such as a wage, commission, lease, interest, professional fees, and so on. The person making the payment deducts tax at the source, whereas the individual receiving the payment/income must pay the tax. It reduces tax fraud because the taxes are collected at the moment of payment.
Here, I will explain the process of TDS deduction on salary calculation in Excel. For simplicity, I have divided the process into 6 easy steps.
Please note that the detailed breakdown of the allowances and deduced amounts are absent here because it varies from country to country.


Step 1: Calculate Yearly Salary

Let’s assume that the monthly salary of a person is Rs 70,000. Now, I will calculate the annual salary.

  • To do so, first, go to C8 and write down the following formula
=C7*12

tds deduction on salary calculation in excel format

  • Then, press ENTER to get the output.

Yearly salary tds deduction on salary calculation in excel format

Read More: How to Calculate Basic Salary in Excel (3 Common Cases)


Step 2: Measure Total Taxable Income from Salary

The next step is to calculate the total taxable income from the salary.
Let’s assume that, other taxable income is Rs 10,000, and the deduction is Rs. 2000.

  • Now, go to C11 and write down the following formula
=C8+C9-C10

taxable income tds deduction on salary calculation in excel format

  • Then, press ENTER to get the output.

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


Step 3: Calculate Tax on Taxable Income

The next step is to calculate the tax on the taxable income. The tax is calculated based on different slabs.

To calculate it,

  • Go to C12 and write down the following formula
=IF(C6<60,IF(C11>1000000,(((C11-1000000)*0.3)+112500),IF(C11>500000,(((C11-500000)*0.2)+12500),IF(C11>250000,(C11-250000)*0.05,0))),IF(C6>79,IF(C11>1000000,(((C11-1000000)*0.3)+110000),IF(C11>500000,(((C11-500000)*0.2)+0),0)),IF(C11>1000000,(((C11-1000000)*0.3)+110000),IF(C11>500000,(((C11-500000)*0.2)+10000),IF(C11>300000,(C11-300000)*0.05,0)))))

tax calculation tds deduction on salary calculation in excel format

Formula Explanation

  • The 1st logical test is C6<60 which is TRUE.
  • So the corresponding output is IF(C11>1000000,(((C11-1000000)*0.3)+112500),IF(C11>500000,(((C11-500000)*0.2)+12500),IF(C11>250000,(C11-250000)*0.05,0)))
  • The value of this output is 82100.
  • Then, press ENTER to get the output.

Read More: How to Calculate Monthly Salary in Excel (with Easy Steps)


Similar Readings


Step 4: Measure Education Cess

The next step is to measure the education cess. The assumption is that the education cess is 2% of the tax on total taxable income.

To calculate it,

  • Go to C13 and write down the following formula
=C12*2%

education cess tds deduction on salary calculation in excel format

  • Then, press ENTER to get the output.

Read More: How to Calculate Gross Salary in Excel (3 Useful Methods)


Step 5: Calculate Tax Liability for Year

The next step is to calculate the yearly tax liability. For this step, I will use the SUM function. To calculate it,

  • Go to C14 and write down the formula
=SUM(C12:C13)

yearly tax tds deduction on salary calculation in excel format

  • Then, press ENTER to get the output.

Read More: How to Calculate Net Salary in Excel (With Easy Steps)


Step 6: Measure Monthly TDS

The final step is to calculate the monthly TDS. To calculate it,

  • Go to C15 and write down the following formula
=C14/12

  • Then, press ENTER to get the output.

tds deduction on salary calculation in excel format

Read More: How to Create a Monthly Salary Sheet Format in Excel (with Easy Steps)


Things to Remember

  • The tax is calculated based on different slabs.
  • The breakdown of the allowances and deductions varies according to the different sections.

Conclusion

In this article, I have explained how to perform a TDS deduction on salary calculation in Excel format. I hope it helps everyone. If you have any suggestions, ideas, or feedback, please feel free to comment below. Please visit Exceldemy for more useful articles like this.


Related Articles

Akib

Akib

Hi, this is MD Akib Bin Rashid. I completed my BSc in Engineering back in 2019. After that, I did an MBA. Then, I joined as an Excel and VBA Content Developer at SOFTEKO Digital. Being passionate about data analytics and finance, I will try to assist you with Excel

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo