18.11 Reconciliation algorithm

Daily reconciliation is non-negotiable. Mis-matched entries silently accumulate into account-balance errors, regulatory reporting errors, and audit findings. This page is the algorithm.

The reconciliation problem

The LMS expects certain entries from the sponsor bank each day:

Disbursement payouts: today’s executed disbursements should show as debits in disbursement account.
NACH collections: yesterday’s NACH presentations should show as credits (success) or non-entries (bounce).
Virtual account credits: per-borrower virtual accounts should receive borrower transfers.
Payment gateway settlements: PG settles per cycle.
Co-lending escrow movements: incoming from partner; outgoing to lenders.
Vendor / DSA payouts: lender’s outgoing.

Sponsor bank’s statement contains all credits and debits. Reconciliation matches LMS expected with bank actual.

Three-way reconciliation for co-lending

For co-lent loans, three-way reconciliation:

LMS expected ↔ Sponsor bank actual ↔ Partner's records

All three must match. Any divergence is an exception.

Algorithm — basic matching

For each day:
    LMS_expected = collect from loan_event:
        - disbursements executed
        - NACH presentations
        - charges levied
        - settlements due

    Bank_actual = parse sponsor-bank statement (CSV / API):
        - credits and debits with reference, narration, amount, date

    Match algorithm:
        For each Bank_actual entry:
            Find matching LMS_expected entry by:
                - Reference match (idempotency key / UTR / virtual account)
                - Amount match (exact)
                - Date match (same day)

            If unique match: mark both as matched.
            If multiple LMS candidates: mark as ambiguous, queue for ops.
            If no LMS match: extra entry; queue for investigation.

        For each LMS_expected without bank match:
            Missing entry; queue for investigation.

Matching strategies (in priority order)

1. Reference match (strongest)

UTR matching: each disbursement has a UTR; bank statement shows UTR; direct match.
Idempotency key: lender’s reference embedded in NACH file; bank ack returns the same.
Virtual account match: borrower’s payment to virtual account; LMS knows VA → loan mapping.

2. Composite match

When reference unavailable:

Amount + Date + Counter-party combination usually uniquely identifies.

3. Fuzzy match

For entries with slight differences:

Amount within tolerance (e.g., < ₹1) — rare; sometimes rounding differences.
Date offset by 1 day — settlement timing.

Fuzzy matches always flagged for ops review.

Exception categories

Exception	Cause	Action
Extra credit (in bank, no LMS expected)	Borrower paid via unfamiliar mode	Investigate; possibly allocate to oldest open loan
Extra debit (in bank, no LMS expected)	Bank charges / errors	Investigate; reverse if bank error
Missing credit (LMS expected, no bank entry)	NACH bounced / wrong account	Investigate; mark as bounce; trigger re-presentation
Missing debit (LMS expected, no bank entry)	Payout failed	Investigate; reattempt
Amount mismatch	Partial credit (bank charged commission)	Reconcile with adjustment
Multiple matches	Same amount, same date, multiple candidates	Manual disambiguation

Daily reconciliation report

Output per day per account:

Reconciliation Report — 2026-05-15
Sponsor Bank: HDFC; Account: Collection Account

Total bank entries: 423
Total LMS expected: 425

Matched: 419
Exceptions: 6
  - 3 Missing credit (NACH bounces — to investigate)
  - 1 Extra debit (bank charge — to verify)
  - 1 Ambiguous match (multiple amount-date candidates)
  - 1 Amount mismatch (₹5 commission difference)

Action queue: 6 items

Exception triage SLA

High-priority exceptions: same-day investigation.
- Missing payout (borrower hasn’t received funds).
- Material amount mismatch (> ₹1,000).
Medium: within 48 hours.
Low (minor / known patterns): within a week.

Reconciliation engine

class ReconciliationEngine:

    def reconcile_day(self, date, account_id):
        bank_entries = self.fetch_bank_statement(account_id, date)
        lms_expected = self.collect_lms_expected(account_id, date)

        matches = []
        exceptions = []

        # First pass: reference-based matching
        for bank in bank_entries:
            lms_match = self.find_by_reference(lms_expected, bank)
            if lms_match:
                matches.append((bank, lms_match))
                lms_expected.remove(lms_match)
                bank.matched = True

        # Second pass: amount + date + counter-party
        for bank in bank_entries:
            if bank.matched: continue
            lms_match = self.find_by_composite(lms_expected, bank)
            if lms_match:
                matches.append((bank, lms_match))
                lms_expected.remove(lms_match)
                bank.matched = True

        # Third pass: fuzzy
        for bank in bank_entries:
            if bank.matched: continue
            candidates = self.find_fuzzy(lms_expected, bank)
            if len(candidates) == 1:
                # Flag for confirmation
                exceptions.append(FuzzyMatch(bank, candidates[0]))
            elif len(candidates) > 1:
                exceptions.append(Ambiguous(bank, candidates))

        # Remaining bank entries = extra
        for bank in bank_entries:
            if not bank.matched:
                exceptions.append(Extra(bank))

        # Remaining lms_expected = missing
        for lms in lms_expected:
            exceptions.append(Missing(lms))

        return ReconReport(date, matches, exceptions)

Post-reconciliation actions

After exceptions are resolved:

For each resolved exception:
    Update LMS as needed (e.g., mark NACH as bounced; trigger re-attempt; or adjust loan ledger for the unexpected credit).
    Update audit trail.
    Notify ops / finance.

Co-lending reconciliation (three-way)

For co-lent loans, after LMS-vs-bank reconciliation, additional layer:

LMS share-level data ↔ Partner's reported data

For each loan reported in partner's MIS:
    Match outstanding (partner share) per partner ↔ per LMS
    Match accrued interest (partner share)
    Match repayments YTD (partner share)

    Mismatches → exception → manual reconciliation with partner

This typically runs weekly or monthly depending on agreement.

Performance

For sponsor-bank account with ~5,000 daily transactions:

Reconciliation completes in < 15 minutes.
Exception rate target < 0.5% (i.e., ~25 exceptions / day).

What the LMS must enforce

Daily reconciliation must run — no missed days.
Exception queue must be triaged within SLA.
Exceptions resolved with audit trail.
Persistent unresolved exceptions escalated to senior management.
Co-lending three-way reconciliation per agreement frequency.
Reconciliation report retained for audit.

Common patterns

NACH timing

NACH cycle is T+1: today’s presentation file lands in bank statement tomorrow as success or non-entry (bounce). The reconciliation engine must understand this timing.

Partial credit

If bank deducts a charge before crediting, the borrower’s credit is short. Reconcile with adjustment (charge captured separately as bank-side cost).

Returned funds

Disbursement that’s returned (beneficiary bank rejected) shows as credit back to lender’s account. Reconciliation captures this and triggers reverse-loan-booking.

Fee structure

Sponsor bank may deduct per-transaction fees from each NACH credit. These accumulate and should be tracked separately.

Compliance touchpoints

DPDP — borrower data in reconciliation.
Audit — reconciliation reports retained.
Co-lending guidelines — three-way reconciliation expected.