PMT, IPMT and PPMT: Loan Functions in Excel

The PMT function in Excel returns the fixed periodic payment on a loan with a constant rate. Its companions IPMT and PPMT split that payment into the interest portion and the principal portion for any given period. Together they let you build a full amortization schedule. The two things that trip people up are the sign convention and converting an annual rate to a periodic one.

How PMT, IPMT and PPMT work

=PMT(rate, nper, pv, [fv], [type]) computes the level payment that pays off a present value pv over nper periods at a per period rate. Because pv is money you receive, the payment comes back as a negative number under Excel's cash flow sign convention.

=IPMT(rate, per, nper, pv) returns just the interest part of the payment in period per, and =PPMT(rate, per, nper, pv) returns the principal part. For any period the two add up to the total: IPMT + PPMT = PMT.

The single most common mistake is feeding an annual rate and a monthly term into the same formula. If the rate is annual and payments are monthly, divide the rate by 12 and multiply the years by 12: =PMT(rate/12, years*12, pv).

Worked example: a 5 year auto loan

Take a 25,000 loan at a 6 percent annual rate, repaid monthly over 5 years. The periodic rate is 0.06/12 = 0.005 and the number of periods is 5*12 = 60.

1. Put the rate in B1 as 6%, the years in B2 as 5, and the loan amount in B3 as 25000.
2. In B4 write =PMT(B1/12, B2*12, B3). The result is about -483.32, negative because it is cash leaving you.
3. For the interest in month 1, write =IPMT(B1/12, 1, B2*12, B3), which returns -125.00, since 25000 times 0.005 is 125.
4. For the principal in month 1, write =PPMT(B1/12, 1, B2*12, B3), which returns about -358.32.
5. Confirm the split: -125.00 plus -358.32 equals -483.32, matching the PMT result.

Interest falls and principal rises each period while the total payment stays constant.

How analysts use them in models

In a financial model these three functions usually appear together to build and tie out a debt schedule.

• Use PMT once to set the level payment, then drive a monthly cash flow row from it.
• Use IPMT to feed the interest expense line of the income statement and PPMT to reduce the debt balance on the balance sheet.
• Flip the sign with a leading minus, as in =-PMT(rate/12, nper, pv), when you want payments shown as positive outflows in a summary.
• Set the optional type argument to 1 for annuity due, where payments fall at the start of each period rather than the end.
• Use the fv argument to model a balloon payment or a residual value left at the end of the term.

Pitfalls to watch

The biggest error is a units mismatch between rate and term. An annual 6 percent with a 60 month term produces a wildly wrong payment unless you convert the rate to 0.06/12. Keep rate and nper on the same period.

The sign convention surprises people. Because pv is positive cash in, PMT, IPMT and PPMT all come back negative. This is correct, not a bug. Decide on one convention for your whole model and stick to it.

Hardcoding the rate directly inside the formula, such as =PMT(0.005, 60, 25000), hides your assumptions. Put the rate, term, and principal in their own labeled cells so a reviewer can find and change them in one place.