Income and work

Udhaar ledger for a family shop: who owes what

An udhaar ledger for a family shop: columns for customer, date, credit and payment, balance formulas that work, and a polite rhythm for reminders.

Editorial illustration for Udhaar ledger for a family shop: who owes what

An udhaar ledger needs one row per event: the date, the customer's name, whether it was credit given or a payment received, and the amount. A formula then gives each customer's balance. Keep it in one place, update it the same day, and send reminders on a fixed, friendly schedule rather than when stress builds.

How should the ledger be laid out?

ColumnHeaderWhat goes in it
ADateDay of the entry
BCustomerName, spelt the same way every time
CTypeCredit or Payment
DAmountRupees, as a number
ENoteItems given, or how the payment was made

Use a separate tab for the customer list, so that the name you pick is always exactly the name in the ledger. Spelling variants are the most common reason the balance looks wrong.

How do I get each customer's balance?

On a Balances tab, list each customer in column A. In B2 enter =SUMIFS(Ledger!$D$2:$D$1000,Ledger!$B$2:$B$1000,$A2,Ledger!$C$2:$C$1000,"Credit")-SUMIFS(Ledger!$D$2:$D$1000,Ledger!$B$2:$B$1000,$A2,Ledger!$C$2:$C$1000,"Payment") and copy it down. A positive number is what the customer owes. For the shop's total outstanding, use =SUM(B2:B200).

Take a made-up customer, Sharma ji. On 4 March he takes goods worth ₹1,250 on credit, on 9 March another ₹840, and on 15 March he pays ₹1,000. The balance is 1,250 + 840 − 1,000 = ₹1,090.

DateTypeAmount (₹)Balance after (₹)
4 MarchCredit1,2501,250
9 MarchCredit8402,090
15 MarchPayment1,0001,090

How do I see who has not paid for a while?

Add a column for the date of the last payment. In Excel 2019 or 365 and in Google Sheets, =MAXIFS(Ledger!$A$2:$A$1000,Ledger!$B$2:$B$1000,$A2,Ledger!$C$2:$C$1000,"Payment") returns it, and =TODAY()-C2 gives the days since. Older Excel versions do not have MAXIFS, so sort the ledger by customer and read the last payment instead. Anyone with a balance and a long gap goes on your reminder list.

What is a reminder rhythm that stays polite?

  • Pick one day a month, such as the first, to look at balances above a small amount you choose.
  • Send a short private message, never a public one: your balance is ₹1,090 as of today; please pay when convenient.
  • Use the same wording for everyone, so no one feels singled out.
  • If a customer promises a date, write it in the note and check that day.
  • After two reminders, a word in person is usually kinder than a third message.

Set a credit limit per customer, and let the sheet warn you. If you decide no one should owe more than ₹2,000 without a conversation, put =IF(B2>2000,"Over limit","") in C2 and copy it down. In the example above, Sharma ji at ₹1,090 is fine. Before festival weeks, when credit tends to rise, a quick look at the Over limit column helps you decide who needs a friendly word before the next order.

What if a customer disputes the balance?

Share the list of dated entries and ask them to point to the one they question. Most disagreements are one missed payment or one wrongly remembered item. If the dispute is serious or a large amount is involved, take advice from a lawyer or a CA about the right next step, since the rules depend on the situation.

What about the family's own spending?

The ledger is a shop record. When the household wants to keep its personal spending separate, SpendixAI works for that side: it imports statements and sorts the family's expenses. It does not manage customer credit, so keep the ledger in its own sheet, and settle it with the customers you trust and see every week.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports