A no-cost EMI tracker needs one row per instalment with the due date, the date it was actually debited, the amount and a tick for matched against your bank statement. Add a closing-date field at the top and two formulas for how much you have paid and how much is left. Then you always know where the loan stands, without opening an app.
What does the layout look like?
- Top block: item, lender or card, total, number of instalments, first due date and closing date.
- Columns: A Number, B Due date, C Debit date, D Amount, E Matched.
- One row per instalment, so a six-instalment loan has six rows.
- A notes column for late debits, fees or a change in debit date.
A made-up six-instalment example
Say you bought a ₹27,000 item on six instalments of ₹4,500, each due on the 5th, starting on 5 April. By 10 June, three have been debited and matched.
| No. | Due date | Debit date | Amount | Matched |
|---|---|---|---|---|
| 1 | 5 Apr | 5 Apr | ₹4,500 | Yes |
| 2 | 5 May | 6 May | ₹4,500 | Yes |
| 3 | 5 Jun | 5 Jun | ₹4,500 | Yes |
| 4 | 5 Jul | Not yet | ₹4,500 | No |
| 5 | 5 Aug | Not yet | ₹4,500 | No |
| 6 | 5 Sep | Not yet | ₹4,500 | No |
You can fill the due dates with a formula as well. Put the first due date in B2, then in B3 type =EDATE(B2,1) and drag it down to B7, formatting the cells as dates. If the first date ever changes, the rest follow.
Which formulas do you need?
With the six rows in rows 2 to 7, these work in both Excel and Google Sheets.
- Instalments matched: =COUNTIF($E$2:$E$7,"Yes") gives 3.
- Paid so far: =SUMIF($E$2:$E$7,"Yes",$D$2:$D$7) gives ₹13,500.
- Left to pay: =SUM($D$2:$D$7)-SUMIF($E$2:$E$7,"Yes",$D$2:$D$7) gives ₹13,500.
- Closing date: =EDATE(B2,5) gives 5 September, because the last instalment falls five months after the first.
Check by hand: 3 × ₹4,500 = ₹13,500 paid, and 6 × ₹4,500 = ₹27,000, so ₹13,500 is left. The numbers agree.
How do you match a debit to a row?
Open the bank or card statement for the month and find a debit of the same amount, within a day or two of the due date. Write the date it actually left in the Debit date column and type Yes under Matched. If the debit is a day late, as in row 2, that is normal and worth a note. If a debit is missing, or appears twice, or has a different amount, leave Matched blank and look into it the same day.
If you part-pay or close early, add a note on the row where it happened and change the closing date, so the sheet does not keep promising instalments that no longer exist.
What if a debit is missing or doubled?
A missing debit may be only a delay, but it can also mean a bounced auto-debit that could attract a charge. Check that the account had enough balance on the due date and look at the lender's own account page. A doubled debit is a reason to contact the lender quickly, with the two statement lines and your schedule in hand.
Where does a tracker app help?
If you import your statements into SpendixAI, each instalment shows up as a debit with its date and account, so matching is a matter of looking, not retyping. Keep the instalment sheet for the schedule and use the app's records for the debits.
At the end of the loan
- Check that the last row is matched and the left-to-pay figure is zero.
- Ask for written confirmation that the loan is closed.
- Look at the next two statements for any stray debit.
- Archive the sheet with the invoice and the schedule.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

