A car loan tracker needs two tabs: one for the EMI schedule, one for running costs such as fuel, service and insurance. A small summary then adds them into a combined monthly total. You can build it in Excel or Google Sheets in under an hour using SUMIFS and EDATE.
Tab 1: the loan schedule
Name the tab Loan. Put these headings in row 1:
- A: No. (1, 2, 3 and so on)
- B: Due date
- C: EMI amount
- D: Paid on
- E: Amount paid
- F: Status (type Paid or Pending)
Fill the due dates and EMI amounts from the schedule your lender gave you. Use invented figures to test it first. Example rows:
| No. | Due date | EMI amount | Paid on | Amount paid | Status |
|---|---|---|---|---|---|
| 1 | 05-05-2026 | 14500 | 05-05-2026 | 14500 | Paid |
| 2 | 05-06-2026 | 14500 | 05-06-2026 | 14500 | Paid |
| 3 | 05-07-2026 | 14500 | - | - | Pending |
Below the table, or in a corner, add two checks: paid so far =SUMIF($F$2:$F$61,"Paid",$E$2:$E$61) and instalments left =COUNTIF($F$2:$F$61,"Pending"). The range runs to row 61 so a 60-month loan fits.
Tab 2: running costs
Name this tab Running. One row per cost:
- A: Date
- B: Type (Fuel, Service, Insurance, Parking, Tolls or Other)
- C: Amount
- D: Odometer reading (optional)
- E: Note
| Date | Type | Amount | Odometer | Note |
|---|---|---|---|---|
| 02-05-2026 | Fuel | 2400 | 18250 | Full tank |
| 09-05-2026 | Parking | 1200 | - | Monthly pass |
| 21-05-2026 | Service | 6800 | 18900 | Scheduled service |
| 10-06-2026 | Insurance | 28000 | - | Annual premium |
Enter dates as real dates, not text, or the formulas below will not catch them. If a date is left-aligned, it is usually text.
How do you build the combined monthly total?
Add a third tab named Summary. In cell A2 put the first day of the month you want to check, for example 01-05-2026 entered as a date. Then:
- Running costs for the month: =SUMIFS(Running!$C$2:$C$500,Running!$A$2:$A$500,">="&$A$2,Running!$A$2:$A$500,"<"&EDATE($A$2,1))
- EMI for the month: =SUMIFS(Loan!$E$2:$E$61,Loan!$D$2:$D$61,">="&$A$2,Loan!$D$2:$D$61,"<"&EDATE($A$2,1))
- Combined total: add the two cells together, for example =B2+C2.
Change A2 and everything updates. With the example rows, May 2026 gives running costs of 2,400 + 1,200 + 6,800 = ₹10,400, plus the EMI of ₹14,500, for a combined ₹24,900. June includes the ₹28,000 insurance premium, which is why June looks very different from May.
Why does one month look so uneven?
Because insurance and service are lumpy. For a smoother view, add a column on Summary that divides the last twelve months of running costs by twelve. It is more useful for planning than a single month with a big bill.
When a sheet stops being enough
A sheet is perfect for one car and one loan. When typing every fuel entry becomes a chore, SpendixAI can take your bank statements as uploads, with the rows reviewed before saving, and sort fuel, EMI and service into categories for you. You would still keep the loan schedule check above.
What if you make a part-payment or the schedule changes?
Add a row for the extra payment on the Loan tab, with its date and amount, and mark its status Paid. Then ask the lender for the revised schedule and replace the remaining due dates and EMI amounts with it. If the lender shortens the tenure instead of lowering the EMI, your Pending rows will be fewer, and the instalments-left check will show the new count. Keep the old schedule in a separate tab so you can see what changed.
Fuel entries are the ones most likely to be forgotten. A habit that works is to type the amount into the Running tab at the pump while the receipt is still in your hand.
Setup checklist
- Three tabs: Loan, Running and Summary
- Dates entered as real dates
- Schedule filled from the lender's paper
- One month tested by hand against the formula result
- A reminder in your calendar to compare with the lender every six months
Useful ideas start with a clearer picture of your own spending.
Explore your reports


