A home loan tracker is a sheet with one row per month showing opening balance, EMI, interest, principal, any extra payment and closing balance. It works in Excel and Google Sheets with a handful of formulas, and you check it against your lender's statement. Below is a layout using a hypothetical loan of ₹25,00,000.
Set up the inputs
- Cell B1: loan amount, 2500000.
- Cell B2: yearly interest rate as a percentage, 8.5%, typed so that the cell holds 0.085.
- Cell B3: number of months, 240.
- Cell B4: the EMI, with the formula =ROUND(-PMT($B$2/12,$B$3,$B$1),0). This gives 21,696 for the example.
PMT returns a negative number for a loan, so the minus sign in front makes it positive. Your lender's EMI may differ by a rupee or two because of rounding, so if it does, type your lender's EMI into B4 as a plain number.
Build the monthly rows
Put these headings in row 6: Month, Opening, EMI, Interest, Principal, Extra, Closing. Then, from row 7:
- A7: 1, and A8: =A7+1, copied down.
- B7: =B1 for the first month. From B8 onwards: =G7, copied down.
- C7: =$B$4
- D7: =ROUND(B7*$B$2/12,0)
- E7: =C7-D7
- F7: the extra payment, typed only in the month you make one.
- G7: =B7-E7-F7
What do the first rows look like?
| Month | Opening | EMI | Interest | Principal | Extra | Closing |
|---|---|---|---|---|---|---|
| 1 | ₹25,00,000 | ₹21,696 | ₹17,708 | ₹3,988 | 0 | ₹24,96,012 |
| 2 | ₹24,96,012 | ₹21,696 | ₹17,680 | ₹4,016 | 0 | ₹24,91,996 |
| 3 | ₹24,91,996 | ₹21,696 | ₹17,652 | ₹4,044 | 0 | ₹24,87,952 |
| 4 | ₹24,87,952 | ₹21,696 | ₹17,623 | ₹4,073 | ₹50,000 | ₹24,33,879 |
The extra ₹50,000 in month 4 is invented. Notice that it reduces the closing balance on top of that month's principal, and from month 5 onward the interest is lower because the opening balance is lower.
How many months are left?
Below the table, use NPER on the latest closing balance. If month 4 is in row 10, the formula is =NPER($B$2/12,-$B$4,G10). It returns about 224 months for the example, against 236 remaining on the original schedule, so the extra payment has shortened the loan by about 12 months. Excel returns a decimal, so round it up when you read it.
What if the rate changes?
On a floating-rate loan the lender may change the rate, and either the EMI or the tenure moves. Add a Rate column to the sheet, type the rate that applied in each month, and make the interest formula refer to that cell instead of $B$2, for example =ROUND(B7*H7/12,0) if the rate sits in column H. Then use the EMI your lender actually charges in column C rather than the formula result, and keep the lender's letter about the change with your records.
What totals should you keep?
- Interest paid so far: =SUM($D$7:$D$246). In the example, the first four months give ₹70,663.
- Extra paid so far: =SUM($F$7:$F$246).
- Principal repaid: =SUM($E$7:$E$246)+SUM($F$7:$F$246).
Where do the real figures come from?
Your lender's statement lists each EMI with the interest and principal split, any part-payment and the balance. Copy those numbers into the sheet rather than trusting the formulas, and use the formula rows only to see what the schedule should look like. If your sheet and the statement disagree by more than a rupee or two, find out why, since rounding is the usual cause and a missed or misapplied payment is the one to catch.
When a sheet stops being enough
A tracker like this covers one loan well. If you want your EMIs, extra payments and the rest of your spending categorised in one place, SpendixAI can import the statement of the account you pay from and let you keep extra principal in its own category. The loan balance itself you still copy from the lender's statement.
Short closing list
- Inputs entered and EMI matched to the lender's figure.
- Statement numbers copied each month.
- Closing balance compared with the lender's balance.
- Extra payments entered only in the month they were applied.
- Months left checked with NPER after any part-payment.
Loan terms and tax treatment change, so confirm details with your lender and your CA.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


