Accounts teams deduct TDS on contractor, professional, rent and commission payments. An Excel sheet that picks the right rate and checks thresholds prevents most errors.

1. The rate table

Sheet Rates (Table TDSRates): Section, Nature, Rate (individual/HUF), Rate (others), Single-payment threshold, Annual threshold. Fill it from the current year’s rate chart — rates and limits change, so keep this in one place.

2. The payment register

Columns: Date, Vendor, PAN, Vendor type (Individual/Company), Section, Amount.

3. Pick the rate

“`excel
=INDEX(IF([@[Vendor type]]=”Company”, TDSRates[Rate Others], TDSRates[Rate Individual]),
MATCH([@Section], TDSRates[Section], 0))
“`

4. Missing PAN: higher rate

If a valid PAN is not furnished, TDS is generally deducted at a higher rate (commonly 20%, or the normal rate if higher):

“`excel
=IF(AND(LEN([@PAN])=10, ISNUMBER(–MID([@PAN],6,4))), [@Rate], MAX([@Rate], 20%))
“`

5. Threshold check

TDS applies when a single payment or the year’s total to that vendor under that section crosses the limit:

“`excel
=SUMIFS([Amount], [Vendor], [@Vendor], [Section], [@Section], [Date], “<="&[@Date]) ```

Compare this running total and the single amount with the thresholds; deduct only when either is crossed.

6. Monthly summary for challans

A pivot with Section in rows and month in columns gives the amounts to deposit by the 7th of the next month.