Reconcile a Customer Payment Advice in India: TDS, GST, Excel

A large customer pays you, and the amount that lands in the bank is short of what you invoiced. Attached is a payment advice listing thirty, fifty, a hundred invoices, each one paid net of a column of deductions: a bit of TDS here, a GST-TDS line there, a damage claim, a scheme deduction, a rate difference. Your sales ledger shows the gross. The bank shows the net. The gap between them is real money, and right now you cannot say, invoice by invoice, what it is made of.

To reconcile a customer payment advice in India, you extract every invoice and its deductions into one row per invoice, then match gross invoice value against the net amount actually paid. Once the document is in that shape, the gap stops being a single alarming number and becomes a list of specific deductions, each one of which has a destination: a tax credit you reclaim, a debit note you accept, or a deduction you dispute.

The single most useful thing to understand before you start is that the gap is mostly recoverable. The deductions on the advice fall into two families. The statutory ones are reclaimable, not lost. Income-tax TDS, commonly 0.1% under Section 194Q, comes back to you as advance-tax credit against your own income-tax liability. GST-TDS, 2% under Section 51 where the customer is a government or notified deductor, is credited to you and used to pay output tax. Only the commercial deductions, damage, shortage, scheme and rate differences, listing charges, returns, are genuinely off your books, and even those you accept only against a supporting document or dispute where none exists.

The reason the shortfall looks worse than it is comes down to a single accounting habit. A supplier sees a shortfall and books it as a loss, or treats it as a customer error, unless the statutory withholdings are accounted for separately. Failing to account for TDS as a prepaid credit rather than a deduction from revenue is the most common cause of reconciliation differences for Indian suppliers. The money was never gone; it was deposited against your PAN and is sitting where you have not yet looked.

This is the receivables side of the document, the deductee's view. You are not reconciling what you owe a vendor; you are decoding what a customer withheld from you and deciding, line by line, how to get it back. Everything below works through that document, what a payment advice carries (if you want the general anatomy first, see what a remittance advice shows), how to get it into a spreadsheet you can reconcile, and how to route each deduction to its claim or its dispute.

---

Decode Every Deduction Line: Statutory vs Commercial

Every deduction on a customer's payment advice belongs to one of two families, and which family a line falls into decides how you recover it. Statutory deductions are withholdings the customer is legally required to make and deposit on your behalf, so they come back to you as a credit. Commercial deductions are the customer reducing what it pays you for a trade reason, so they stay reduced unless you can show the reason is wrong. Sorting each line into the right family is the first analytical step, and it is what lets you decode a retailer's payment advice instead of staring at an unexplained net figure.

Income-tax TDS. When a customer's turnover in the preceding financial year exceeds the prescribed limit and its purchases from you cross the per-seller threshold, it withholds income-tax TDS on your invoices under Section 194Q, commonly at 0.1% of the value above that threshold. This is the same transaction you see on your advice as a small percentage deduction, viewed from the buyer's chair: the customer is the deductor, you are the deductee. Understanding how Section 194Q applies to your customer's purchase invoices tells you exactly what your customer is obliged to deduct and deposit, which is what you will later match against your own tax records. Section 194Q thresholds and the rate are statutory figures that change; confirm the current numbers against an authoritative source rather than relying on a remembered rate, and check the rate applied to your line against the India TDS rate chart for invoice payments.

GST-TDS. Where your customer is a government department, a public-sector undertaking, or another notified entity, it withholds GST-TDS at 2% under Section 51 on contracts above the prescribed value. On the advice this shows as a separate 2% line, distinct from the income-tax TDS, and it is reclaimable through a different channel than income-tax TDS. For now the task is only to recognise it as a statutory line that you will trace to a credit, not a charge against your sales.

