Loan repayment with PMT

A loan is a principal amount borrowed for a set period at a set rate of interest, repaid in regular (usually monthly) instalments. A loan repayment schedule is the complete table of those instalments: for each one, how much is interest, how much repays the principal, and the balance still owed, until the loan is fully paid.

The instalment comes from =PMT(rate, nper, pv, fv, type):

• rate: interest rate per period.
• nper: the total number of payments, in the same unit as the rate.
• pv: present value, the loan amount.
• fv: future value, 0 for a loan, because after the last regular instalment nothing is owed.
• type: 0 if paid at the end of each period, 1 at the beginning.

For monthly instalments, use the monthly rate and the number of months. ₹1,00,000 for a TV at 10% over 24 months: =PMT(10%/12, 24, -100000) gives about ₹4,614.49 a month. The book's second example, ₹2,50,000 for a car at 11% over 36 months, comes to about ₹8,184.68 a month.

The schedule's columns are opening balance, instalment, interest, principal repaid and closing balance. Built with formulas, it is a live model: change the rate or the period and the whole schedule updates, which makes planning repayments easy.