Free Excel Debt Tracker: A Template for Debts and Repayments

If customer debts are scattered between a paper in a drawer and phone messages, you may remember a sale but forget what you received. The problem often appears when checking the remainder: was the latest payment recorded, and does the displayed figure include the old balance? A clear table brings those details together for easier review.

You can download the free Excel debt and repayment template and start with the blank-account sheet. The file also includes instructions and a completed example. The workbook uses Arabic worksheet and column labels; this article explains the steps in English. No account registration or entry of your data on the website is required. You need software that supports XLSX files to open it.

What does this template record?

The table tracks money owed to you by a person or customer and payments received from them. Use it for a personal loan, services or credit sales, keeping one account and one currency in each record. Its balance answers a specific question: how much does this person still owe me?

The file contains three worksheets:

• “Start here”: explanations of the fields and how to use the template.
• “Blank account”: space for your own account and transactions.
• “Worked example”: educational figures showing the effects of debts and repayments.

The file does not calculate loan interest or late penalties, or automatically combine several customers' accounts. Do not record personal spending as if it were money repaid by a customer. If one person owes you money and you owe someone else, keep each relationship clear instead of subtracting them into a figure that no longer identifies whose money it is.

Start with the account name, currency and previous balance

Enter a clear name, adding a short identifier if needed. Select and enter the transaction currency in the appropriate field. Formatting a cell or typing a different currency symbol does not convert the value. For two currencies, use separate records rather than combining their balances.

Next, establish whether anything was owed before the table's start date. Start at zero if there were no previous transactions. If your review confirms an agreed 500 owed to you, enter 500 in the opening-balance field once.

Do not re-enter the old invoices making up that 500 as New debt, which would count them twice. Keep their references outside the table or in notes, explaining that the previous balance already includes them. The opening balance and partial-payment example explains this further.

Give each transaction its own row

Each transaction has a date, description and amount, with room for a note. Use New debt when another amount becomes due to you and Repayment when you receive money settling part of the account. Enter positive amounts in the appropriate column and leave the other amount column empty.

Choose specific descriptions. “Repair service — reference 12” or “Payment received by transfer” explains more than “Account.” Use the transaction's actual date and keep a receipt number or reference in the note without copying unnecessary banking details.

If two images or messages refer to the same transfer, check your record before adding another repayment. A promise to pay next week belongs in a separate follow-up note. The repayment column is for confirmed receipts; a promise does not yet change the outstanding balance.

Worked example: why is the remainder 450?

Suppose the opening balance is 500, you add another service fee due of 150, and then receive a repayment of 200. All amounts use the same currency, with no other transactions:

• Opening balance: 500.
• After the new debt: 500 + 150 = 650.
• After the repayment: 650 − 200 = 450.

You are still owed 450. The example worksheet shows this sequence, while the blank-account worksheet is for your data. Check which sheet you are using so educational figures do not become mixed with real transactions.

The balance column takes the opening balance, adds all new debts through the current row and subtracts all repayments through that row. Do not overwrite the formula with the balance you expect. Find the missing or duplicate entry that explains the difference.

Check the balance and formulas before sharing

If the balance becomes negative, review the transactions. You may have entered a repayment twice, omitted an earlier debt or genuinely received more than was due. Do not remove a minus sign simply because it is unexpected. Understand the cause and correct the record according to what happened.

When correcting an entry, review the whole row: date, description and amount column. Do not sort one column independently, which can separate amounts from descriptions. Keep a copy before changing several entries or bringing in records from another file.

The worksheet provides space for 50 transactions. If you exceed the prepared area, verify that formulas and input validation extend into the added rows before relying on them. Typing amounts below the table does not automatically include them in totals. A local file also needs saving and backing up: keeping it in one folder does not protect it if the device is lost.

Before sending the file, make sure it contains only the intended person's information. To share a review copy without making your spreadsheet editable, use your spreadsheet program's Print or Export to PDF feature. Open and check the resulting file before sharing it.

When might Zawid Accounts be more convenient?

One spreadsheet may be enough for a small number of transactions. As accounts and payments increase, switching between files and copying formulas can become part of the daily workload. If you prefer recording transactions on your phone and returning to each person's account, you can download Zawid Accounts and start with the free plan.

In Zawid Accounts, create a customer account, record debts and payments, then review the transactions and statement. Follow the debt and partial-payment guide to get started. The attached Excel workbook is independent of the app, not an automatic import file. When moving a balance, check the account and exactly what it includes to avoid duplicating older amounts.

Begin with an account you know well. Enter its previous balance and first two transactions. If you can explain the result from those details, you have a sound basis for maintaining the rest of its record.