IPMT function: calculate the interest portion of any loan payment period

Why early payments carry much more interest than later ones

Even when a fixed-rate loan has the same total monthly payment (PMT) every month, the split between interest and principal shifts over time. IPMT(rate, per, nper, pv, [fv], [type]) returns the interest portion for a single period per (1..nper).

On a $24,000 loan at 6% nominal annual interest (0.5% per month) over 48 months, Month 1 interest is simply -24000 * 0.005 = -$120.00. By Month 25, nearly half the balance is paid down ($12,717.35 remaining), so monthly interest drops to -$63.59.

Assume a constant nominal rate, monthly compounding and payments at month end, with no fees or extra repayments. An effective annual rate must be converted differently; dividing it by 12 is not equivalent.

Match periodic units and verify IPMT + PPMT = PMT

Divide the annual rate by 12 for monthly payments (B2/12). In every valid period, IPMT + PPMT equals total PMT to the penny (-120.00 + -443.64 = -563.64).

=ROUND(IPMT(B2/12, B4, B3, B1), 2)

Guard period numbers outside 1..NPER

A loan period must lie between 1 and NPER inclusive. Wrapping with IF(OR(B4<1, B4>B3), "Invalid period", ...) prevents accidental period 0 or post-maturity lookups.

=IF(OR(B4<1, B4>B3), "Invalid period", ROUND(IPMT(B2/12, B4, B3, B1), 2))

Further reading

• Microsoft Support — IPMT function