📎 This article includes 1 downloadable practice file ↓
Keep rates and limits in a small Rates table, never inside formulas: when the Budget changes them, you update one table.
Rate: =XLOOKUP([@Type], Rates[Type], Rates[Rate])
Limit: =XLOOKUP([@Type], Rates[Type], Rates[Single limit])
TDS: =ROUND(IF([@Amount] > Limit, [@Amount] * IF([@PAN]="No", MAX(20%, Rate), Rate), 0), 0)| Payee | Type | Amount | TDS |
|---|---|---|---|
| Swift Logistics | Contractor (company) 2% | ₹45,000 | ₹900 |
| Ravi Kumar | Contractor (individual) 1% | ₹25,000 | ₹0 (below single limit) |
| CloudSoft LLP | Professional 10% | ₹80,000 | ₹8,000 |
| Office Space | Rent 10% | ₹60,000 | ₹6,000 |
Quarter total: ₹14,900. TDS deducted in July is generally due by 7 August.
=SUMIFS([Amount], [PAN], [@PAN], [Date], "<="&[@Date]) and test it against the annual threshold.Practice
Download the workbook below. Type your formula in the yellow column; the Check column turns green when it matches Excel’s own answer, and the Answers sheet has a working formula for every task. Tax rates and limits in the examples are for practice; verify current rules before real use.
Test formulas dynamically in our web formula laboratory or download the companion Excel workbook.
📎 Practice files for this article
- 📗Practice workbookFive payments and a rate table, 7 tasks.⬇ XLSX · 15 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.