Commercial and trade deductions. Everything the customer takes for a business reason sits in the second family. A modern-trade retailer or large distributor typically deducts for damage and shortage on receipt, scheme and rate-difference adjustments, listing or visibility charges, and returns. These reduce what you are paid commercially, and there is no tax authority to reclaim them from. Each one is valid only if it is supported by a document you can verify, and the genuine ones become a permanent reduction in revenue while the unsupported ones become a dispute.

The practical way to tell the families apart on sight is to read what each line references. A statutory deduction points to a tax section, a TDS certificate, or a GSTIN-linked deduction entry. A commercial deduction points to a claim number, a debit note, a scheme code, or a damage report. Tag every line on the advice as statutory or commercial before you do anything else, because the two halves of the gap behave in opposite ways: one you reclaim, the other you contest.

Turn the Payment Advice Into One Row Per Invoice in Excel

The layout that makes a payment advice reconcilable is one row per invoice with a column for every deduction type. At minimum: invoice number, invoice date, gross invoice value, then a separate column for income-tax TDS, GST-TDS, damage and shortage, scheme and rate-difference, listing, and returns, followed by net amount paid and a reason or reference field. The discipline that matters is the separate column per deduction type. When TDS sits in its own column rather than lumped into a single deductions figure, the statutory-versus-commercial split you tagged earlier becomes visible at a glance, and every column foots to a number you can act on.

That structure earns its keep the moment you start reconciling. Sum the two statutory columns and you have the total sitting as reclaimable credit. Sum the commercial columns and you have the amount to test against debit notes. The net-paid column should tie back to what actually hit your bank, and the gross column should match your sales ledger invoice by invoice. A single table, built once per advice, isolates the reclaimable total from the disputable total and ties both ends to records you already hold.

Getting there from the document you received is the work most suppliers do by hand, and it is the step where the time goes. Payment advices arrive as PDFs, scanned copies, emailed statements, or vendor-portal exports, often with dozens of invoices concatenated into one file and the deduction breakup buried in sub-columns or footnotes. Re-keying that into a spreadsheet is slow and error-prone, and an error in the deduction columns is exactly the kind of mistake that reopens the reconciliation later.

This is where you can extract a multi-invoice payment advice into structured Excel instead of transcribing it. The same approach finance teams use for extracting vendor statement data into Excel on the AP side applies in reverse here on the receivables side: a document listing many line items in a summary table becomes one structured row per item. Our tool reads a multi-invoice payment advice, including one where several invoices are concatenated in a single PDF, and produces a row per invoice with the columns you define, and each row carries a reference back to its source file and page so you can check any figure against the original. You describe the output in a prompt rather than configuring a template. A workable prompt for this document looks like:

I'm reconciling a customer's payment advice. Each invoice listed in the statement should be a separate row.

Extract, with one row per invoice:

• Invoice Number
• Invoice Date (YYYY-MM-DD)
• Gross Invoice Value
• Income-tax TDS (Section 194Q deduction; if not present use 0)
• GST-TDS (Section 51 deduction; if not present use 0)
• Damage/Shortage Deduction (if not present use 0)
• Scheme/Rate-Difference Deduction (if not present use 0)
• Listing/Other Deduction (if not present use 0)
• Net Amount Paid
• Deduction Reason / Reference (any claim number, debit note, or note shown against the line)

Keep all amounts as numbers to two decimal places.

Adjust the deduction columns to match the labels your particular customer uses, since a retailer's scheme deductions and a PSU's GST-TDS lines are named differently. The output is a clean per-invoice table, the same shape whether the advice held ten invoices or a thousand, ready to reconcile against your books.

---

Reclaim the Statutory Deductions: TDS and GST-TDS

With the statutory columns totalled, the next job is to turn each withheld amount into a traced credit. Neither family is a reduction in your revenue. The income-tax TDS column matches to a credit in your tax records; the GST-TDS column matches to a credit in your GST ledger. Reconciling the TDS deducted by your customer is a matching exercise against records that already exist, not a recovery of lost money.

