Debt and cash Updated November 2026

PMT, IPMT and PPMT

=PMT(rate, nper, pv) returns the constant payment that repays a loan over nper periods at the given rate per period. =IPMT(rate, per, nper, pv) is the interest part of that payment in period per, and =PPMT(rate, per, nper, pv) is the principal part; IPMT + PPMT = PMT.

Syntax

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

Part	What it is
`rate`	Interest rate per period: annual rate / 12 for monthly payments
`nper`	Total number of payments
`pv`	Loan amount (entered positive gives a negative payment)
`per`	The period for IPMT and PPMT, from 1 to nper

Examples

Loan 100,000, 5% annual, 10 annual payments
=PMT(5%, 10, 100000)

Result: -12,950.46

Interest and principal in year 1
=IPMT(5%, 1, 10, 100000)  =PPMT(5%, 1, 10, 100000)

Result: -5,000.00 and -7,950.46

Year 2 split
=IPMT(5%, 2, 10, 100000)  =PPMT(5%, 2, 10, 100000)

Result: -4,602.48 and -8,347.98

Interest is 5% of the remaining 92,049.54.

Monthly mortgage: 200,000 at 6% over 30 years
=PMT(6%/12, 360, 200000)

Result: -1,199.10

Common mistakes

Using the annual rate with monthly periods: rate must be per period, so 6%/12 with nper 360.
Negative result surprise: Excel returns payments with the opposite sign to pv; enter -pv or wrap in -PMT().
Changing per in IPMT without changing nper, or using a per beyond nper, which gives #NUM!.

Practice PMT, IPMT and PPMT

These drills check your answer on live data, free, no account needed.

Monthly payment for three loansWork out the level monthly payment for each loan with PMT. The rates are annual and the terms are in years, so convert both to months. Write E5 and fill down to E7. Show the payment as a positive number.
Split payments with IPMT and PPMTFor the months listed in column A, split the monthly payment into interest (IPMT, column B) and principal (PPMT, column C) without building a full table. Column D adds them back up. Show all three as positive numbers.
Quarterly amortization tableBuild the full amortization table for a loan repaid in equal quarterly payments over the quarters in B3. Fill all five columns for quarters 1 to 8: opening balance, payment (PMT), interest on the opening balance, principal (payment less interest) and closing balance. Write row 6, then fill down.

Questions

What is the difference between PMT and PPMT?

PMT is the whole payment; PPMT is only the principal portion for one period. Early on, most of PMT is interest, so PPMT is small and grows each period.

How do I get the total interest on a loan?

Total payments minus principal: =PMT(...)*nper + pv, or sum IPMT over all periods with =CUMIPMT(rate, nper, pv, 1, nper, 0).

Amortization tableA loan amortization table shows opening balance, payment, interest, principal and closing balance per period. How to build it with PMT, exact rows, mistakes.
Debt scheduleA debt schedule rolls each loan forward: closing = opening + draws - repayments, with interest on the opening balance. Layout, example, amortization, sweeps.
Interest on the opening balanceInterest = rate x opening balance avoids a circular reference; rate x average balance is more accurate but circular. Both formulas, an example, when to use.
DSCRDSCR = cash flow available for debt service / (interest + principal). How lenders compute it, exact examples, typical covenant levels and sizing debt from it.