IPMT Function in Google Sheets: Formula, Examples & Troubleshooting

=IPMT(rate, period, number_of_periods, present_value, [future_value], [end_or_beginning])

Google documents the period range and consistent rate/payment units. The reviewed reference does not state the omitted future-value default. All new IPMT calls below provide future value and timing. Google IPMT reference.

1. In B10 enter =IPMT(B2/B3,B8,B4,B5,B6,B7).
2. Expected signed interest: −60.
3. In B11 enter =PMT(B2/B3,B4,B5,B6,B7). Expected total payment: −531.847323.
4. In B12 enter =B11-B10. Expected principal component: −471.847323.

The two negative components add to the negative payment. IPMT does not return the whole payment or the balance remaining. Use =-B10 in a reporting cell if you want to display the interest expense as positive 60, while retaining the original sign in the model.

For this end-payment, zero-target model, interest equals the opening balance times the monthly rate, with a negative payment sign. Principal equals total payment minus interest; closing balance equals opening balance plus signed principal.

Build the ledger with D2 =B5, E2 =-D2*$B$2/$B$3, F2 =$B$11-E2, and G2 =D2+F2. Set D3 to =G2 and fill D3 down through D4 to carry forward each closing balance. Fill E2:G2 down through E4:G4 for the complete three-period audit. This audit uses unrounded amounts even though the table displays six decimals.

Google’s mortgage example visibly uses =-IPMT(...) with a positive principal of 85,000, monthly rate 4%/12, and 360 periods. It publishes positive displayed interest amounts of 283.33, 282.93, and 282.52 for the first three periods. This supports the signed interest convention used here. Published IPMT mortgage example.

The example’s remaining-balance column contains inconsistent values and later errors. It is not used as evidence for this guide’s balance ledger. The ledger above is independently derived from the stated inputs. No hidden formulas from that published balance column were inspected.

Place 1, 12, and 24 in I2:I4. In J2 enter =IPMT($B$2/$B$3,I2,$B$4,$B$5,$B$6,$B$7) and fill down.

The payment remains constant in this model, but the interest portion shrinks as the balance falls. In K2, =-J2/(-$B$11) gives the interest share of the payment. Compare shares only when numerator and denominator use consistent sign conventions.

This makes a useful audit of an imported amortization report: a constant-payment, constant-rate, steadily declining balance should not have an unexplained increase in the interest component.

Use whole period numbers within 1 through 24 for this example. Period 0 is outside the documented range. Do not assume fractional periods are interpolated. Check the periodic rate, total term, and sign of principal before interpreting an unexpected result.

Although timing 1 is documented, beginning-timing IPMT output and first-period details were not resolved by the reviewed evidence; this guide’s numeric examples deliberately use timing 0. Arbitrary future values, zero/negative rates, and a complete error-code map also remain unverified. Do not generalize the first-period principal*rate shortcut to a different timing model.

Google currently documents IPMT. Its introduction date, earliest functioning version, and any retirement date are unestablished. No other spreadsheet’s edge behavior has been adopted.

Related: SUM, ROUND, and IF.

References checked October 17, 2026: Google IPMT contract, published interest amounts.