Reconciling fee collection against partial payments nobody warned you about
A school in Ghaziabad collects ₹4,200 in three UPI transfers from the same parent over two weeks. The fee for the term is ₹38,500. The accounts team sees three credits of ₹1,000, ₹2,000, and ₹1,200 on the bank statement, none of which matches any invoice amount, and starts reconciling manually in Excel.
This is the part of fee collection that breaks. The payment gateway reports each transaction correctly. The bank statement reports each credit correctly. The ERP shows the invoice as partially paid. But none of these three sources agree on what a single fee payment looks like, because the parent treated the invoice as a target and paid toward it in pieces.
We ran into this building the fee module for a school ERP last year. The first version of our reconciliation job matched each bank credit to a single invoice by amount. It worked for 81% of transactions in the first month. The remaining 19% were partial payments, late fees added after the parent initiated but before settlement, and cases where a grandparent paid from a different account than the one linked to the student.
The fix is to stop matching on amount alone and match on a combination of UTR, student ID embedded in the payment note, and a tolerance window for amount. When a parent pays through the ERP's payment link, the UTR and student ID are available in the gateway webhook. When they pay directly via UPI to the school's virtual account, the virtual account ID maps to the student, but the credit on the bank statement is a single line with a reference number.
Here is the reconciliation logic we settled on:
def match_credit(credit, open_invoices, tolerance=50):
candidates = []
for inv in open_invoices:
if credit.utr and credit.utr in inv.gateway_references:
return inv, "exact_utr"
if abs(credit.amount - inv.balance_due) <= tolerance:
candidates.append((inv, "amount_match"))
if credit.virtual_account_id == inv.student.virtual_account_id:
candidates.append((inv, "vpa_match"))
if len(candidates) == 1:
return candidates[0]
if credit.amount < 5000:
smallest = min(open_invoices, key=lambda i: i.balance_due)
return smallest, "partial_fallback"
return None, "unmatched"The `partial_fallback` is the part that matters. When a credit is small and no exact match exists, we apply it to the invoice with the smallest outstanding balance for that student. This is wrong about 8% of the time — the parent might be paying toward a specific optional fee like transport, not tuition. But it gets the credit applied to the right student, and the accounts team can reclassify it in the ERP instead of starting from a raw bank statement.
The tradeoff we accepted: automatic application of partial payments means the ERP sometimes shows a fee as partially paid when the parent intended to close a different fee head. We flag these with a `confidence` field — `exact_utr` is high confidence, `partial_fallback` is low — and the accounts team reviews low-confidence matches before locking the month.
One thing we tried and dropped: asking parents to enter the invoice number in the UPI note field. About 30% of parents did this correctly, 40% entered the wrong format, and 30% left it blank. The reconciliation job cannot depend on data that only sometimes exists.
The bank statement format itself is a separate problem. Each bank exports a different CSV schema. We wrote adapters for HDFC, ICICI, and SBI, and the school's accountant uploads the statement file. The job parses it, groups credits by virtual account ID, and runs the matching function against open invoices for that student. Credits that do not match any student land in an exception queue.
The result is that the accounts team now spends about 40 minutes per month on fee reconciliation instead of three days. The 40 minutes are spent on the exception queue and reclassifying low-confidence partial payments.