Debt

Loan tracker spreadsheet template with EMI, balance and end date

A loan tracker spreadsheet you can copy: the column layout, formulas for months remaining and total still to pay, and two sample rows with made-up amounts.

Editorial illustration for Loan tracker spreadsheet template with EMI, balance and end date

A good loan tracker spreadsheet needs eleven columns: loan name, type, lender, first EMI date, tenure, EMI, due day, EMIs paid, months remaining, last EMI month and total still to pay. Months remaining is tenure minus EMIs paid, and total still to pay is EMI multiplied by months remaining. Copy the layout below into Excel or Google Sheets and fill one row per loan.

What is the column layout?

ColHeaderWhat goes in it
ALoan nameA label you will recognise, such as Home loan
BTypeHome, car, personal, gold, phone
CLenderAs printed on your statement
DFirst EMI dateA real date, so formulas can use it
ETenure (months)Original number of instalments
FEMI (₹)The instalment you pay each month
GDue dayDay of month the EMI is taken
HEMIs paidUpdate monthly, from your statement
IMonths remainingFormula
JLast EMI monthFormula
KTotal still to pay (₹)Formula

Which formulas go in row 2?

Assume your first loan is in row 2 and you have room for loans down to row 20. In both Excel and Google Sheets:

  • Months remaining (I2): =E2-H2
  • Last EMI month (J2): =EDATE(D2, E2-1), then format the cell as Mon-YYYY
  • Total still to pay (K2): =F2*I2
  • Total monthly EMIs (F22): =SUM(F2:F20)
  • Total still to pay across loans (K22): =SUM(K2:K20)
  • Loans still running: =COUNTIF(I2:I20, ">0")

EDATE(D2, E2-1) returns the date E2-1 months after the first EMI date. For a 60-month loan with the first EMI in April 2024, that gives March 2029, which is the 60th instalment.

What do two sample rows look like?

These are invented numbers to show how the sheet behaves.

Loan nameTypeEMI (₹)TenureEMIs paidMonths remainingTotal still to pay (₹)
Home loanHome31,0002403021065,10,000
Car loanCar12,8006017435,50,400

Check the arithmetic: 240 - 30 = 210 months, and 31,000 × 210 = ₹65,10,000. For the car, 60 - 17 = 43 months, and 12,800 × 43 = ₹5,50,400.

What about gold loans and loans that do not follow EMIs?

Some loans, gold loans in particular, may be repaid in a different pattern, for example interest at intervals and the principal at the end. If yours is one of these, replace the EMI column with the regular payment and add a column for the principal due date. Read your own loan agreement for the exact repayment terms rather than assuming.

How do I keep it accurate each month?

  1. When the EMI debit shows on your statement, add 1 to EMIs paid.
  2. If you part-prepay, ask the lender for the revised schedule and update tenure or EMI to match.
  3. If the loan has a floating rate, check whether the EMI or the tenure changed and update the row.
  4. Once a quarter, compare months remaining with the number on the lender's statement.

Freeze row 1 so headers stay visible, and consider conditional formatting that highlights rows where months remaining is 3 or less, because those are the loans about to free up cash.

If you share the sheet with a spouse or a parent, add one more column called Co-borrower or Guarantor, and another for Insurance linked to the loan, if any. They do not affect any formula, but they answer questions that otherwise need a search through old papers. Keep the sheet in a place only the people who need it can open, and never put account numbers, passwords or card details into it.

When does a spreadsheet stop being enough?

The sheet only knows what you type. If EMIs are tagged to a loan automatically from your statements, the check becomes easier. SpendixAI lets you upload bank statements in PDF, CSV or Excel and review rows before they are saved, so the debits show up against the right account for you to compare with your sheet. For a handful of loans, the sheet alone is perfectly fine.

SPENDIXAI JOURNAL

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

Explore your reports