Free Download · Excel workbook
TDS Receivable Aging Workbook
TDS receivable ledger vs Form 168 (the April 2026 successor to Form 26AS) in one Excel workbook. Cross-era Section-code-to-payment-code mapping (194C → 1002, 194J → 1005, and the full set), 4-bucket aging via DATEDIF, a 180-day Section 197 escalation flag, and a deductor follow-up list with a mail-merge data source ready for a Word query-letter run. Ships with illustrative data so you can see it working — replace with your ledger to run your own month.
What's inside — 7 components
TDS receivable input table
A single tab keyed on PAN + Section Code + Period + Amount — the columns Indian finance teams paste from Tally, SAP, Oracle, or a customer ledger export. One row per TDS certificate expected against a raised invoice, ready for the two-key match below.
Form 168 download layout with two-key XLOOKUP match
Second tab mirrors the Form 168 line layout (the April 2026 successor to Form 26AS). A composite key of PAN + Payment Code + Period drives an XLOOKUP against your receivable input so a customer entered under two payment codes still resolves cleanly.
Cross-era Section-code-to-payment-code mapping
Reference tab with the mapping every accounts team needs during the transition: 194C → 1002, 194J → 1005, 194H → 1015, 194Q → 1031, 194O → 1011, and the full set the workbook draws on. Formulas translate a legacy 194-series receivable entry into the new payment code Form 168 will file under, so aged pre-April 2026 balances still match cleanly.
4-bucket aging with DATEDIF
Every unmatched TDS receivable line is auto-bucketed into 0–60 / 61–120 / 121–180 / 180+ days using a DATEDIF formula off deduction date, so the workbook always shows how far each line has slipped without a manual recut.
180-day Section 197 escalation trigger
Conditional formatting flags every line that has crossed the 180-day mark red — the point at which a Section 197 (lower deduction certificate) route or a formal deductor query letter becomes the escalation of record. Nothing older than 180 days can hide in the aging report.
Deductor priority list — SORT + FILTER
Pivot of unmatched TDS lines by deductor PAN, sorted first by 180+ days exposure and then by total amount at risk. The deductor with the biggest, oldest slippage is always the top row — the follow-up call list writes itself.
Mail-merge template for deductor query letter
A separate tab structured as a mail-merge source with Deductor Name / PAN / Section Code / Period / Amount / Days Aged columns — ready to feed a Word mail-merge that generates a query letter per deductor. The letter body itself lives in the Playbook blackbook (Brief 13); this workbook produces the data half.
Illustrative data included.
The workbook ships pre-populated with sample TDS receivable lines, sample Form 168
rows, and sample deductor entries so every XLOOKUP, DATEDIF, SORT, and FILTER is
visibly wired up when you first open it. A REPLACE WITH YOUR LEDGER DATA note sits on each input tab —
clear the sample rows before pasting your own quarter.
Get the Workbook
The download appears immediately below — no wait, no follow-up call.
Your Workbook is ready
Download tds-receivable-aging-workbook.xlsx →Submission failed. You can still download the Workbook directly.
Where this workbook fits
This is the operational asset for the TDS leg of a monthly close. Read the full recipe, the runbook it slots into, the pillar the whole methodology sits under, or the process-design sibling that catalogues the failure modes it is designed to catch.
TDS Receivable Aging in Excel — Full Recipe
The step-by-step article the workbook operationalises: DATEDIF aging, XLOOKUP two-key match, cross-era mapping formula-by-formula.
TDS Reconciliation Runbook — Monthly & Quarterly
The parent runbook this workbook slots into (Brief 2, Phase 1): what to do each week of the TDS close cycle and when to send the deductor query batch.
Monthly Close Playbook — India Pillar
The umbrella methodology: how the TDS leg fits alongside the GST, bank, and platform-settlement legs of a monthly close.
Form 26AS Reconciliation Failure Modes
The bidirectional process-design sibling (Brief 2): the 12 recurring failure modes this workbook is designed to catch, ranked by Action Priority.
Form 168 — the New TDS Statement (April 2026)
Reference explainer on the Form 168 line layout, filing cadence, and how it differs from the outgoing Form 26AS the workbook mirrors.
TDS Payment Codes 1001–1092 — Reference
The full new-era payment code list the cross-era mapping tab draws on: what each code means and which pre-April 2026 Section it succeeds.
Three-Way ITC Reconciliation Workbook
Sibling free workbook: purchase register vs GSTR-2B vs IMS actions, dropdown categorisation, Section 16(4) countdown, supplier follow-up list.
TDS Reconciliation Software
The automated equivalent — what a live TDS receivable-vs-Form-168 reconciliation looks like when a workbook is not enough.
Frequently Asked Questions
Is the workbook really free?
Yes. There is no charge and no trial period. The download link appears immediately on this page after you submit the form — nothing is emailed to you first, and there is no waiting period.
What happens after I submit the form?
The download link appears immediately on this page — nothing is emailed to you first, and there is no waiting period. We use your details to send an occasional update on Indian reconciliation (statutory changes, new calculators, worked examples); you can ignore or unsubscribe from those at any time without losing access to the download you already have.
My aged TDS receivables were deducted under old Section codes (194C, 194J, etc.) — will the workbook still match them against Form 168?
Yes — that is exactly what the cross-era mapping tab is for. Every pre-April 2026 receivable line entered with a legacy Section code (194C, 194J, 194H, 194Q, 194O, and the full set the workbook draws on) is translated into its Form 168 payment code (1002, 1005, 1015, 1031, 1011, respectively) before the XLOOKUP fires. Aged pre-cutover balances match cleanly against post-cutover Form 168 downloads without a manual recut.
What if my ERP is not standard?
The workbook is Excel-native and system-agnostic. You paste your TDS receivable ledger from whichever accounting system you use (Tally, SAP, Oracle, BUSY, Zoho, Odoo, or a raw CSV export) into the input tab, drop your Form 168 download into the Form 168 tab, and the two-key XLOOKUP, aging, and deductor priority list all recompute. No integration, no macros, no add-ins — just XLOOKUP, DATEDIF, SORT, and FILTER.
Where does the 180-day Section 197 escalation trigger come from?
Section 197 of the Income-tax Act lets a payee apply for a lower deduction certificate; in practice, 180 days is the point at which a TDS receivable that has failed to appear in Form 168 needs a formal escalation — either a deductor query letter or a Section 197 route for future deductions. The workbook flags every line that crosses 180 days red and surfaces the deductor at the top of the follow-up list, so the escalation happens on schedule rather than being noticed at year-end.
How does the mail-merge template work?
The mail-merge tab is structured as a Word mail-merge data source — one row per deductor query, with Deductor Name / PAN / Section Code / Period / Amount / Days Aged as merge fields. You point a Word mail-merge document (the query letter body itself lives in the Playbook blackbook, Brief 13) at this tab as the recipient list, and Word generates one query letter per deductor. The workbook produces the data half of the merge; you supply the letter body.
Disclaimer.
The pre-loaded rows in the workbook are illustrative only — invented deductor names,
invented PANs, invented amounts — so every XLOOKUP, DATEDIF, SORT, and FILTER has
something to compute against on first open. Replace with your ledger data before running
an actual quarter. This workbook is a working template, not tax advice; verify TDS
categorisation and Section 197 escalation decisions with your Chartered Accountant before
acting.
When the workbook stops scaling
Excel handles a few hundred deductors cleanly and a few thousand slowly. Past that — or once Form 168 needs to be reconciled continuously through the quarter rather than at the end — a live TDS receivable-vs-Form-168 reconciliation makes sense. TransactIG configures in 2–4 weeks. ISO 27001:2022 certified.