Trade Receivables Ageing under Schedule III: A Worked Excel Example

Scope: Schedule III Division I company reporting; worked reporting date 31 March 2026. Verification date: 11 October 2026.

Why customer balances are not enough

One customer can have several invoices with different due dates, a credit note awaiting allocation and an advance for a new order. Ageing the net customer balance from the oldest invoice can produce a misleading disclosure. Start with the outstanding invoice allocation, then reconcile it to the general ledger.

Division I distinguishes undisputed/disputed balances and amounts considered good/doubtful. Its overdue ageing bands are less than six months, six months to one year, one to two years, two to three years and more than three years. Where no due date is specified, use the transaction date. Disclose unbilled dues separately.

Collect the right columns

A useful Excel working contains customer code, invoice number, invoice date, contractual due date, original amount, allocated receipts, credit-note adjustments, balance outstanding, dispute status and recoverability assessment. Keep the source record and management explanation for each disputed item.

Set the reporting date in a single fixed cell, for example B1. Create an “ageing base date” using the due date where available and the invoice date otherwise. Create a separate flag for amounts not yet due. Do not move future-due invoices into overdue buckets just to make the table total match the ledger.

A small worked example

The following fictional invoices remain outstanding at 31 March 2026. Values are shown in rupees, and the example assumes the stated due dates are supported by the contract.

Total invoice receivables are ₹2,80,000: overdue ₹2,40,000 plus not yet due ₹40,000. If ₹20,000 of unbilled income is also included in the relevant receivable balance, the reconciliation becomes ₹3,00,000, with the unbilled component identified separately. Confirm the ledger composition before using this bridge.

Use calendar boundaries consistently

For an Excel implementation, compare dates with EDATE($B$1,-6), EDATE($B$1,-12), EDATE($B$1,-24) and EDATE($B$1,-36). Document which side includes an exact boundary. For example, a due date exactly six months earlier belongs in the six-month-to-one-year band under a convention where “less than six months” excludes that boundary.

Test the formula using dates exactly on each boundary, one day before and one day after. Use the same logic for all customers. A rough number-of-days formula can be a useful screening tool, but should not silently replace a defined calendar-month convention.

Review before generating the note

• Resolve duplicate invoice numbers using customer identity.
• Allocate receipts and credit notes before ageing.
• Investigate advances and negative invoice balances.
• Document disputed status and recoverability independently.
• Reconcile overdue, not-yet-due and unbilled amounts to the appropriate gross balance and show allowances consistently.

A Schedule 3 financial builder can help present the note, but it cannot create reliable contractual dates from an unsupported balance. For assureOffice preparation, retain the reviewed ageing working and compare its total and classifications with the generated note before export.

Test the spreadsheet before using the full population

Build a small test set containing an invoice due on the reporting date, an invoice not yet due, an item on each band boundary and an item with no contractual due date. Check the expected band manually before applying the formula to all rows. Store dates as real Excel dates rather than text imported from a report.

Give partial receipts and credit notes their own review. Age the remaining outstanding amount using the applicable invoice information; do not reset the age merely because a payment occurred. Retain unmatched receipts separately until their allocation is supported. These controls improve both the total and the distribution.

Related articles

• Trade Payables Ageing: Building the Working and Checking MSME Classification
• Excel Power Query for Accountants: Reconcile Two Registers without Repeated Copy-paste
• assureOffice Financials: Checks after Import and before Export

Sources and references

• Schedule III, Companies Act, 2013 — India Code
• ICAI Guidance Note on Division I — Non Ind AS Schedule III