In this guide · 10 sections
- Debtors ageing format under Schedule III: the receivables grid
- Creditors ageing schedule format: the payables grid
- Step 1: get a bill-wise export out of Tally
- Step 2: the ageing schedule format in Excel — columns and bucket formulas
- Step 3: fill the grid with SUMIFS, or a pivot
- Worked example: from bills to the note
- When the client does not keep bill-wise details
- Checks before the grid goes into the note
- FAQs
- Sources
- The Schedule III ageing tables as copyable Excel grids: receivables with Unbilled, Not due and five buckets (less than 6 months to more than 3 years); payables with Unbilled, Not due and four buckets (less than 1 year to more than 3 years).
- Start from bill-wise data, not the trial balance: a Tally outstandings export with bill date, due date and pending amount, plus three flag columns you add — disputed, doubtful (or MSME) and unbilled.
- Two formulas do the work: DATEDIF for completed months past the due date, and a nested IF that turns the months into the Schedule III bucket. A SUMIFS grid (or a pivot) then gives the note.
- No bill-wise records? A common practice is to age the closing balance first-in, first-out against the latest invoices — a practical method, not one Schedule III prescribes, so record it.
- Before it goes into the note: the grid total ties to the trade receivables / payables note and the TB, the doubtful rows to the allowance, and the MSME rows to the MSME split on the face.
Try SignReady: Fill the receivables and payables ageing from the Tally report — it goes straight into the draft notes.
Start freeEvery company file now needs two ageing schedule formats in Excel: one for trade receivables and one for trade payables, for the current year and the previous year. Schedule III prescribes the tables, but not how to get there — and the usual route in a small firm is a Tally outstandings export, a few helper columns and a grid that must agree with the note. This article gives both grids ready to copy, the exact columns and formulas to build them from a bill-wise export, a worked example, the first-in, first-out workaround where the client does not keep bill-wise details, and the checks to run before the table goes into the notes. For what the rows and buckets mean, and the judgements behind "disputed" and "due date", read our article on trade receivables and payables ageing under Schedule III; this one is the spreadsheet how-to.
Debtors ageing format under Schedule III: the receivables grid
Division I of Schedule III (as amended by G.S.R. 207(E) from 1 April 2021) prescribes four rows and five buckets, "outstanding for following periods from due date of payment", and says unbilled dues are disclosed separately. ICAI's Guidance Note on Division I (Revised January 2022) adds two columns before the buckets — Unbilled and Not due — so that the total agrees with the note. That gives seven amount columns plus Total. Copy this into Excel as it stands (Amount in ₹, or your rounding unit):
| Particulars | Unbilled | Not due | Less than 6 months | 6 months – 1 year | 1–2 years | 2–3 years | More than 3 years | Total |
|---|---|---|---|---|---|---|---|---|
| (i) Undisputed Trade Receivables – considered good | ||||||||
| (ii) Undisputed Trade Receivables – considered doubtful | ||||||||
| (iii) Disputed Trade Receivables – considered good | ||||||||
| (iv) Disputed Trade Receivables – considered doubtful | ||||||||
| Total |
The row headings are the Division I wording. A grid with "significant increase in credit risk" or "credit impaired" rows is the Division II (Ind AS) format and does not belong in a non-Ind AS company's notes. If the company has long-term trade receivables (under Other non-current assets), they get a separate grid in the same shape.
Creditors ageing schedule format: the payables grid
Trade payables have a different shape — no considered good / doubtful split, and four buckets starting at "less than 1 year". The Guidance Note again adds Unbilled and Not due, and clarifies that rows (i) and (ii) carry only undisputed dues, and that a provision is not an unbilled trade payable.
| Particulars | Unbilled | Not due | Less than 1 year | 1–2 years | 2–3 years | More than 3 years | Total |
|---|---|---|---|---|---|---|---|
| (i) MSME | |||||||
| (ii) Others | |||||||
| (iii) Disputed dues – MSME | |||||||
| (iv) Disputed dues – Others | |||||||
| Total |
Put the previous-year grid directly below each table, in the same columns. The Guidance Note says comparatives are prepared on the same lines as the current year, so last year's ageing is needed as at last year's balance sheet date — not this year's ageing with last year's totals.
SignReady generates the Schedule III balance sheet, Statement of Profit and Loss and notes from your trial balances as an editable Excel workbook. Section 5 of the draft takes the receivables and payables ageing in the Schedule III buckets — fill it from the Tally ageing report or type it in. First 3 finalisations free.
Step 1: get a bill-wise export out of Tally
The ageing is built from bills, not ledger balances. In TallyPrime the outstandings reports sit under Display More Reports → Statements of Accounts → Outstandings (Receivables, Payables, Ledger, Group); they work only where bill-wise details are maintained for the party ledgers. The ageing method option (F6) lets you choose ageing by bill date or by due date and set your own period bands, and Alt+E exports the report. Menus and keys differ between Tally releases, so check against the client's version.
Two practical choices make the Excel work easier:
- Export the bill-by-bill list, not an age-wise summary. Tally's own bands rarely match Schedule III out of the box (a receivables table needs a 6-month band and a payables table does not), and a summary cannot be split into disputed or doubtful. A list with party, bill date, bill reference, due date and pending amount lets you do everything in Excel. The Tally trial balance export is still needed alongside, for the tie-out.
- Clean the data first. Unadjusted receipts and "On Account" amounts make old bills look outstanding; credit balances in debtors and debit balances in creditors are not trade receivables or payables at all; duplicate party ledgers split one customer into two. Fix these Tally ledger errors before finalisation, then take the export.
Step 2: the ageing schedule format in Excel — columns and bucket formulas
Paste the export into a sheet called Bills and put the balance sheet date in a cell outside the data, say $M$1 = 31-03-2026 (row 1 holds the headings, data starts in row 2). Add helper columns so the sheet looks like this:
| Col | Heading | Content |
|---|---|---|
| A | Party | From the export |
| B | Bill date | From the export |
| C | Bill ref | From the export |
| D | Due date | From the export; where blank, see below |
| E | Amount | Pending amount (positive) |
| F | Unbilled | Y / blank — accrued items not yet invoiced |
| G | Disputed | Y / blank — only with evidence of the dispute |
| H | Doubtful (receivables) or MSME (payables) | Y / blank |
| I | Months past due | Formula |
| J | Bucket | Formula |
| K | Row | Formula |
Due date vs bill date. Schedule III ages from the due date of payment; only where no due date is specified does the disclosure run from the date of the transaction. For current receivables, the Guidance Note adds that where no due date is specifically agreed, the company's normal credit period is taken into account in computing it. So if the debtors export has no due date but the client allows 45 days, fill the blanks in column D with the bill date plus 45 days (=B2+45), and keep agreed due dates where they exist. For payables the Guidance Note is stricter: where no due date was agreed, in writing or orally, age from the transaction date (the date the liability was recorded), so column D is simply the bill date. Whatever basis you use, note it in the working paper.
Months past due (I2):
=IF(OR(F2="Y",D2>$M$1),"",IF(D2="","CHECK",DATEDIF(D2,$M$1,"m")))
DATEDIF counts completed months and returns an error if the start date is after the end date, which is why amounts not yet due are filtered out first. Calendar months match the wording of the buckets better than dividing days by 30.
Receivables bucket (J2):
=IF(F2="Y","Unbilled",IF(D2>$M$1,"Not due",IF(I2<6,"Less than 6 months",IF(I2<12,"6 months – 1 year",IF(I2<24,"1–2 years",IF(I2<36,"2–3 years","More than 3 years"))))))
Payables bucket (J2 on the payables sheet):
=IF(F2="Y","Unbilled",IF(D2>$M$1,"Not due",IF(I2<12,"Less than 1 year",IF(I2<24,"1–2 years",IF(I2<36,"2–3 years","More than 3 years")))))
Row label (K2) — receivables, then payables:
=IF(G2="Y","Disputed","Undisputed")&" Trade Receivables – considered "&IF(H2="Y","doubtful","good")
=IF(G2="Y","Disputed dues – "&IF(H2="Y","MSME","Others"),IF(H2="Y","MSME","Others"))
Make the labels in K match the row headings in the grid character for character — the grid looks them up. Either keep the (i)–(iv) numbering in a separate column of the grid, or drop it from column A, because the K formulas produce the labels without it.
Step 3: fill the grid with SUMIFS, or a pivot
In the note-format grid, with row labels in column A and bucket names in row 4, each cell is one formula:
=SUMIFS(Bills!$E:$E,Bills!$K:$K,$A5,Bills!$J:$J,B$4)
Copy it across and down, add a Total column and a Total row, and the grid stays in the exact Schedule III layout however many bills are added. A pivot table (Rows: Row, Columns: Bucket, Values: Sum of Amount) gives the same figures and is quicker for a first look, but it sorts buckets alphabetically and drops empty ones — fine for review, awkward as the note itself. Many firms keep the pivot as the check and the SUMIFS grid as the note.
If the statements are in lakhs or thousands, round in the grid (=ROUND(SUMIFS(...)/100000,2)) and adjust any rounding difference in the largest cell so the total agrees with the note — the unit must be the one used throughout, as covered in Schedule III rounding off.
Worked example: from bills to the note
Balance sheet date 31 March 2026; normal credit period 45 days. The cleaned bill-wise list for a small company:
| Party | Bill date | Due date | Amount (₹) | Flags | Months | Bucket |
|---|---|---|---|---|---|---|
| Alpha | 10-03-2026 | 24-04-2026 | 2,40,000 | – | – | Not due |
| Beta | 25-02-2026 | 11-04-2026 | 1,20,000 | – | – | Not due |
| Alpha | 05-01-2026 | 19-02-2026 | 1,10,000 | – | 1 | Less than 6 months |
| Beta | 15-08-2025 | 29-09-2025 | 60,000 | – | 6 | 6 months – 1 year |
| Gamma | 20-11-2024 | 04-01-2025 | 85,000 | Disputed | 14 | 1–2 years |
| Zeta | 01-06-2024 | 16-07-2024 | 50,000 | Disputed, doubtful | 20 | 1–2 years |
| Delta | 12-02-2023 | 29-03-2023 | 45,000 | Doubtful | 36 | More than 3 years |
| Epsilon | – | – | 30,000 | Unbilled | – | Unbilled |
Note Beta's August bill: due on 29 September 2025, it is six completed months past due at 31 March 2026, so it falls in 6 months – 1 year, not less than 6 months. Boundaries are where a "days ÷ 30" formula drifts: a bill due 4 April 2025 is 361 days old at 31 March 2026, which is 12.03 by days ÷ 30 (1–2 years) but only 11 completed months (6 months – 1 year). An amount falling due on the balance sheet date itself counts as due (0 months, less than 6 months); one due exactly three years earlier shows 36 months and is put in "more than 3 years" — adjust the cut-offs if your firm treats the boundary differently. The SUMIFS grid then reads (₹):
| Particulars | Unbilled | Not due | < 6 m | 6 m – 1 y | 1–2 y | 2–3 y | > 3 y | Total |
|---|---|---|---|---|---|---|---|---|
| (i) Undisputed – considered good | 30,000 | 3,60,000 | 1,10,000 | 60,000 | – | – | – | 5,60,000 |
| (ii) Undisputed – considered doubtful | – | – | – | – | – | – | 45,000 | 45,000 |
| (iii) Disputed – considered good | – | – | – | – | 85,000 | – | – | 85,000 |
| (iv) Disputed – considered doubtful | – | – | – | – | 50,000 | – | – | 50,000 |
| Total | 30,000 | 3,60,000 | 1,10,000 | 60,000 | 1,35,000 | – | 45,000 | 7,40,000 |
A payables grid built the same way might read: MSME — Not due 2,10,000, less than 1 year 35,000 (total 2,45,000); Others — Unbilled 40,000, Not due 4,80,000, less than 1 year 1,25,000, 1–2 years 18,000, more than 3 years 12,000 (total 6,75,000); Disputed dues – Others — 2–3 years 22,000; grand total 9,42,000. The 35,000 of MSME dues past their due date is the starting point for the interest question under the MSMED Act — but interest runs from the appointed day under section 15 (at most 45 days from acceptance), so check the Not due MSME bills too where credit terms exceed 45 days.
When the client does not keep bill-wise details
Many small clients run party ledgers without bill-wise tracking, so there is no outstandings report to export. A common practice is to age the closing balance on a first-in, first-out basis: assume receipts settled the oldest invoices first, so the closing balance consists of the most recent invoices. This is a practical method, not something Schedule III or the Guidance Note prescribes; use it only where it reflects how the party actually pays, identify known disputes and retentions separately, and record the basis in the working paper.
In Excel, per party: list the sales invoices from the ledger newest first (column B amount), put the closing balance in $E$1, a running total in C (=SUM($B$2:B2)) and the amount allocated to each invoice in D:
=MAX(0,MIN(B2,$E$1-(C2-B2)))
Example: closing balance ₹3,40,000; invoices of 1,20,000 (15-03-2026), 90,000 (10-02-2026), 1,00,000 (20-12-2025) and 75,000 (05-06-2025). The allocation is 1,20,000, 90,000, 1,00,000 and the last 30,000 of the June invoice. The allocated amounts then go into the Bills sheet with their due dates and are bucketed by the same formulas.
Checks before the grid goes into the note
| Check | In the worked example |
|---|---|
| Grid total = gross trade receivables in the note = TB, after reclassification | 7,40,000 = debit balances in Sundry Debtors 7,10,000 + unbilled 30,000 (its own ledger); the 18,000 of customer credit balances goes to other current liabilities, not into the grid |
| Considered good (rows i + iii) = secured good + unsecured good in the sub-classification | 5,60,000 + 85,000 = 6,45,000 |
| Doubtful (rows ii + iv) agrees with the allowance for bad and doubtful debts, or the difference is explained | 45,000 + 50,000 = 95,000 |
| MSME rows (i + iii) of payables = micro and small enterprises dues on the face (or the difference, such as medium enterprises, explained) | 2,45,000 + nil |
| Payables grid total = trade payables in the note, with no provisions in Unbilled | 9,42,000 |
| Every "Disputed" flag has evidence; every "Doubtful" flag ties to the provision working | Gamma, Zeta: correspondence on file |
| Previous-year grid as at last year's date, totals agree with last year's signed note | Both years shown |
Try SignReady: Editable Excel statements from the TB. First 3 finalisations free.
Start freeFrequently asked questions
What is the ageing schedule format for trade receivables under Schedule III?
Four rows — undisputed and disputed, each considered good and considered doubtful — and five buckets from the due date of payment: less than 6 months, 6 months–1 year, 1–2 years, 2–3 years and more than 3 years. ICAI's Guidance Note adds Unbilled and Not due columns before the buckets so the total agrees with the note.
What is the creditors ageing schedule format in Schedule III?
Four rows — MSME, Others, Disputed dues – MSME and Disputed dues – Others — and four buckets from the due date: less than 1 year, 1–2 years, 2–3 years and more than 3 years, with Unbilled and Not due columns added before them.
Which Excel formula calculates ageing buckets?
Compute completed months past the due date with DATEDIF(due date, balance sheet date, "m") — only for amounts already due — and convert the months into a bucket with nested IFs (under 6, 12, 24, 36 months for receivables; 12, 24, 36 for payables). SUMIFS on the row and bucket then fills the grid.
Should ageing be calculated from the bill date or the due date?
From the due date of payment. Schedule III uses the transaction date only where no due date is specified, and for current receivables the Guidance Note says the normal credit period should be taken into account where no due date is agreed.
How do I prepare a debtors ageing if Tally bill-wise details are not maintained?
A common practice is first-in, first-out: treat the closing balance as made up of the most recent invoices and age those. It is not prescribed by Schedule III, so use it only where it reflects how the party pays, separate known disputes, and record the basis in the working paper.
Sources
- Companies Act, 2013 — Schedule III, Division I (India Code, as amended)
- Schedule III amendment — G.S.R. 207(E), 24 March 2021 (Gazette)
- ICAI — Guidance Note on Division I – Non Ind AS Schedule III to the Companies Act, 2013 (Revised January 2022)
- TallyPrime Help — Outstanding receivables and payables reports
Upload the client's Tally trial balances to SignReady and you get the Schedule III statements and notes in editable Excel, with the previous year. Fill the ageing from the Tally ageing report in section 5 (left blank, the draft shows "INPUT REQUIRED"), add the MSME dues for the face split and the MSME note, and let FinalCheck flag arithmetic and cross-reference differences. First 3 finalisations free.
Audit and accounts updates, practical tips and knowledge: join the Audit and Accounts Update group on WhatsApp.
Version history: 23 Oct 2026 — first published.
