Instalment collections: the report every developer rebuilds by hand

Ask three people in a property company what is outstanding on a project and you will get three numbers. None of them is lying.

Why the numbers disagree

Each person is answering a slightly different question:

• Sales answers with the contract value not yet received. That includes instalments not yet due.
• Finance answers with invoiced amounts past their due date. That excludes anything not yet invoiced.
• The MD wants to know what will arrive this month, which is neither of those.

All three are legitimate. The problem is that nobody says which one they mean, so the numbers are compared as though they measure the same thing.

The four numbers worth separating

Contracted. The total the customer agreed to pay. Fixed at signing, changes only through a documented variation.

Fallen due. The portion the payment plan says should have arrived by today. This is the only number that means late.

Received. What actually landed in the bank and was matched to this customer. Not what somebody says they paid — what reconciled.

At risk. Fallen due, unpaid, and past whatever grace your policy allows. This is the number worth ringing somebody about.

Once those four exist separately, "how much are we owed" stops being ambiguous.

Where the manual version breaks

The rebuild-by-hand report usually fails in three places:

• Part payments. A customer pays ₦4m against a ₦5m instalment. Is that instalment paid, unpaid, or partly paid? Most spreadsheets pick one and are wrong for the other cases.
• Rescheduling. A plan was renegotiated in July. The old schedule is still in the file, so the customer looks late against dates nobody expects them to meet.
• Payments received but not matched. Money is in the bank and not yet against a customer, so it is missing from collections and present in cash. Both reports are individually correct and jointly wrong.

The design that fixes it

The collection position has to be derived from the ledger, not tallied separately. If a receipt posts to the accounts and to the customer in the same movement, the two can never disagree — not because somebody reconciles them, but because there is only one record.

A schedule that has been renegotiated keeps its history. You should be able to answer "was this customer late in July" using the plan that was in force in July, not the one agreed in August. That distinction decides arguments.