Khata (Udhaar) Register Template
A digital khata register in Excel: list your customers once, add every udhaar and payment as a line, and each customer's balance, last payment date and days since payment fill in by themselves. Balances over the credit limit and payments older than 30 days turn red.
- Published
- Reading time
- 2 min read
Khata (udhaar) register template (Excel): customer balances, last payment and days since payment
Excel (XLSX) · 24 KB · free, no sign-up
What is in the file
- Customers: name, phone, credit limit, total udhaar, total received, balance, last payment date and days since the last payment, for up to 100 customers. Everything after the credit limit is a formula.
- Entries: one line for every udhaar given or payment received, with date, customer (picked from a drop-down of your customers), details such as items or bill number, and the amount.
- How to use: the four steps below in the file itself.
Days over 30 and balances above the credit limit are shown in red, so the customers to call stand out when you open the file.
Direct link to the file: nafaa-khata-udhaar-register.xlsx. Free to use, copy and share.
How to use it
Step 1: List your customers
Write each name once on the Customers sheet, with phone and an optional credit limit.
Step 2: Add every entry
Goods on credit go in "Udhaar given"; money received goes in "Payment received". Pick the customer from the drop-down so the spelling matches.
Step 3: Check balances
The Customers sheet adds up both columns for each customer. Balance is udhaar given minus payments received.
Step 4: Follow up on red
Call customers whose last payment is over 30 days old or whose balance is above their limit.
When a spreadsheet is not enough
A spreadsheet keeps the totals honest, but someone still has to type every credit sale from the counter, and the customer only sees the balance when you send it. Nafaa's digital khata records udhaar as part of the bill, keeps each customer's ledger and ages balances into bands, and sends WhatsApp reminders with the amount due. To check how old your udhaar is today, try the udhaar ageing calculator.
Frequently asked questions
Does the khata template work in Google Sheets?
Yes. Upload the file to Google Drive and open it with Google Sheets; the balance, last payment and days since formulas work there. In Excel you need Excel 2019 or later, or Excel for the web, because the last payment column uses the MAXIFS function. Older Excel versions show an error in that one column only.
How do I record a partial payment?
Add a new line on the Entries sheet with the date, the customer and the amount paid in the Payment received column. Do not edit or delete the original udhaar line. The balance goes down by the amount paid, the last payment date updates, and the days counter starts again from that date.
