Spreadsheets

Udhaar khata ledger in Excel for a shop with customer-wise balance

An udhaar khata ledger in Excel: credit sales and payments per customer, running balances with SUMIFS and an ageing split into 0-30, 31-60 and 60+ days.

Editorial illustration for Udhaar khata ledger in Excel for a shop with customer-wise balance

An udhaar khata in Excel is a single ledger tab with date, customer, credit sale and payment received, plus a summary tab that uses SUMIFS for each customer's balance and an ageing split into 0-30, 31-60 and 60+ days. It replaces the paper register and shows at a glance who owes how much and for how long.

How is the ledger tab laid out?

ColumnHeadingNotes
ADateDate of the sale or payment
BCustomerExactly one spelling per customer
CSale on credit (₹)Amount added to what they owe
DPayment received (₹)Amount they paid
ENoteBill number, items, UPI reference

One row per event, never more. A sale of ₹800 on credit is a row with 800 in column C, and a part-payment later is a row with the amount in column D. Use a drop-down for customer names; a spelling such as Sharma and Sharma ji becomes two different people to the formulas.

How do I get each customer's balance?

On a Summary tab, list customers in column A. Balance in B2:

  • =SUMIFS(Ledger!$C$2:$C$1000, Ledger!$B$2:$B$1000, A2) - SUMIFS(Ledger!$D$2:$D$1000, Ledger!$B$2:$B$1000, A2)

That is total credit given minus total payments received. For the shop's total outstanding, add up column B. To see a running balance within the ledger itself, add a column F with the customer's cumulative balance: =SUMIFS($C$2:C2, $B$2:B2, B2) - SUMIFS($D$2:D2, $B$2:B2, B2), using the expanding range so each row shows the balance after that entry.

How do I add ageing?

Ageing needs one assumption: payments settle the oldest credit first. With that, the balance is made up of the most recent sales. Columns C, D and E on the Summary tab hold the three buckets.

  • 0-30 days in C2: =MIN(B2, SUMIFS(Ledger!$C$2:$C$1000, Ledger!$B$2:$B$1000, A2, Ledger!$A$2:$A$1000, ">"&TODAY()-30))
  • 31-60 days in D2: =MIN(B2-C2, SUMIFS(Ledger!$C$2:$C$1000, Ledger!$B$2:$B$1000, A2, Ledger!$A$2:$A$1000, ">"&TODAY()-60, Ledger!$A$2:$A$1000, "<="&TODAY()-30))
  • 60+ days in E2: =B2-C2-D2

The first bucket is the smaller of the balance and the credit given in the last 30 days; the second takes what remains, up to the credit of the next 30 days; and whatever is left is older than 60 days.

What does the summary look like with five customers?

These customers and amounts are made up, as on one particular day.

CustomerBalance0-30 days31-60 days60+ days
Sharma ji₹1,200₹1,200₹0₹0
Ramesh₹3,450₹900₹1,550₹1,000
Meena₹600₹600₹0₹0
Kiran Stores₹8,200₹2,200₹3,000₹3,000
Anil₹450₹0₹0₹450

The balances add up to 1,200 + 3,450 + 600 + 8,200 + 450 = ₹13,900. The buckets add up to 4,900 + 4,550 + 4,450 = ₹13,900 too, a useful check. Notice that Kiran Stores has the largest amount and ₹3,000 beyond 60 days, so that is the first conversation. Anil owes only ₹450, but all of it is old, which a plain balance would hide.

How do I collect without losing goodwill?

  1. Sort the Summary by the 60+ column and start from the top.

Visit or call with the exact amount and the dates it relates to. Offer to settle part now and the rest by a date you note. Record each promise in the Note column. Where the customer pays by UPI, put the reference in the note, so a dispute is settled by the record. Ask regular customers to initial or acknowledge their monthly balance.

How do I keep the ledger safe?

  • Save the file with a date in its name once a week and keep a copy on a second device
  • Protect the sheet with a pass-code on the phone or laptop it lives on, so customer balances do not leak
  • Never delete a row; add a correcting row with a note
  • Compare the total outstanding with the paper register for the first month

Weekly routine

  • Enter all credit sales and payments from the day book
  • Read the 60+ column
  • Make the calls
  • Note outcomes
SPENDIXAI JOURNAL

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

Explore your reports