Tracking a loan in a spreadsheet
A spreadsheet is a perfectly good way to track one or two straightforward loans, and for a single interest-free loan with a due date it is probably the right tool. It stops working for specific, predictable reasons — and knowing which ones tells you whether yours is about to.
The minimum that actually works
Two sheets, not one. The mistake almost everyone makes is keeping a single sheet with a running balance typed in by hand, which cannot be audited and cannot be corrected.
Sheet 1 — the loan
One row per loan: borrower, amount, currency, date lent, due date or term, interest rate (and whether it is monthly or annual — write it in the header), and a note.
Sheet 2 — the repayments
One row per payment: which loan, date, amount, note. Never overwrite a row here, and never keep a running total by hand. The balance is a formula that sums this sheet against the loan — so if you find yourself typing a balance, the design has already gone wrong.
Store money as a number in one currency per loan, and never let a cell mix two. Spreadsheets will happily add pesos to dollars and give you a total that means nothing, and there is no warning when they do.
The four places spreadsheets go wrong
1. Interest that has to accrue over time
A fixed schedule is easy. What is hard is interest that keeps accruing because a payment was missed — that depends on today's date, so the sheet is only correct on the day you open it, and only if the formula is right. Overdue interest also compounds on the balance rather than the principal, which is where hand-built formulas most often quietly diverge.
The compounding people miss
$10,000.00 at 5% a month, three months overdue, has accrued $1,576.25 — not the $1,500.00 that three months of flat 5% suggests. A sheet that multiplies the principal by the rate by the months is wrong from month two onward, and wrong by more every month after.
2. Overpayments
Someone pays more than the installment. Does the extra reduce the principal, shorten the term, or shrink the remaining payments? The answer differs by repayment plan, and rebuilding the rest of the schedule correctly by hand after each irregular payment is where most spreadsheets stop being trustworthy — usually without anyone noticing, because the sheet still shows a confident number.
3. Only one person can see it
The lender's private sheet has no authority in a disagreement, because the borrower has no way to check it and no reason to trust a number they cannot see. A shared sheet fixes that and creates a new problem: now either party can edit any cell, including history.
4. Nothing tells you anything
A spreadsheet is passive. It never reminds the borrower a payment is due, never tells you someone is two months behind, and is only consulted by someone who already remembered to consult it. On a loan between people, the reminder is the whole mechanism — the hard part is the asking, and a file cannot do the asking for you.
When a spreadsheet is genuinely the right answer
- One or two loans, interest-free, with a single due date.
- A loan already fully repaid that you are keeping for the record.
- Anything you want to model or restructure — spreadsheets are far better than any app at asking “what if we changed the term”.
- Cases where you actively want the record private and not shared with the borrower.
When to stop
Move off a spreadsheet when any of these becomes true:
- More than about three or four active loans
- Any loan carrying interest that keeps accruing past a due date
- Payments that arrive irregularly, in amounts that are not the scheduled one
- You want the borrower to see the same figures you do
- You want the due-date reminder to come from something other than you
That last one is usually the deciding factor, and it is the one a spreadsheet can never solve.
What LoanCompass does differently
Every derived figure — balance, remaining schedule, ahead or behind, what is due this month — is recomputed from the repayments and the terms each time it is shown, so there is no stored total to drift. Overdue interest accrues on a daily schedule without anyone opening the app. The borrower signs in to the same figures rather than being told them. And the reminder goes out three days before each due date on its own.
It is free while in early access, no money moves through it, and you can check its arithmetic against yours without creating an account. How it works.
Keep reading
Stop recalculating this by hand
LoanCompass keeps the schedule, the repayments and the running balance for every loan you have made, so the figures on this page stay current without you rebuilding them. It is free while in early access, and no money moves through it.