Dunning Letter Generator (Multi-Stage)

The Problem

For a mid-market accounts receivable team managing 50–500 open invoices, the manual dunning process typically consumes two to three days of AR staff time each month. A collector must export the aged receivables register, sort invoices by days-past-due (dpd), draft tiered reminder letters in separate Word templates, hand-calculate accrued interest at the applicable rate, and then send each letter individually — all before the next invoice batch arrives. At 200 open invoices across 80 customers, that cycle rarely closes before the working-week ends.

The process is not optional: under Section 2(b) of the Limitation Act, 1963, a creditor's right to sue on a money debt is extinguished after three years from the date the debt became due. Consistent, timestamped dunning correspondence is the evidentiary record that courts and NCLT benches examine when a matter escalates to insolvency proceedings under the Insolvency and Bankruptcy Code, 2016. Without stage-differentiated letters — reminder, first notice, final demand, legal referral — a company's legal team has no paper trail to support recovery action.

Scale breaks the manual model in two predictable ways. First, interest calculations drift: staff apply a flat rate to the full invoice amount instead of the outstanding balance, or omit interest entirely on partially paid invoices, understating the recoverable amount. Second, data integrity errors in the source AR register — a missing contact email, an outstanding balance that exceeds the original invoice amount — go undetected and result in undeliverable letters or legally defective demand notices.

Why It Matters: Context

The four-stage dunning framework used in Indian commercial practice corresponds to the escalation logic many controllers encode informally in Excel: Stage 1 Reminder covers 1–15 dpd, Stage 2 First Notice covers 16–30 dpd, Stage 3 Final Demand covers 31–60 dpd, and Stage 4 Legal Referral triggers at 60+ dpd. The interest rate of 18% per annum is the rate most commonly specified in Indian commercial contracts and mirrors the rate applied by GST authorities under Section 50 of the CGST Act, 2017 for delayed tax payments — making it the de facto reference rate for overdue trade receivables in India.

Each dunning letter must reference the invoice number, GSTIN of the debtor, outstanding principal, and accrued interest to be legally complete. The debtor's GSTIN is particularly important: it ties the demand to a registered taxable entity, making it harder for a debtor to dispute identity. Under ICAI Guidance Note on Audit of Revenue from Contracts with Customers (Ind AS 115), auditors also expect the AR aging schedule used for ECL provisioning to be consistent with the dunning records — a mismatch between the aging register and dispatched letters is a common point of qualification during statutory audits.

For mid-market companies without a dedicated collections team, the practical failure mode is simple: letters are sent late or at the wrong escalation stage, interest is miscalculated, and records with bad data (no email, corrupted outstanding balances) are silently skipped. The consequence ranges from uncollected receivables ageing past the Limitation Act window to a qualified observation in the statutory audit report that the company's collections process lacks documented escalation controls.

What This Workflow Automates

1. The workflow ingests the AR aging register uploaded as an Excel file with columns for customer name, GSTIN, contact email, invoice number, invoice date, due date, invoice amount, amount outstanding, and currency.
2. Each invoice row is validated on four checks: contact email is present and non-empty (FAIL severity if missing), GSTIN conforms to the 15-character Indian format with 'Z' at position 14 (FAIL if malformed), amount outstanding is less than or equal to invoice amount (FAIL if outstanding exceeds invoice, e.g. Rs 42,000 outstanding against a Rs 35,000 invoice), and due date is not in the future (FAIL if due date is ahead of the run date).
3. Records that fail any FAIL-severity validation are routed to a named exception queue and excluded from letter generation until corrected in the source register.
4. For each record that passes validation, the workflow computes days-past-due as of the run date and assigns one of four dunning stages: Stage 1 Reminder (1–15 dpd), Stage 2 First Notice (16–30 dpd), Stage 3 Final Demand (31–60 dpd), or Stage 4 Legal Referral (60+ dpd).
5. Accrued interest is calculated at 18% per annum on the amount outstanding (not the full invoice amount), pro-rated for the exact number of days overdue — for example, Rs 95,000 outstanding for 22 days accrues Rs 1,030 at 18% p.a. under a 365-day convention.
6. A stage-appropriate dunning letter is generated per customer, referencing invoice number, GSTIN, outstanding principal, accrued interest, and total amount payable, with tone and legal language calibrated to the assigned stage.
7. The completed dunning run is surfaced in an inbox with a stage badge per customer and exported as an Excel file containing all records, their assigned stages, interest calculations, and exception flags.

All of this happens in under 90 seconds with deterministic outputs — stage assignment, interest figures, and validation statuses — that any controller can re-derive from the uploaded register and audit without re-running the workflow.

Edge Cases We Simulate

The workflow ships with a battery of synthetic test scenarios that exercise every failure mode we have seen in real-world data. Each scenario produces a deterministic outcome that an auditor or controller can verify in seconds.

Sample Documents

Download or inspect the seeded sample files used to demonstrate this workflow:

Sample Results

Across the five demo files included with this workflow, the extraction layer correctly parsed all structured fields in every case: invoice amounts, GSTINs, contact emails, and dates were captured without manual correction. The clean baseline case (ar_aging_clean.xlsx) for Bharat Heavy Electricals Ltd — invoice INV-2026-0142, Rs 2,85,000 outstanding, 10 dpd — passed all six validation checks including contact email present, GSTIN 29AAACB4146P1ZN structurally valid, outstanding equal to invoice amount, and due date in the past. The Stage 2 case (Featherlite Office Systems Pvt Ltd, INV-2026-0118) correctly extracted a partial payment scenario where invoice amount is Rs 1,42,500 and outstanding is Rs 95,000, allowing interest to be computed on the actual balance rather than the full invoice face value. The Stage 4 legal referral case (Coastal Logistics India Pvt Ltd, INV-2025-0987) correctly placed a 78-dpd, Rs 67,500 full-balance invoice into the legal-referral tier.

The exception queue captured two distinct failure classes in the demo set. The missing contact email case (Sunrise Traders, INV-2026-0156, 35 dpd) triggered the Contact Email Required FAIL validation, blocking letter generation and surfacing the record in the exception queue — the most common silent failure in manual dunning runs where staff skip records with incomplete data without logging them. More critically, the data integrity case (Modern Hardware Co, INV-2026-0161) where amount outstanding of Rs 42,000 exceeded invoice amount of Rs 35,000 was flagged before any letter was generated, preventing a legally defective demand notice from being issued on a corrupted source record.

Why Automation Wins Here

Compared to a manual dunning cycle that typically requires two to three days of AR staff time, this workflow reduces the time from register upload to dispatched, interest-calculated, stage-appropriate letters to under 90 seconds. It eliminates the two most common calculation errors — interest applied to the full invoice amount rather than the outstanding balance, and uniform letter tone regardless of escalation stage — and enforces four hard data-quality gates before any letter is generated. For a 200-invoice AR book, that means zero misfiled letters and a complete, recomputable interest ledger for every dunning run.

Every output produced by the workflow — the stage-assignment log, interest calculation detail, validation pass/fail results, exception queue entries, and the Excel export of the full dunning run — is time-stamped and structured for direct inclusion in the AR section of the month-end audit workpaper package. Controllers can attach the exception queue report as evidence that incomplete records were identified and quarantined, satisfying the escalation-control documentation requirement that statutory auditors and internal audit teams check against the Limitation Act and IBC evidentiary standard.

Frequently Asked Questions