Content authored by practitioners with experience at Amazon India, Intuit QuickBooks, and the Tata Group. Meet the team →
Every Indian enterprise finance function carries a TDS receivable ledger that runs at any point between a low single-digit and a double-digit percentage of monthly revenue as working capital tied up in tax the customer has withheld but not yet remitted, and that the recipient can only claim once it appears in the tax authority's records. From April 2026 onwards, those records live in Form 168 rather than Form 26AS, and the credit table restructures around the new Section 393 payment codes 1001 through 1092 rather than the earlier free-form section references. A receivable ledger that ages against the old Section 194C, 194J, 194H, 194Q, and 194O references now has to reconcile against a Form 168 that carries payment codes 1002, 1005, 1015, 1031, and 1011. A single-key match will miss either the legacy population or the current population; a Section 197 low-deduction certificate that lapses without renewal converts an ongoing billing to standard-rate deduction; and a receivable that ages past 180 days without appearing in Form 168 is a working-capital line the finance function will fund out of its own cash until the recovery cycle closes.
Build the workbook around four input sheets keyed to the recipient's PAN. Sheet 1 is the receivable ledger extracted from the ERP with invoice number, invoice date, deductor PAN, deductor TAN, invoice value, TDS amount at the applicable rate, the old section reference, and the new payment code populated where known. Sheet 2 is the Form 168 download pasted from the TRACES portal preserving the deductor TAN, the payment code, the transaction date, the transaction amount, the tax deducted, the deposit date, and the challan identification number. Sheet 3 is a cross-era mapping table — code 1002 to 194C, 1005 to 194J, 1015 to 194H, 1031 to 194Q, 1011 to 194O — that the driver formula reads whichever key is populated on the input row. Sheet 4 is the Section 197 certificate register. A driver sheet runs XLOOKUP with a four-argument exact match against the composite of PAN plus payment-code-or-section plus transaction date plus taxable value within a five-hundred-rupee tolerance, computes the aging bucket via DATEDIF from invoice date to today, and returns a Matched, Deductor-not-filed, Reduced-in-Form-168, or 197-certificate-lapsed category. Two computed sheets fall out — the 180-day escalation queue keyed to the invoice date and the deductor priority list keyed to unique deductor PAN with aggregated exposure. Formulas use XLOOKUP, TEXTBEFORE and TEXTAFTER for TAN normalisation, ROUND to a five-hundred-rupee tolerance, DATEDIF for aging, and SORT with FILTER for the priority list.
Receivable ledger sheet with invoice number, invoice date, deductor PAN, deductor TAN, recipient PAN, invoice value, applicable section (194C, 194J, 194H, 194Q, or 194O), applicable payment code (1002, 1005, 1015, 1031, or 1011), TDS amount, and Section 197 certificate number where applicable. Form 168 download sheet with deductor TAN, deductor name, payment code, transaction date, transaction amount, tax deducted, deposit date, challan identification number, and TRACES filing quarter. Cross-era mapping table sheet with the section reference to payment code lookup. Section 197 register sheet with certificate number, issue date, validity end date, aggregate cap in rupees, applicable section, and cumulative payments received. Configuration cell for the amount tolerance in rupees (default five hundred). Named ranges on every input sheet so driver formulas reference stable names rather than volatile cell references.
A driver sheet with one row per receivable ledger entry, categorised into Matched, Deductor-not-filed, Reduced-in-Form-168, or 197-certificate-lapsed and colour-banded. A 180-day escalation queue that filters the Deductor-not-filed bucket by DATEDIF greater than 180 days and shows days-outstanding, days-since-last-TRACES-refresh, and forward interest exposure at one-and-a-half per cent per month under Section 201(1A). A deductor priority list with unique deductor PAN, count of unmatched rows, total exposure in rupees, minimum days-outstanding across the deductor's unmatched rows, and a mail-merge-ready contact placeholder pulled from the deductor master. A Section 197 register that surfaces every certificate flagged inside the 180-day validity window. A reconciled TDS credit total that populates the recipient's Form 131 claim for the financial year.
TDS receivable is the working-capital line most Indian finance functions carry without a first-class aging discipline. It is the customer’s tax deduction sitting in the tax authority’s ledger, waiting for a quarterly TRACES filing cycle to reflect it back to the recipient, and for the recipient’s book credit to reconcile against the ledger entry. Get the discipline right and it is a two-quarter working-capital cycle that closes cleanly against every Form 131 filing. Get it wrong — miss a Section 197 certificate lapse, miss a deductor’s short-deduction, miss a cross-era mapping between the old Section 194 references and the new Section 393 payment codes — and it is a line item that ages silently for eighteen months before the tax function realises the recovery window has closed. This is the recipe for the working Excel workbook that runs the TDS receivable aging workbook Excel Form 168 India discipline as a first-class monthly output rather than a quarterly scramble.
The workbook pairs with the monthly and quarterly TDS reconciliation runbook — the runbook covers the operational sequence, and this recipe covers the working paper the sequence produces. On the design side, it is the operational counterpart to the TDS Form 26AS reconciliation failure modes catalogue, which documents the specific failure classes this workbook is engineered to detect. On the bank-side, the bank reconciliation runbook Days 1 to 5 hands off to this workbook on Day 3, when TDS-net receipts on the bank side pre-populate the receivable-side aging queue this recipe operationalises.
What changed in April 2026
Two things shifted in the April 2026 direct-tax administration cycle that every TDS receivable workbook has had to absorb.
First, Form 168 replaced Form 26AS as the consolidated tax credit statement for every PAN holder from Assessment Year 2027-28 (Financial Year 2026-27) onwards. The underlying data is unchanged — TDS credits, TCS credits, advance tax, self-assessment tax, refunds all continue to reflect — but the TDS credit table restructures around the new payment codes 1001 through 1092 rather than the earlier free-form section references. A receivable ledger that historically keyed against 194C or 194Q now has to key against 1002 and 1031 to align with Form 168.
Second, Form 131 remained the annual TDS certificate that the recipient files as the summary claim, and Form 16A remained the quarterly non-salary certificate the deductor issues. The reconciliation key that ties the recipient’s books to the tax authority’s records, however, shifted from the section reference to the four-digit payment code. The cross-era mapping between the old sections and the new codes is now a permanent lookup table every Indian AR-tax working paper carries.
The receivable-side workbook therefore has to run a two-key match: the old section reference where the invoice was booked in FY 2025-26 or earlier, and the new payment code where the deductor has filed under the FY 2026-27 regime. A single-key workbook will miss half the reconciliation surface.
The four inputs
The workbook has four input sheets. Every input is copy-pasted from a portal or ERP download; no scraping, no macro, no add-in.
Input 1 — Receivable ledger from the ERP
Extract the TDS receivable ledger for the reconciliation window. The columns the workbook needs are invoice number, invoice date, deductor PAN, deductor TAN, recipient PAN, invoice value, applicable section reference (194C, 194J, 194H, 194Q, or 194O for the common cases), applicable payment code (1002, 1005, 1015, 1031, 1011 respectively), TDS amount at the applicable rate, Section 197 certificate number where the invoice was billed against a low-deduction certificate, and remarks. Every major ERP running in India — Tally, SAP, Oracle Fusion, Zoho Books, Busy, D365, Odoo — produces this extract from the standard AR-tax report; the TDS reconciliation software page covers the export mechanics per system.
Input 2 — Form 168 download from the TRACES portal
Log in to the TRACES portal, navigate to the Form 168 view for the recipient PAN, and download the TDS credit table for the reconciliation window. The download carries, for every TDS credit reported against the recipient PAN, the deductor TAN, the deductor name, the payment code, the transaction date, the transaction amount, the tax deducted amount, the deposit date on the deductor’s TRACES filing, the challan identification number (the seven-digit CIN concatenated with the BSR code and the deposit date), the deposit challan status, and the TRACES quarter in which the entry was filed. The Form 168 explanatory article walks through the download mechanics and the field-by-field mapping from the erstwhile Form 26AS view.
Input 3 — Cross-era mapping table
A static two-column sheet that lists the section-reference to payment-code correspondence for the common TDS classes on the AR side. At a minimum:
- Section 194C — payment code
1002— payments to contractors and sub-contractors - Section 194J — payment code
1005— professional and technical services - Section 194H — payment code
1015— commission and brokerage - Section 194Q — payment code
1031— purchase of goods above ₹50 lakh threshold - Section 194O — payment code
1011— e-commerce operator withholding at 1%
The full mapping across all payment codes 1001 through 1092 is on the TDS payment codes reference page. The driver sheet references this table via XLOOKUP so the workbook works whether the receivable row was booked under the old section reference or the new payment code.
Input 4 — Section 197 certificate register
A supplier-master-style sheet keyed on the deductor’s PAN carrying every open Section 197 low-deduction certificate the recipient has secured, with the certificate number, issue date, validity end date, aggregate cap in rupees, applicable section, cumulative payments received against the certificate to date, and the Assessing Officer’s identifier. This is the register the 180-day escalation queue reads against.
The composite key and the two-key match logic
The workbook’s driver sheet is a copy of the receivable ledger with additional computed columns. The match runs on a four-part composite key against Form 168.
- Deductor PAN and TAN. The PAN is the ten-character alphanumeric string; the TAN is the ten-character alphanumeric TDS identifier. Both are upper-cased and stripped of whitespace via
=UPPER(TRIM(A2)). Form 168 downloads carry the TAN; the receivable ledger typically carries both. The match keys on TAN because a single PAN may hold multiple TANs across branches. - Section-code-or-payment-code lookup. The formula reads whichever key is populated on the receivable row. Where the row carries only the old section reference,
=XLOOKUP(section_ref, cross_era_section_col, cross_era_code_col, section_ref, 0)returns the payment code; where the row carries only the new payment code, the same formula returns the code unchanged (theif_not_foundargument returns the original value). The Form 168 side always carries the payment code. - Transaction date, standardised. Excel serial date via
=DATEVALUE()where the source is a text date, or=DATE(2000+MID(C2,7,2),MID(C2,4,2),LEFT(C2,2))where the format is add/mm/yyyystring with a two-digit year. - Transaction amount within a five-hundred-rupee tolerance. The Form 168 transaction amount is the gross invoice value the deductor reported for tax computation; small rounding differences arise from the deductor’s own book adjustments.
=ABS(pr_amount - f168_amount) <= tolerance_cellreturns TRUE for a within-tolerance match. The default tolerance is ₹500; adjust the configuration cell for higher-value or lower-value receivable ledgers.
The driver row runs XLOOKUP with the four-argument exact-match syntax against the Form 168 sheet keyed on the concatenated composite key of TAN, payment code, and rounded transaction date. Wrap the extraction in a LET binding to keep the formula readable across the sheet, and point the lookups at named ranges rather than volatile cell references so the workbook stays portable when the deductor list grows.
The four categorisation buckets
The driver sheet’s Bucket column is a nested IFS() (or SWITCH()) formula that reads the composite-key match result, the Form 168 tax-deducted amount versus the receivable ledger TDS amount, and the Section 197 register lookup to assign the row to one of four buckets.
Bucket 1 — Matched
The row is in the receivable ledger, in Form 168 on the composite key with amount within the ₹500 tolerance, and the tax-deducted amount reconciles within a paise tolerance. This is the population that flows to the annual Form 131 claim. Colour-band it green. Expect between 60 and 78 per cent of the receivable count to land here for a team on a stable deductor base and a disciplined quarterly refresh.
Bucket 2 — Deductor not filed
The row is in the receivable ledger but there is no matching Form 168 entry for the tax period. The most common cause is that the deductor has not yet filed the applicable quarterly TRACES statement — the TDS quarter for the invoice date has not passed the filing due date, or the deductor is running late on the quarterly filing. Colour-band it amber. This row moves to the 180-day escalation queue, and if the aging crosses the 180-day threshold, it enters the deductor priority list for active follow-up. The TDS credit recovery mechanisms article documents the recovery pathways where the deductor fails to file in a subsequent cycle.
Bucket 3 — Reduced in Form 168
The row matches Form 168 on the composite key but the tax-deducted amount is lower than the receivable ledger TDS amount. The deductor has applied a lower rate — either the applicable section rate was reduced by a Section 197 certificate the recipient did not track, or the deductor mis-applied a higher rate initially and revised downward in a subsequent TRACES correction, or the deductor mis-computed the TDS at deposit. Colour-band it orange. The reviewer investigates on the same day to determine whether the reduction is legitimate (in which case the receivable ledger is corrected) or a Section 200A demand notice will follow (in which case the demand notice reconciliation workflow picks up the row).
Bucket 4 — 197-certificate-lapsed
The receivable ledger row was billed against an open Section 197 certificate, but the certificate’s validity end date has passed or its aggregate cap has been exceeded by the time of billing. Colour-band it red. Two consequences follow: the deductor should have reverted to the standard-rate deduction, so the tax-deducted amount in Form 168 will be lower than the certificate implied, and the recipient’s book TDS accrual needs to correct upward. This is a High-Action-Priority failure mode on the design side and requires immediate escalation to the tax manager for a fresh Section 197 application under Section 197 of the Income-tax Act.
The four buckets are mutually exclusive and every row lands in exactly one. The bucket assignment carries into the aging queue and the priority list without further categorisation logic.
The aging bucket structure
TDS receivable ages against the invoice date, not against the deposit date on the deductor’s side. The workbook computes a Days-Outstanding column as =DATEDIF(invoice_date, TODAY(), "d") and buckets the result into four aging bands.
- 0 to 60 days remaining. Informational band. The deductor has not yet had a full quarterly TRACES filing cycle to report the credit. The credit is expected to appear in the next Form 168 refresh.
- 61 to 120 days. First watch band. One quarterly cycle has passed without the credit landing. Standard deductor follow-up letter goes out under the tax executive’s name.
- 121 to 180 days. Second watch band. Two quarterly cycles have passed. The escalation moves to the controller’s monthly review, and the deductor priority list picks the row up for a second follow-up.
- Above 180 days. Escalation band. Three quarterly cycles have passed without the credit landing in Form 168. The controller triggers either a formal recovery letter to the deductor, an application for Section 197 provisional certificate to protect subsequent billings, or a write-off provisioning decision if the deductor is uncontactable.
The 180-day threshold matches the practitioner rule that a TDS credit not visible in Form 168 three quarterly cycles after the invoice date is a receivable that will require active recovery rather than passive tracking. It also matches the practical runway needed to file a fresh Section 197 provisional certificate application, secure the Assessing Officer’s approval, and issue the new certificate to the deductor before the aging crystallises into a write-off.
A rupee-value summary at the top of the aging sheet totals the exposure per band, so the finance manager sees “₹8,42,000 above 180 days” as the mandatory attention line before opening the underlying rows.
The 180-day escalation queue against Section 197
The Section 197 register is a second computed sheet that scans the Section 197 input register and returns every open certificate whose remaining runway is inside the 180-day window.
A computed column returns days remaining as the smaller of =validity_end - TODAY() and =(aggregate_cap - cumulative_paid) / avg_monthly_billing, where avg_monthly_billing is derived from the trailing three months of receivable ledger rows against the deductor’s PAN under the same certificate. Whichever of the two hits 180 days first triggers the flag.
Every flagged certificate enters the tax executive’s escalation queue for the next quarterly TRACES review cycle. The forward interest exposure — computed at 1.5 per cent per month under Section 201(1A) on the differential between the certificate rate and the standard rate — is displayed alongside so the priority is by interest at risk rather than merely by principal.
The deductor priority list
The deductor priority list is a third computed sheet. It runs =UNIQUE(FILTER(pan_column, bucket_column="Deductor not filed")) to produce one row per delinquent deductor, then runs COUNTIFS and SUMIFS to compute per-deductor metrics: count of unmatched rows, total exposure in rupees, at-risk TDS in rupees, earliest invoice date, days since that earliest invoice, and the maximum days-outstanding across the deductor’s unmatched rows. A final column left-joins the deductor master via XLOOKUP on PAN to pull the contact email and phone into a mail-merge-ready column.
=SORT(deductor_list, exposure_column, -1) sorts the list by descending exposure so the deductor with the largest at-risk TDS surfaces at the top. =FILTER(sorted_list, max_days_out_column > 120) restricts the working view to the deductors whose oldest unmatched row has passed the second watch band. The finance team exports the filtered list to CSV and mail-merges into the standard TDS recovery letter template.
The recovery letter is a fortnightly rhythm on the twenty-day close cadence; anything more frequent is best served by continuously-refreshed reconciliation infrastructure.
The formulas at practitioner level
Every formula the workbook uses is standard Excel — no add-in, no Power Query, no macro, and every function is available in Microsoft 365 or Excel 2019 and later.
- TAN normalisation.
=TRIM(UPPER(A2))on the raw TAN column. Where the TAN carries a branch suffix the deductor added,=TEXTBEFORE(A2,"/")strips the suffix before the match; the residual raw value is retained in a shadow column for audit. - Cross-era section-to-code lookup.
=IFERROR(XLOOKUP(section_ref, era_section_col, era_code_col, "", 0), payment_code_col_original)returns the mapped code where the section reference is populated, or the original payment code where the row was booked under the new regime. TheIFERRORwrapper handles the mixed-population case. - Composite key concatenation.
=deductor_tan & "|" & payment_code & "|" & TEXT(txn_date,"yyyymmdd"). The pipe delimiter is chosen because it does not appear inside any legitimate TAN or payment code. - Composite-key XLOOKUP into Form 168.
=XLOOKUP(recv_composite_key, f168_composite_key_range, f168_tax_deducted_range, "", 0). Exact match,#N/A-safe return of empty string. - Tolerance amount match.
=IF(f168_match_value="", FALSE, ABS(ROUND(recv_amount,0) - ROUND(f168_match_value,0)) <= tolerance_cell). - Aging computation.
=DATEDIF(invoice_date, TODAY(), "d"). The"d"interval returns days between the two dates. - Bucket assignment. A single
IFS()walks the four flags (in Form 168 on composite key, tax-deducted amount matches, Section 197 certificate valid on invoice date, tax-deducted amount lower than expected) and returns one of the four bucket labels. - Days-remaining on Section 197.
=MIN(validity_end - TODAY(), (aggregate_cap - cumulative_paid) / avg_monthly_billing). The minimum of the two runway measures triggers the flag when it crosses 180. - Deductor priority sort.
=SORT(FILTER(deductor_list, max_days_out > 120), exposure_col, -1). Sorted descending by exposure, filtered to deductors past the second watch band.
Every formula uses LET where the intermediate values are referenced more than once, and every named range is defined via the Name Manager so the workbook is portable to a fresh installation without cell-reference drift.
The workbook download
The downloadable Excel workbook — with the four input sheets pre-structured, the driver formulas wired against named ranges, the cross-era mapping table pre-populated with the common section-to-code mappings, and a deductor master template — is on the TDS receivable aging workbook resource page. The download is soft-gated behind a short qualifying form; the workbook itself carries illustrative data only, and every sheet is watermarked.
For teams that want to see the recipe run without a download, the Controller’s Toolkit resource page bundles this workbook with the other Playbook working papers as a single reference pack.
When the manual workbook tops out
The workbook is engineered for a finance team running its own TDS receivable reconciliation manually. It holds up cleanly below roughly 150 active deductors on a single PAN with a monthly receivable count under approximately 1,200 rows. Above those thresholds — or on multi-PAN groups with more than three PANs, or in aggregator-heavy revenue models where a single aggregator batch TAN mixes hundreds of underlying sellers — the two-key match against Form 168 refreshes needs to happen weekly rather than quarterly, and the Section 197 register requires active daily monitoring rather than a monthly review.
This is where the aging queue and the Section 197 escalation queue become first-class outputs of continuously-refreshed reconciliation infrastructure such as TransactIG, rather than a quarterly Excel refresh. The manual workbook keeps its role as the discipline the system runs against, not the process the finance team runs by hand.
Where this fits
- Reconciliation software India — pillar guide
- TDS reconciliation software
- GST reconciliation software India
- The Reconciliation Playbook — monthly close pillar
- TDS reconciliation runbook — monthly and quarterly cadence
Related reading
- TDS Form 26AS reconciliation failure modes — the design layer
- Form 168 — the new TDS statement from April 2026
- Form 131 — annual TDS certificate
- TDS payment codes 1001-1092
- Cross-era TDS reconciliation — old sections to new codes
- TDS demand notice reconciliation
- TDS correction statement — March 2026 deadline
- TDS credit recovery mechanisms
- Controller’s Toolkit — bundled Playbook working papers
Content authored by practitioners with experience at Amazon India, Intuit QuickBooks, and the Tata Group. Meet the team →
- ▸ Section 194Q, Income-tax Act 1961 — Tax deduction at source on payment for purchase of goods. A buyer whose total turnover, gross receipts, or sales from business exceeds ten crore rupees during the financial year immediately preceding the year of purchase shall deduct tax at 0.1 per cent on the value of purchase exceeding fifty lakh rupees in aggregate from a resident seller in a financial year. The deduction is timed at the earlier of credit to the seller's account or payment to the seller, and reported under the new Section 393 payment code 1031 from FY 2026-27 onwards, replacing the earlier reporting under Section 194Q against a bare section reference. This is the largest single volume driver of TDS receivable rows for manufacturing buyers, and the aging workbook keys against the fifty-lakh threshold row-by-row to identify the point at which the buyer's obligation crystallised.
- ▸ Section 197, Income-tax Act 1961 — Low deduction certificate. Where the total income of the recipient justifies a lower rate of deduction than the standard rate under the applicable TDS section, the Assessing Officer may issue a certificate authorising deduction at a lower rate or no deduction. The certificate carries a validity period and an aggregate payment cap. Once the aggregate paid against a Section 197 certificate crosses either the value cap or the validity date, the deductor reverts to the standard rate. The aging workbook computes the days remaining on every open Section 197 certificate and flags the 180-day-from-issue window as the escalation trigger for a fresh application or a provisional extension request under CBDT circular guidance.
- ▸ Section 201(1A), Income-tax Act 1961 — Interest on failure to deduct or deposit TDS. Where a person responsible for deducting tax fails to deduct or, after deduction, fails to pay the whole or any part of the tax as required, that person shall be liable to pay simple interest at one per cent per month or part thereof from the date on which the tax was deductible to the date on which the tax is deducted, and at one and a half per cent per month or part thereof from the date on which the tax was deducted to the date on which it is actually paid. The one-and-a-half-per-cent leg is the interest rate that runs on late-deposit demand notices under Section 200A, and the aging workbook's escalation columns compute forward interest exposure so the deductor priority list is sorted by interest at risk rather than merely by principal.
- ▸ Section 200A, Income-tax Act 1961 — Processing of TDS statements. Any TDS statement filed under Section 200 shall be processed by the tax authority through an automated system that computes any short deduction, short payment, or interest on late payment. Where the processing produces a demand, an intimation is issued to the deductor within one year from the end of the financial year in which the statement was filed. The receivable-side aging workbook cross-references demand notices raised on the deductor to the deductor's Form 168 filings for the recipient — a demand raised on the deductor for short-deduction of tax against a specific PAN is the first signal that the recipient's TDS credit for that PAN may be reduced in a revised Form 168.
- ▸ Form 168 notification, Central Board of Direct Taxes effective April 2026 — Form 168 replaces the erstwhile Form 26AS as the consolidated tax credit statement for every PAN holder from Assessment Year 2027-28 (Financial Year 2026-27) onwards. Form 168 carries the same underlying data as Form 26AS — TDS credits reported by deductors, TCS credits, advance tax and self-assessment tax payments, refunds — but restructures the TDS credit table around the new Section 393 payment codes 1001 through 1092 rather than the earlier free-form section references. Form 131 remains the annual TDS certificate issued by the deductor and Form 16A remains the quarterly non-salary certificate, but the reconciliation key that ties the recipient's books to the tax authority's records shifts from the section reference to the four-digit payment code. Every aging workbook built for FY 2026-27 onwards must ingest the new payment code columns and carry a cross-era mapping table for reconciliation against invoices booked under the old regime that continue to age past the transition date.