Debt

How to track a personal loan repayment from first EMI to last

Track a personal loan repayment from first EMI to last, with a 24-month example, interest and principal columns, and a check against the lender statement.

Editorial illustration for How to track a personal loan repayment from first EMI to last

Track a personal loan with one row per EMI: due date, amount paid, interest part, principal part and the balance left. After three EMIs, compare your balance with the lender's statement. If they match, your sheet is sound for the rest of the loan. Here is a hypothetical 24-month example you can copy.

What is the example loan?

Say you borrow ₹1,20,000 for 24 months at 14% a year. The EMI works out to about ₹5,762, which in a sheet is =PMT(14%/12, 24, -120000). Over 24 EMIs you would pay about 24 × 5,762 = ₹1,38,288, so the cost of borrowing in the example is about ₹18,288. The last EMI is often slightly different because of rounding.

How is each row calculated?

  1. Interest part = opening balance × annual rate / 12. For month 1: 1,20,000 × 0.14 / 12 = ₹1,400.
  2. Principal part = EMI minus interest part. Month 1: 5,762 - 1,400 = ₹4,362.
  3. Closing balance = opening balance minus principal part. Month 1: 1,20,000 - 4,362 = ₹1,15,638.

In a sheet, with the opening balance in C2 and the rate in a fixed cell B1, interest is =C2*$B$1/12, principal is =D2-E2 if the EMI is in D, and the balance is =C2-F2.

EMI no.EMI (₹)Interest (₹)Principal (₹)Balance left (₹)
15,7621,400.004,362.001,15,638.00
25,7621,349.114,412.891,11,225.11
35,7621,297.634,464.371,06,760.74
245,762about 66about 5,696about 0

Notice that the interest part falls each month while the principal part rises, because interest is charged on a shrinking balance. The EMI itself does not change.

Why check at the three-month mark?

Three EMIs are enough for a mismatch to show up, and few enough that it is easy to fix. Take the closing balance on your lender's statement and compare it with ₹1,06,760.74. A difference of a few rupees can come from the lender's day-count method, the date the EMI was credited, or rounding, so do not chase it. A difference of hundreds is worth a call, and could be an extra fee, a processing charge or a differing rate.

What should you do each month?

  • Enter the date the EMI actually left your bank, not only the due date
  • Mark the row Paid, so late or missed ones stand out
  • Note any extra charge separately instead of folding it into the EMI
  • Update the remaining count: 24 minus the number paid

What if a payment is late or missed?

Record it in the notes column with the date, and look at the next statement to see what the lender charged. Late fees and extra interest depend on your agreement, so check the document rather than guessing. If you often cut it close, move the due date or keep the amount in the paying account a few days early.

When a sheet is not the best tool

Once you have several loans, one sheet per loan gets heavy. SpendixAI can pick up each EMI from your uploaded bank statements, so you can see the debits in one place with the right account kept against each. Keep the schedule check above in either case.

What about fees and part-payments?

Processing fees, insurance premiums charged with the loan and late charges are not part of the EMI. Give each its own row, so your EMI rows match the schedule exactly and your total cost of borrowing is still complete. If you make a part-payment, add a row with its date and amount, then take the new balance from the lender's statement and recalculate the remaining rows with it, because later interest will be lower.

End-of-loan checklist

  • Last EMI entered and balance shows zero or the lender's tiny residual
  • Closing statement requested and filed
  • Standing instruction cancelled
  • Sheet saved with the loan papers
SPENDIXAI JOURNAL

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

Explore your reports