For income-tax TDS, the customer deposits what it withheld under Section 194Q against your PAN. It appears in your Form 26AS and is reported through the deductor's quarterly 26Q return. You match each TDS line on the advice to its 26AS entry and treat the total as advance-tax credit set against your own income-tax liability for the year. The point to hold onto is that the gross invoice value remains the income you recognise. The tax was simply prepaid on your behalf, so the 0.1% that left your bank receipt is money you have already paid toward a liability you would have owed anyway. An unmatched TDS line is not a loss; it is a credit not yet appearing in 26AS, and the action is to chase the customer's deposit and return filing.

GST-TDS follows a separate channel. According to the Karnataka Commercial Taxes Department's FAQs on TDS under GST, GST-TDS is credited to the supplier's electronic cash ledger: it is deducted at 2% under Section 51 of the CGST Act, applies where the value of supply under a contract exceeds Rs. 2.5 lakh, and once credited can be used to pay your output tax. The sequence is worth knowing so you watch the right place: the deductor files GSTR-7, the credit flows through to you and shows in your GSTR-2A and GSTR-2B, and it then sits in your electronic cash ledger ready to offset output tax. Because the credit only appears once the deductor files, a GST-TDS line on the advice with no matching credit yet usually means the GSTR-7 has not been filed, which is a follow-up with the customer rather than a write-off. The mechanics of reconciling GST credit against GSTR-2B are the same statement you will read to confirm the GST-TDS landed.

GST TDS Section 51 reconciliation on the supplier side turns on the credit being attributed to the correct GSTIN, so it is worth confirming the GSTIN the customer deducted against is yours and is active. The same care that goes into verifying a supplier's GSTIN before claiming ITC applies to checking that a GST-TDS deduction has been booked against your registration and not a stale or incorrect one.

Both families come down to one check: every statutory line on the advice should tie to a record, a 26AS entry or a cash-ledger credit. When a line ties, you have your credit. When it does not, you have a filing to chase, not an amount to absorb.

Dispute the Trade Deductions and Close the Reconciliation

Commercial deductions are accepted only against proof. Each trade deduction on the advice must point to a document you can verify, usually a debit note the customer raised, a damage or shortage report, or a scheme and rate-difference agreement. A deduction with a valid supporting reference is a genuine reduction in what you are owed. A deduction with no reference, or one that cites a document you never received, is a candidate for dispute. Sorting the commercial column on this single test, supported or unsupported, is what separates the amount you concede from the amount you chase.

Each type resolves against its own evidence. Damage and shortage claims reconcile to proof of delivery and whatever claims process the trading agreement sets, so a shortage deducted on goods your POD shows were delivered in full is contestable. Scheme and rate-difference deductions check against the agreed trade terms and price lists; a rate difference applied at a rate you never agreed, or a scheme deduction for a scheme that had lapsed, does not hold. Listing and visibility charges check against the contract that authorised them. Returns should match credit notes you raised yourself, and a return deduction with no corresponding credit note on your side is one to query. The pattern that makes modern trade deduction reconciliation in India tractable is treating every commercial line as a claim to be substantiated rather than a number to be accepted.

The reconciliation closes when both halves are routed. The statutory total is matched to its credits in 26AS and the cash ledger. Every commercial deduction is either tied to a debit note you accept or flagged for dispute. At that point the net-paid figure ties to the bank receipt, the gross ties to your sales ledger, and the only remaining gap is the disputable amount you take back to the customer with the specific lines and references behind it. The unexplained shortfall you started with has become a known, line-itemised position: this much reclaimed as tax credit, this much conceded against valid claims, this much in dispute.

Because the customers that deduct at source are large and their documentation arrives piecemeal, supplier payment reconciliation in India is rarely a single event. The same per-invoice table and the same statutory-versus-commercial routing run against every advice, every cycle. Building it as a repeatable process, rather than a fresh scramble each time a short payment lands, is what keeps the reclaimable credits from quietly turning into written-off shortfalls.