Partial payments, advances and allocation: keeping every invoice balance right

Why does the outstanding list go wrong in Excel?

Outstanding goes wrong in Excel because the payment is recorded against the customer, not against the invoices it pays. The customer total may still be right. The invoice balances are not, and nobody can say which bills are overdue.

A typical sheet has one row per invoice and a column for the amount received. When ₹50,000 arrives against three bills, someone types it on the newest row, or splits it by guesswork, or adds a new row called "received". A month later the ageing is wrong, the reminder goes out for an invoice that was paid, and the customer stops trusting your statements.

• Party-level receipts reduce the total but leave every invoice looking unpaid.
• Round-figure payments never match an invoice, so the leftover is forgotten or typed into the wrong row.
• Advances sit in a separate sheet and are never set off against the invoice they were meant for.
• Corrections overwrite the old figure, so there is no record of what was changed and why.

What is payment allocation?

Payment allocation is the step that links a receipt to the specific invoices it settles, and says how much goes to each. The payment is one record. The allocations are lines under it, one per invoice.

Once every receipt is allocated, each invoice has its own balance: invoice amount minus everything allocated to it. That balance is what drives the ageing report, the statement of account and the reminder you send.

What are the three cases of payment allocation?

A sound allocation model handles three cases, and a business with credit customers meets all three in its first month.

How do you allocate ₹50,000 against three invoices?

Allocate oldest first unless the customer tells you which invoices they are paying. Here is a receipt at Lakshmi Steel Furnitures, the fictional Coimbatore business on this site, from its customer Sri Ganesh Office Systems, which has three open invoices totalling ₹65,300.

1. 1

   Open the receipt for the customer

   Enter ₹50,000 received by NEFT on 6 October and the bank account it landed in.

2. 2

   List the open invoices, oldest first

   INV-2026-27-0180 for ₹18,500, INV-2026-27-0195 for ₹22,000 and INV-2026-27-0207 for ₹24,800.

3. 3

   Allocate in order

   ₹18,500 to the first, ₹22,000 to the second, and the remaining ₹9,500 to the third.

4. 4

   Check the total

   Allocations add to ₹50,000, so nothing is left unallocated. Post the receipt.

The customer now owes ₹15,300, all of it on one invoice, and that invoice ages from its own due date. When the next ₹15,300 arrives, it is allocated to INV-2026-27-0207 alone, and the invoice moves to paid. That is the many-payments-to-one-invoice case.

What do the invoice statuses posted, partially paid and paid mean?

An invoice's status should be calculated from its allocations, never typed by hand. If someone can set an invoice to paid without a receipt behind it, the status means nothing.

• Posted: the invoice is in the books and nothing has been allocated to it yet.
• Partially paid: some amount is allocated, and a balance remains.
• Paid: allocations equal the invoice amount, and the balance is zero.

How should you record an advance from a customer?

Record an advance as a receipt from the customer with no invoice allocated, so it sits on the party as an unallocated amount. When you raise the invoice, apply the advance to it.

Suppose Sri Ganesh Office Systems sends ₹20,000 with an order for steel cupboards on 8 October. The receipt posts, the bank goes up, and the customer shows a credit of ₹20,000. On 14 October you invoice ₹23,600 for the cupboards. Apply the ₹20,000 to that invoice, and it shows partially paid with ₹3,600 due.

The same approach covers an overpayment. If a customer pays ₹500 more than is due, the ₹500 stays unallocated on account and is applied to their next invoice, not refunded by an adjustment nobody can trace.

What happens when you cancel a payment?

Cancelling a payment should reverse its allocations and re-open every invoice it touched. A cheque that bounces is the usual reason.

If the ₹50,000 receipt above is cancelled, INV-2026-27-0180 and INV-2026-27-0195 go back to posted with their full balances, INV-2026-27-0207 goes back to ₹24,800 due, and the customer owes ₹65,300 again. The cancelled receipt stays on record with its reversal, so the history shows what happened and when.

How do you record a short payment when the customer deducts TDS?

A common practice is to record the amount actually received as the payment, and the TDS deducted as a TDS receivable, so the customer's balance clears in full.

Say Lakshmi Steel invoices Kovai Builders ₹59,000 including GST, and Kovai Builders pays ₹58,000, deducting ₹1,000 as TDS. The receipt is ₹58,000 to the bank. The ₹1,000 goes to a TDS receivable account through a journal against the customer. Between them, the customer's account is clear.

What about bank charges deducted from a payment?

Bank charges are a cost to you, not a balance the customer still owes. If an invoice of ₹22,000 arrives as ₹21,950 because a bank kept ₹50, many businesses record the ₹50 as a bank charges expense so the balance clears, instead of chasing the customer for it.

Agree a policy with your accountant. Small differences written off as charges should be rare and visible, not a habit that hides real short payments.

A checklist for clean allocation every week

Allocation stays clean when it is done on the day the money arrives, by the person who can see the bank statement.

• Every receipt is entered the day it reaches the bank, with the date and mode
• Every receipt is allocated to invoices, oldest first unless the customer names them
• Unallocated amounts are reviewed weekly and applied to new invoices
• TDS and bank charges are recorded, so no customer is left owing a small difference
• Bounced cheques are handled by cancelling the receipt, never by editing it
• The customer's statement of account is checked against their ledger once a month

How does SimpleERP handle partial payments and allocation?

In SimpleERP, a payment can be allocated to many invoices and an invoice can take many payments. Any amount not allocated stays on the party as on-account or advance and can be applied later. The database derives each invoice's status, posted, partially paid or paid, from its allocations, so nobody sets it by hand.

• Cancelling a payment reverses it and re-opens the invoices it settled; posted documents are never deleted, and the audit history keeps every change.
• The receivables ageing shows each party with bucket columns, and the row expands to the invoices and their balances.
• The statement of account per party shows the opening balance, dated rows, running balance, closing balance and an ageing footer, to print, save as PDF or copy as share text.

Read more on the payments and receivables page, or see how allocation fits the whole system in the accounts receivable management guide.