IPMT in Excel: Find One Period’s Interest and Reconcile the Payment

=IPMT(rate, per, nper, pv, [fv], [type])

rate is interest per period. per is the particular period to inspect and must be from one through nper, the total number of periods. pv is the present value. fv defaults to zero, and type defaults to zero for period-end payments; one means beginning-of-period payments.

The model has constant payments and a constant rate. per is an index, not elapsed years or a date. In a three-year monthly model, nper is 36, and per=3 asks about the third month rather than the third year.

Cash-flow signs follow the annuity convention: a positive amount received now normally has negative repayment components. Interest paid is negative from that borrower’s perspective. Reversing every cash-flow sign reverses the components.

Put periodic rate 1%, total periods 12 and initial receipt 1000 in B2:B4. Put period numbers one and two in D2:D3. In E2 enter =IPMT($B$2,D2,$B$3,$B$4,0,0) and fill down.

Independent targets are −10 and −9.2115121132. The constant payment from =PMT(B2,B3,B4,0,0) is −88.8487886783.

After the first payment, the balance is 921.1512113217. Multiplying that balance by 1% gives the second period’s positive interest charge; the IPMT cash-flow component carries the negative payment sign.

For each period, calculate PMT, IPMT and PPMT with the same rate, nper, pv, fv and type. The independent identity is PMT=IPMT+PPMT.

In period one, −88.8487886783 equals −10 plus −78.8487886783. In period two, the principal component is −79.6372765651. As the balance falls, the interest portion declines and the principal portion increases in magnitude under this ordinary positive-rate amortization model.

Do not subtract a positive interest charge from a negative payment and call it PPMT. Either use consistently signed cash-flow components or clearly separate positive charge amounts from negative payments.

Fill the period column through twelve. The independent interest component in period twelve is −0.8796909770, and the principal component is −87.9690977013. Their sum remains the same full payment.

A direct balance recurrence provides a separate check. For an end payment, calculate interest on the opening balance, then apply the payment. The final closing balance approaches zero with full precision. A schedule rounded each period can differ and may need a final adjustment.

With type one, =IPMT(1%,1,12,1000,0,1) has mathematical expected interest 0 because the first payment is immediate. The full beginning payment is approximately −87.9690977013, reducing the initial balance to 912.0309022987.

The next interest portion targets −9.1203090230, calculated on that reduced balance. This example explains the timing rather than asserting a separately captured native result. Mixing type-one payments with type-zero interest components breaks the reconciliation.

Microsoft’s monthly example =IPMT(10%/12,1,3*12,8000) has independent first-interest target −66.6666666667, displayed as −66.67. The annual rate must be divided by twelve because the periods are months.

Validate per in the documented one-through-nper range and use whole period indices for the schedule. The dedicated reference does not enumerate all exact error codes, fractional-period behavior or text coercion; none is certified here.

IPMT is not the interest on arbitrary irregular balances or changing-rate cash flows. Keep all annuity parameters fixed across the component formulas, distinguish per from nper, and preserve full precision while checking totals. The figures are illustrative calculations, not verification of a lender’s statement or of native Excel execution.

• Current documentation: the dedicated Microsoft reference lists Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel 2024, Excel 2024 for Mac, Excel 2021, Excel 2021 for Mac, Excel 2019, Excel 2016.
• Introduced: the first introduction version/build is not established by the dedicated reference used here.
• Last functioning version: unknown; a current help listing is not a last-working-build test.
• Evidence checked: October 15, 2026. Official documentation was freshly retrieved; examples were independently calculated. No native Excel, Mac or web-client execution was performed.

• Microsoft IPMT reference.
• Microsoft function catalog and introduction-marker explanation.
• Editorial testing standards.

Examples use English function names and comma separators. Expected numbers are rounded independent mathematical calculations; documented error expectations are labeled as such. They are not captured Excel output. All illustrative data and policies are fictional.