Spreadsheets

IPMT and PPMT: split any EMI into interest and principal in Excel

Use IPMT and PPMT in Excel to split any EMI into interest and principal for a chosen month, and add up the interest paid in the first twelve instalments.

Editorial illustration for IPMT and PPMT: split any EMI into interest and principal in Excel

IPMT returns the interest part of a chosen EMI and PPMT returns the principal part, so you can look at instalment number 1, 36 or 120 without building a full schedule. Both use the same arguments as PMT, plus the instalment number. Add the two and you get back the EMI.

What do the arguments mean?

Both functions share one pattern: rate per period, which instalment you mean, total number of periods, present value. For a loan of ₹10,00,000 at 9% for 120 months, set up inputs in B1 (loan), B2 (annual rate) and B3 (months). Then:

  • EMI: =PMT(B2/12,B3,-B1)
  • Interest in instalment n: =IPMT(B2/12,n,B3,-B1)
  • Principal in instalment n: =PPMT(B2/12,n,B3,-B1)

Replace n with a number or a cell, say E1. The loan is entered with a minus sign for the same reason as in PMT: so the results are positive. The instalment number must be between 1 and the tenure; anything else returns #NUM!.

What do the numbers look like for a sample loan?

The EMI on this made-up loan is about ₹12,667.58. Here are three instalments and the last.

InstalmentInterest (IPMT)Principal (PPMT)Total
1₹7,500.00₹5,167.58₹12,667.58
12₹7,057.32₹5,610.25₹12,667.57
60₹4,637.03₹8,030.55₹12,667.58
120₹94.30₹12,573.28₹12,667.58

Instalment 1 follows from simple arithmetic: 10,00,000 x 9% / 12 = ₹7,500. Tiny differences in the last digit come from rounding in the display. Notice how early EMIs are mostly interest and late ones mostly principal. Half-way through the tenure, at instalment 60, principal has already overtaken interest.

How much interest do I pay in the first twelve months?

Build a helper column. Put 1 to 12 in A2:A13, then in B2 write =IPMT($B$2/12,A2,$B$3,-$B$1) and fill down to B13. The first-year interest is =SUM(B2:B13). On the sample loan that comes to about ₹87,377. The principal for the same year is the same pattern with PPMT, about ₹64,634. Together they are 87,377 + 64,634 = ₹1,52,011, which equals 12 EMIs of ₹12,667.58.

If you have Excel 365 or 2021, there is a one-cell version: =SUM(IPMT(B2/12,SEQUENCE(12),B3,-B1)). Older Excel versions do not have SEQUENCE, so use the helper column.

Is there a shortcut for a range of instalments?

Yes. CUMIPMT adds the interest over a range of instalments in a single step, and CUMPRINC does the same for principal. Their syntax is rate, number of periods, present value, first period, last period and type. For the first year write:

=-CUMIPMT(B2/12,B3,B1,1,12,0)

The loan goes in as a positive number here, because CUMIPMT requires it, and the minus sign in front turns the negative result positive. The 0 at the end means payments are at the end of each month. Use it for any window: instalments 13 to 24 for year two, for instance, by changing 1,12 to 13,24.

Why would I need this?

  • To see how much of this month's EMI is really reducing your loan before deciding on a prepayment.
  • To compare the interest portion across two loan offers at the same instalment number.
  • To note down the interest paid in a financial year for your records. For how it is treated for tax, ask your CA, because rules and limits change.
  • To check a lender's statement: if the interest in instalment 1 on your statement is far from IPMT, ask why.

What if the loan has a different EMI date or a moratorium?

IPMT and PPMT assume equal monthly instalments that begin one period after the loan starts. A loan with an interest-only period, a step-up plan or a broken first period will not match. In that case, build the schedule row by row instead, and let IPMT serve as a quick sanity check on the regular part.

Keep the figures with the rest of your records

Interest paid across a year is useful next to your other spending. When you import your loan account statement into SpendixAI, EMIs can sit in their own category, and the yearly total can be compared with the sum of your IPMT cells.

SPENDIXAI JOURNAL

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

Explore your reports