Spreadsheets

Multiple loans tracker in Excel: EMI, outstanding and end date

Build one Excel sheet for every loan: lender, EMI, end date, instalments left, estimated outstanding and a total EMI to income ratio, with working formulas.

Editorial illustration for Multiple loans tracker in Excel: EMI, outstanding and end date

A loan tracker in Excel needs one row per loan and a handful of columns: lender, type, principal, yearly rate, EMI, start date and tenure in months. From those, formulas give you the end date, instalments left, an estimated outstanding balance and the total of all EMIs. Put your monthly income in one cell at the top and the sheet also shows what share of it goes to EMIs.

What columns does a multiple loans sheet need?

Keep the inputs on the left and the computed columns on the right, so you only ever type into the first seven columns. Use row 1 for headings and start loans from row 2.

ColumnHeadingWhat goes in it
ALenderName as it appears on your loan statement
BLoan typeHome, car, personal, consumer durable
CPrincipalAmount sanctioned, in rupees
DYearly rateAs a percentage, for example 8.75%
EEMIThe monthly instalment from your sanction letter
FStart dateDate the loan began; format the column as a date
GTenure (months)Total number of instalments
H to KComputedEnd date, paid, left, outstanding (formulas below)

Which formulas give end date, instalments left and outstanding?

Type these in row 2 and fill them down. They work in Excel and in Google Sheets.

  • End date in H2: =EDATE(F2, G2). This assumes the last instalment falls the same number of months after the start date as the tenure; compare with your repayment schedule.
  • Instalments paid in I2: =IF(F2>TODAY(), 0, MIN(G2, DATEDIF(F2, TODAY(), "m"))). The IF stops an error if you enter a future start date.
  • Instalments left in J2: =G2-I2
  • Estimated outstanding in K2: =IF(J2=0, 0, -PV(D2/12, J2, E2)). PV values the remaining instalments at the loan rate, and the minus sign turns its negative answer positive.

The outstanding figure is an estimate. It can drift from the lender's number because of part-prepayments, a changed rate on a floating loan, or charges added to the loan. Treat your latest loan statement as the final word and use the sheet to see the shape.

What does a worked example look like?

Say you have three loans: a home loan, a car loan and a phone bought on 0% EMI. The numbers below are made up.

LoanEMITenurePaidLeft
Home loan₹32,00024030210
Car loan₹14,500602238
Phone EMI₹3,0001248

For the phone, the rate is 0%, so the PV formula returns 8 x ₹3,000 = ₹24,000 outstanding, which is what is left to pay on a ₹36,000 phone after four instalments. Check your own paperwork for processing fees or interest hidden in a no-cost offer, because those do not show up in an EMI-only sheet.

How do I show total EMI against income?

Put your monthly take-home income in B1 under the label "Monthly income" and use these cells above the table, or on a summary row below it.

  • Total EMI of loans still running: =SUMIF($J$2:$J$20, ">0", $E$2:$E$20)
  • EMI as a share of income: =SUMIF($J$2:$J$20, ">0", $E$2:$E$20)/B1, formatted as a percentage
  • Total estimated outstanding: =SUM($K$2:$K$20)

With the example EMIs, the total is ₹32,000 + ₹14,500 + ₹3,000 = ₹49,500. If income is ₹1,20,000, the ratio is 49,500 / 1,20,000 = 0.4125, or 41.25%. There is no single right figure that applies to everyone; the number is useful because you can watch it fall when the phone EMI ends in eight months and rise if you take another loan.

How do I keep the sheet honest month after month?

  1. When a loan ends, leave the row in place; the SUMIF formula already ignores it once instalments left reach zero.
  2. After a part-prepayment, add a new row with the reduced principal and new start date rather than editing the old one, so history stays visible.
  3. On a floating-rate loan, update the rate in column D when the lender informs you of a change.
  4. Once a quarter, compare column K with the outstanding on the lender's statement and note the difference.

When does a sheet stop being enough?

Loans are only half the picture. The EMIs leave your account along with rent, groceries and everything else, and a sheet will not read your statement for you. When you want the repayments to appear next to the rest of your spending, SpendixAI can take a bank statement uploaded as PDF, CSV or Excel, let you review the rows before saving, and put them in categories you can report on.

Quick checklist

  • One row per loan, with start date and tenure filled in
  • Income cell filled and updated when it changes
  • Rate column updated for floating loans
  • Outstanding compared with the lender's statement every quarter
SPENDIXAI JOURNAL

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

Explore your reports