PPMT and IPMT

Most borrowers know only one number about their loan: the EMI. That number hides the thing that actually matters.

An EMI is two payments glued together. Part of it pays down the money you borrowed. The rest is rent on the money you still owe.

Only the first part reduces your debt. The second part is gone.

Two spreadsheet functions pull the EMI apart. PPMT gives the principal part. IPMT gives the interest part. You name the instalment you want, and they tell you the split for that one.

Add the two answers together and you get the EMI back. That is a useful check.

Early in a loan the interest part is large, because the balance is large. Late in the loan it is small. The amortisation schedule is just these two functions run for every month.

Arguments, in the workbook's own order. Both functions take the rate for each period, the period you want the break-up for, the total number of periods, and the loan amount entered as a negative number.

• PPMT(0.07/12, 1, 120, -1000000) returns Rs 5,777 — the principal repaid in instalment 1.
• IPMT(0.07/12, 1, 120, -1000000) returns Rs 5,833 — the interest in instalment 1.

That is the workbook's own case: a Rs 10,00,000 loan at 7%, repaid over 10 years in monthly instalments with a monthly rest. PMT gives the whole EMI of Rs 11,610, and 5,777 + 5,833 = 11,610.

Divide the rate, multiply the periods. 7% a year becomes 0.07/12 for a monthly instalment, and 10 years becomes 120 periods. Getting one of the two right and the other wrong is the usual arithmetic failure.

What the interest rate does to the split. The workbook runs the same loan at two other rates, and the EMI is not the interesting part:

The workbook's reading of it: at the lower rate a larger part goes towards capital repayment right from the initial EMI, and at the higher rate a higher total interest cost means more goes towards interest right from the first payment.

Principal part of instalment n: PPMT(rate per period, n, total periods, −loan amount)

Interest part of instalment n: IPMT(rate per period, n, total periods, −loan amount)

Check: PPMT(...) + IPMT(...) = PMT(rate per period, total periods, −loan amount)

For a monthly EMI on an annual rate r over y years: rate per period = r ÷ 12, total periods = y x 12.

Mr Deshpande borrows Rs 10,00,000 at 7% for 10 years, monthly. His EMI is Rs 11,610.

Instalment 1. PPMT gives Rs 5,777 of principal; IPMT gives Rs 5,833 of interest. So in his very first payment, more than half the money does nothing to his debt. He pays Rs 11,610 and his balance falls to Rs 9,94,223.

Now the decision this arithmetic is actually for. His lender offers to refinance at 5%, with a processing charge. Should he?

The EMI saving is Rs 1,004. But the principal repaid in month 1 rises by Rs 663, and the interest falls by Rs 1,667. He is both paying less and clearing more.

And the mirror case. If his loan had been written at 9% instead, his EMI would be Rs 12,667 and only Rs 5,167 of it would touch the principal, against Rs 7,500 of interest. Compared with the 5% loan he would pay Rs 2,061 more each month and clear Rs 1,273 less of what he owes.

That is the number an adviser should put in front of a client considering a longer tenor or a costlier lender: not the EMI, but how little of the EMI is actually theirs.

Chapter 4 (Debt Management and Loans), section 4.11 (Repayment schedules with varying interest rates), which follows loan restructuring at 4.10 and precedes the criteria for evaluating loans at 4.12. Expect a computation: given a loan, a rate and a tenor, find the principal or interest component of a named instalment; and a conceptual question on what happens to the split when the interest rate changes. The workbook's own figures — 5,777 and 5,833 on a Rs 10 lakh loan at 7% for 120 months — are the ones most likely to appear.

• PPMT is principal, IPMT is interest. The extra P is for principal. Swapping them is the single commonest slip.
• The second argument is the instalment number, not the tenor. PPMT(0.07/12, 1, 120, −10,00,000) is month 1 of 120. Putting 120 there gives the last month.
• Adjust rate and periods together. 0.07/12 with 120 periods. Never 0.07 with 120, and never 0.07/12 with 10.
• Enter the loan as a negative number, as the workbook does, or the sign of the answer flips.
• PPMT + IPMT = PMT, for every instalment. If they do not add up to the EMI, an argument is wrong.
• A lower rate shifts the mix, not just the total. At 5% the first instalment repays Rs 6,440 of capital; at 9% it repays Rs 5,167. The EMI difference understates the damage a high rate does.
• The EMI is constant; the split is not. Do not assume the month-1 split holds later in the loan — that is exactly why the functions take a period number.