Build a TDS Calculator in Excel (Section, Rate, Threshold and PAN Check)

📎 This article includes 1 downloadable practice file ↓

Accounts teams deduct TDS on contractor bills, professional fees, commission and rent every week. Doing it from memory leads to wrong rates, missed thresholds and interest later. A small Excel calculator driven by one rate table makes it consistent, and when the rates change you update the table, not fifty formulas.

Important: the rates and thresholds below are examples to show the method. TDS rules change in Budgets; check the current rates on the Income Tax Department’s website or with your tax adviser before using any figure.

Step 1: the section master

On a Sections sheet, one row per section, as a Table named Sec:

(Example values; some sections have only one kind of threshold, so the same figure sits in both columns.)

Step 2: the payments sheet

Columns: Vendor, Section, Payee type (Individual / Company / Firm), PAN available (Yes/No), Amount, Paid earlier this year. Use Data Validation drop-downs for Section and Payee type so nobody types “194-C”.

Step 3: rate lookup

=IF([@Type]="Individual",
    XLOOKUP([@Section], Sec[Section], Sec[Rate Ind]),
    XLOOKUP([@Section], Sec[Section], Sec[Rate Oth]))

Step 4: does TDS apply? (threshold test)

For many sections, TDS applies when a single payment crosses one limit or the year’s total crosses another:

=OR([@Amount] > XLOOKUP([@Section], Sec[Section], Sec[Single]),
    [@Amount] + [@[Paid earlier]] > XLOOKUP([@Section], Sec[Section], Sec[Yearly]))

Kumar Builders’ ₹45,000 bill crosses the ₹30,000 single limit, so TDS applies even though the yearly total is under ₹1 lakh.

Step 5: no PAN means a higher rate

Where the payee has not given a PAN, the law requires a higher rate (commonly 20%, or the normal rate if that’s higher). Build it in:

Applied rate: =IF([@PAN]="No", MAX(20%, [@Rate]), [@Rate])
TDS:          =IF([@Applies], ROUND([@Amount] * [@[Applied rate]], 0), 0)
Net payable:  =[@Amount] - [@TDS]

Step 6: summary for the challan and return

=SUMIFS(Pay[TDS], Pay[Section], "194C")
=SUMIFS(Pay[TDS], Pay[Section], "194C", Pay[Date], ">=" & DATE(2026,10,1), Pay[Date], "<=" & DATE(2026,10,31))
=COUNTIFS(Pay[PAN], "No")

A pivot with Section in rows and month in columns gives the deposit amounts at a glance. Deposit deadlines and return due dates are on the TRACES/Income Tax portals.

Checks worth adding

• PAN format check with Data Validation (5 letters, 4 digits, 1 letter); see data validation.
• The fourth character of a PAN shows the holder type (P individual, C company, F firm, H HUF). Compare it with your Payee type column: =MID(PAN,4,1).
• Flag vendors close to a yearly threshold so the next bill isn’t missed.

Where people go wrong

Practice

Download the practice workbook below. It has an example section table and 8 vendor payments. Each task has an automatic ✓ check, and the Answers sheet has a working formula if you get stuck.

See also: GST calculations in Excel.