Designing EMI Systems That Still Reconcile After Two Years

Lending platforms rarely fail loudly. They fail two years in, when the finance team cannot explain why the system says a borrower owes ৳12,438 and the manual calculation says ৳12,441.

Almost every version of that problem has the same root cause: balances are stored and mutated instead of derived.

The mistake: a balance column

The intuitive design keeps a running balance on the loan record:

-- The design that will eventually drift
CREATE TABLE loans (
  id              uuid PRIMARY KEY,
  principal       numeric(14,2) NOT NULL,
  outstanding     numeric(14,2) NOT NULL,  -- mutated on every payment
  last_payment_at timestamptz
);

Every payment does UPDATE loans SET outstanding = outstanding - ?. It works until:

• A payment is recorded twice because a webhook retried
• A refund is processed outside the normal flow
• A restructuring adjusts the balance manually and nobody logs why
• Two payments land concurrently and one overwrites the other's read

None of these throw an error. The number is simply wrong from then on, and there is no record of when it went wrong.

The fix: an append-only ledger

Store events, not state. Nothing is ever updated or deleted.

CREATE TABLE ledger_entries (
  id             bigserial PRIMARY KEY,
  loan_id        uuid NOT NULL REFERENCES loans(id),
  entry_type     text NOT NULL,   -- disbursement | instalment_due | payment
                                  -- | penalty | waiver | reversal
  amount         numeric(14,2) NOT NULL,  -- signed
  effective_date date NOT NULL,
  reference      text,            -- gateway transaction id, for idempotency
  reverses_id    bigint REFERENCES ledger_entries(id),
  created_at     timestamptz NOT NULL DEFAULT now(),
  created_by     uuid NOT NULL
);
 
CREATE UNIQUE INDEX ledger_reference_idx
  ON ledger_entries (reference) WHERE reference IS NOT NULL;

The outstanding balance becomes a query:

SELECT COALESCE(SUM(amount), 0) AS outstanding
FROM ledger_entries
WHERE loan_id = $1 AND effective_date <= $2;

This buys you four things at once:

1. Idempotency. The unique index on reference makes a duplicate gateway webhook a no-op rather than a double credit.
2. Auditability. Every figure traces to entries with an author and a timestamp.
3. Correctability. Mistakes are fixed by appending a reversal, never by editing history — so the correction is itself auditable.
4. Time travel. effective_date <= ? reconstructs any historical balance exactly, which is what an auditor will ask for.

If the aggregate query gets slow at volume, add a materialised snapshot — but keep the ledger as the source of truth and rebuild snapshots from it.

Use integers or exact decimals, never floats

// Wrong — binary floating point cannot represent these exactly
0.1 + 0.2                 // 0.30000000000000004
12438.55 * 3              // 37315.649999999994

Use numeric / DECIMAL in the database and an exact decimal type in application code. Storing minor units as integers (paisa rather than taka) also works, provided every boundary in the system agrees on the convention.

Decide your rounding rule once, and write it down

An instalment of ৳10,000 over 3 months is ৳3,333.33 three times — which totals ৳9,999.99. The missing paisa has to go somewhere.

The convention we use: compute every instalment rounded to 2 decimal places, then put the residual on the final instalment. The last payment absorbs the difference, and the schedule always sums exactly to the total payable.

Whatever rule you pick, encode it in one function, test it against the edge cases, and make sure the customer-facing schedule and the ledger both use it.

Test the cases that actually occur

A test suite for a lending system should cover, at minimum:

• Part payment less than the instalment due
• Overpayment that clears the next instalment and part of the one after
• Early settlement with a rebate on unearned interest
• Payment arriving on the grace-period boundary — the off-by-one day
• Restructuring mid-term, and the schedule rebuild it triggers
• A reversed payment after a later payment has already been applied
• A leap-year February in a daily-interest product

That last one has caused more production incidents than it has any right to.

What this looks like in operation

With this design, reconciliation becomes a query rather than an investigation. When the finance team asks why a balance is what it is, you can show them the entries that produce it — with dates, authors and gateway references — instead of trying to reconstruct two years of mutations.

That is the real deliverable. The software is not finished when the payments work; it is finished when someone can prove the payments worked.

We build EMI and loan management systems on exactly this foundation, with bKash and Nagad reconciliation built in. Talk to us about your portfolio.