Spreadsheets

Loan amortisation schedule in Excel, built month by month

Build a loan amortisation schedule in Excel with opening balance, interest, principal and closing balance for each month, plus a total row and a zero-balance check.

Editorial illustration for Loan amortisation schedule in Excel, built month by month

A loan amortisation schedule lists every EMI with how much of it is interest and how much reduces the loan. Build it with four columns: opening balance, interest, principal and closing balance. Each month's interest is the opening balance times the monthly rate, principal is the EMI minus that interest, and the closing balance becomes the next month's opening balance.

What do I put in the input cells?

Keep inputs apart from the table:

  • B1 Loan amount, B2 Annual rate, B3 Tenure in months
  • B4 EMI: =PMT(B2/12,B3,-B1)

For a small example you can verify by eye, use ₹1,20,000 at 12% for 12 months. These are test numbers, not an offer. The EMI is about ₹10,661.85.

How do I set up the table?

In row 7 write the headers: A Month, B Opening balance, C EMI, D Interest, E Principal, F Closing balance. Row 8 holds month 1.

  • A8: 1. A9: =A8+1, then fill down.
  • B8: =B1. B9: =F8, then fill down.
  • C8: =$B$4, then fill down. Absolute references keep the EMI cell fixed.
  • D8: =B8*$B$2/12, then fill down.
  • E8: =C8-D8, then fill down.
  • F8: =B8-E8, then fill down.

Select A9:F9 and drag the fill handle until the month column reads 12. For a longer loan, fill down as far as the tenure.

What should the first rows look like?

MonthOpening balanceInterestPrincipalClosing balance
1₹1,20,000.00₹1,200.00₹9,461.85₹1,10,538.15
2₹1,10,538.15₹1,105.38₹9,556.47₹1,00,981.67
3₹1,00,981.67₹1,009.82₹9,652.04₹91,329.63
12₹10,556.29₹105.56₹10,556.29₹0.00

Month 1 interest is 1,20,000 x 1% = ₹1,200, because 12% a year is 1% a month. Principal is 10,661.85 - 1,200 = ₹9,461.85. Notice that interest shrinks each month while principal grows, though the EMI stays the same. That is what reducing balance means.

How do I add totals and the zero check?

Under month 12, add a Total row: C20 =SUM(C8:C19), D20 =SUM(D8:D19), E20 =SUM(E8:E19). Expect the principal total to equal the loan amount, here ₹1,20,000, and the interest total to be about ₹7,942, since 10,661.85 x 12 = 1,27,942.20 and subtracting 1,20,000 leaves the interest.

Now the check. In H1 write =IF(ROUND(F19,2)=0,"Balance reaches zero","Check the schedule"). Rounding to two places avoids a false alarm from tiny fractions. In H2 write =IF(ROUND(E20-B1,2)=0,"Principal matches loan","Principal does not match"). If either message is the bad one, look for a formula typed over or a missing absolute reference.

Can I get the tenure to adjust automatically?

Yes, with a guard. Wrap each row's formulas so rows beyond the tenure stay empty: in A9 use =IF(A8="","",IF(A8>=$B$3,"",A8+1)), and in the other columns start with =IF($A9="","",...). It lets you fill 360 rows once and reuse the sheet for a 5-year or a 20-year loan.

What if my bank's schedule looks different?

Small differences are normal. Banks may compute interest by days, start the first instalment on a particular date, or round each month. Use your sheet to understand the shape, and use the lender's schedule for dates and exact figures. If the two diverge by a large amount, ask the lender to explain. Keep the sheet with your loan papers so you can compare the closing balance at the end of each financial year with the lender's statement.

What to do with it

  • Add a column G for Extra payment and subtract it from the closing balance to test part payments.
  • Use the sum of the first twelve interest cells to see the interest paid in the first year.
  • When EMIs appear on your bank statement, SpendixAI can keep them as their own category, and you can compare the total with this schedule at the end of each year.
SPENDIXAI JOURNAL

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

Explore your reports