If you have lent to several people, one table keyed by person is enough: date, amount lent, repaid so far, balance and the date of your last reminder. Add a total line at the bottom and you will always know who owes what and who is due a gentle nudge.
What does the person-wise ledger look like?
This is a hypothetical ledger of five people on one particular day. It is the kind of udhar record a shopkeeper has always kept, made neat enough to share with yourself in a month.
| Person | Date lent | Amount lent | Repaid so far | Balance | Last reminder |
|---|---|---|---|---|---|
| Ravi | 12 Aug | ₹5,000 | ₹2,000 | ₹3,000 | 2 Oct |
| Sunita | 3 Sep | ₹12,000 | ₹0 | ₹12,000 | 28 Sep |
| Imran | 20 Sep | ₹2,500 | ₹2,500 | ₹0 | Cleared |
| Pooja | 1 Oct | ₹8,000 | ₹3,000 | ₹5,000 | 15 Oct |
| Dev | 14 Oct | ₹1,500 | ₹0 | ₹1,500 | None yet |
| Total | - | ₹29,000 | ₹7,500 | ₹21,500 | - |
Check the arithmetic: ₹5,000 + ₹12,000 + ₹2,500 + ₹8,000 + ₹1,500 = ₹29,000 lent. Repaid is ₹2,000 + ₹2,500 + ₹3,000 = ₹7,500. Outstanding is ₹29,000 - ₹7,500 = ₹21,500, which matches adding the balance column.
How do I handle several loans to the same person?
Switch to a transaction list instead of one row per person. Every line is either a loan or a repayment. The summary table is then built from that list, so a second loan to Ravi does not overwrite the first.
Set up a sheet called Entries with column A person, B date, C type (Lent or Repaid) and D amount, data in rows 2 to 500. Then, on a Summary sheet with names in column A from row 2, use these formulas in Excel or Google Sheets:
- In B2, total lent to the person named in A2: =SUMIFS(Entries!$D$2:$D$500,Entries!$A$2:$A$500,A2,Entries!$C$2:$C$500,"Lent")
- In C2, total repaid by the person named in A2: =SUMIFS(Entries!$D$2:$D$500,Entries!$A$2:$A$500,A2,Entries!$C$2:$C$500,"Repaid")
- In D2, balance owed: =B2-C2
- In D7, total outstanding if five names run from row 2 to row 6: =SUM(D2:D6)
- Days since last reminder, if the date is in E2: =TODAY()-E2
SUMIFS takes the range to add first, then pairs of a range and a condition. Spelling must match: Lent and Repaid typed the same way every time, or use a dropdown.
What should the last reminder column do?
It stops you nagging too often and forgetting too long. A common habit is to remind a person only if it has been more than a month, and to vary the message: one gentle, one specific, one offering an instalment plan. Record the date every time you send one, even if you only sent a short message.
Should I show people their own balance?
Showing a person only their own lines is often the gentlest reminder. A message such as the date lent, the amount repaid and the balance is factual and carries no pressure. Never share the full ledger with names and amounts of other people; keep that view private to you.
How often should I update it?
Whenever money moves, and in any case once a month. A monthly review takes ten minutes: open the ledger, scan the balances, note who needs a reminder, and check the total outstanding against what you feel comfortable with. If the total is growing faster than you are being repaid, it is a sign to lend less.
When does a sheet stop being enough?
A sheet is fine for a handful of people. If your lending sits among many other transactions, SpendixAI can import your bank statements and keep the surrounding spending organised in your own categories, though the person-wise balances are still best kept in a ledger like this one.
A short checklist
- One row per loan or repayment, never overwritten.
- A balance for each person and a total at the bottom.
- A reminder date for every person with a balance.
- A monthly review, with write-offs marked clearly.
- Keep it private; share only a person's own lines with them.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


