An education loan is paid out in pieces, so a tracker needs a disbursement log first, an interest estimate for the study period second and an EMI table third. Record each instalment with its date, let a formula count how long it has been outstanding, and you can see the balance that repayment will begin with and the EMI that follows from it.
What goes in the disbursement log?
Start with inputs: B1 Annual interest rate, B2 Repayment start date, B3 Repayment tenure in months. Below, from row 6, list each payout: A Semester, B Date paid by the lender, C Amount, D Paid to (college, hostel, lab), E Months to repayment start, F Interest accrued.
In E7 write =DATEDIF(B7,$B$2,"m"), which counts whole months between the payout and the repayment start. In F7 write =C7*$B$1*E7/12, which is simple interest. In C14 write =SUM(C7:C12) for the total disbursed and in F14 the same for interest.
What does a two-year course look like?
Say the rate is 10% and four disbursements of ₹1,00,000 arrive in months 0, 6, 12 and 18 of the course. Assume repayment starts 30 months after the first payout, which allows a six-month gap after the course. These are made-up figures.
| Semester | Paid in month | Months to repayment | Interest at 10% simple |
|---|---|---|---|
| 1 | 0 | 30 | ₹25,000 |
| 2 | 6 | 24 | ₹20,000 |
| 3 | 12 | 18 | ₹15,000 |
| 4 | 18 | 12 | ₹10,000 |
Each line is 1,00,000 x 10% x months / 12; the first is 1,00,000 x 0.10 x 30 / 12 = ₹25,000. The four lines total ₹70,000, so disbursed principal of ₹4,00,000 plus interest of ₹70,000 gives ₹4,70,000 if the lender adds the interest to the loan when repayment begins.
How do I estimate the EMI at the start of repayment?
Add a Starting balance cell: =C14+F14 if interest is added to the loan, or =C14 if you pay the interest while studying. Then use the PMT function with the monthly rate:
=PMT($B$1/12,$B$3,-B16)
With ₹4,70,000, 10% and 60 months, the EMI is about ₹9,986 and the total repayment about ₹5,99,167, so the interest in the repayment phase is about ₹1,29,167. If you had paid the ₹70,000 yourself during the course, the balance would be ₹4,00,000 and the EMI about ₹8,499. The difference is the cost of letting interest build up, which is why many borrowers service it where they can.
How do I track repayments afterwards?
Copy the amortisation approach on a second tab. Put the starting balance in B2 and use these columns from row 3: A Month, B Opening balance, C Interest =B3*$B$1/12, D EMI, E Principal =D3-C3, F Closing =B3-E3. The first row for the example gives interest of about ₹3,917 and principal of about ₹6,069. Add a Paid? column and mark each month when the debit is seen on your account statement.
If a part payment is made later, add a Prepayment column and subtract it from the closing balance, as in a home loan schedule. A final check cell, =ROUND(F62,0), should read 0 when the table reaches the last month.
What else should the sheet record?
- Processing or other charges paid, with date and mode, in a separate small table.
- Dates and amounts of any interest you pay during the course.
- The lender's reference number and the contact for queries.
- Co-applicant details and the date repayment is due to begin, copied from the letter.
- A link to the sanction letter and each disbursement confirmation.
Keeping the sheet honest
Ask the lender for a statement every semester and compare its outstanding figure and interest with your sheet. Small differences are normal; large ones deserve a question. For tax treatment of education loan interest, ask your CA, because the rules and limits change.
When the loan is repaid, the EMIs show up on the bank statement. Importing those statements into SpendixAI lets you keep the monthly outgo in your usual expense reports.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


