PPMT Function

Summary

The Excel PPMT function returns the principal payment for a given period of a loan or investment based on constant payments and a constant interest rate. Microsoft defines it as the principal component of one payment, which makes it different from functions that return the full payment or the interest portion.

That distinction matters in amortization work. A fixed payment is split between interest and principal, and the mix changes over time. PPMT isolates only the amount that actually reduces the outstanding balance in the selected period.

PPMT is therefore useful when the model needs one period's balance-reduction amount rather than the full payment amount or a cumulative total across several periods.

Syntax

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

rate is the interest rate per period, per is the selected period number, nper is the total number of periods, and pv is the present value. Optional fv and type work the same way they do in other time-value functions.

Microsoft notes that the period number must be between 1 and nper. The time unit also has to stay consistent. If the model is monthly, use a monthly rate and monthly period numbering.

Arguments

• rate - Interest rate per payment period.
• per - The specific payment period to calculate.
• nper - Total number of payment periods.
• pv - Present value, or original balance.
• fv - [Optional] Ending balance after the last payment.
• type - [Optional] Payment timing: 0 for end of period, 1 for beginning of period.

PPMT usually returns a negative number when the loan amount is entered as a positive present value. That negative sign reflects cash paid out from the borrower's perspective and is consistent with Excel's standard sign convention.

PPMT vs Other Functions

PPMT is part of the payment-split family, so it is best understood alongside PMT, IPMT, and CUMPRINC.

Use PPMT when the question is about a single payment period. Use CUMPRINC when the question is about a longer window.

Using the PPMT Function

PPMT is especially useful in amortization schedules, where each payment needs to be split into interest and principal. Early in the loan, the principal portion is small because interest still consumes most of the payment. Later in the loan, the principal portion becomes much larger.

That changing split makes PPMT valuable in debt analysis, payoff planning, and loan reporting. It helps explain how the payment mix evolves even when the payment amount itself remains constant.

• Use PPMT when you need principal for one selected period.
• Keep the rate and period units aligned.
• Interpret the sign correctly before comparing the result to a target.

Example 1 - First-Month Principal

This formula returns the principal portion of the first monthly payment on a 30-year loan. Because the loan is just beginning, the result is relatively small in absolute value compared with later periods.

This is a useful starting example because many beginners assume the first payment mostly reduces the loan. PPMT shows that early in a long loan, only a small part of the payment actually cuts down the balance.

=PPMT(0.05/12,1,360,100000)

Example 2 - Final-Month Principal

Calculating month 360 shows the opposite end of the amortization schedule. By the final payment, very little interest remains, so most of the payment is principal.

That makes the example useful as a contrast with Example 1. Together, the two examples show how the payment mix changes from interest-heavy to principal-heavy over time.

=PPMT(0.05/12,360,360,100000)

Example 3 - Equity Threshold Check

This logical test checks whether the first month's principal portion is more negative than -120. The negative comparison is intentional because PPMT typically returns a negative cash outflow.

So the example is not only about the principal amount itself. It also shows how to turn that amount into a quick review rule for minimum debt reduction or equity buildup.

=PPMT(0.05/12,1,360,100000)<-120

Example 4 - Equity-to-Payment Ratio

Wrapping the principal amount in ABS removes the sign and makes it easier to express the principal share as a positive percentage of the full payment. That ratio is a compact way to show how much of one payment is actually building equity.

This is helpful because a percentage often explains the payment split better than a raw dollar number. The example makes it easier to see how much of the payment is really going toward ownership.

=ABS(PPMT(0.05/12,1,360,100000))/536.82

Conclusion Recap

PPMT is useful when you want to look inside one payment and see how much of it actually reduces the loan balance. In this lesson, that meant comparing early and late payment periods, testing the principal amount against a threshold, and expressing the principal share as part of the total payment.

The key idea is that PPMT answers a one-period principal question. It does not give the whole payment and it does not give total principal across many months. It shows the balance-reducing part of one selected payment, which makes it useful for amortization detail.

• Summary: PPMT returns the principal component of a specific payment period.
• Syntax: =PPMT(rate,per,nper,pv,[fv],[type]).
• Core setup: Keep time units aligned and expect a negative result under the standard loan sign convention.
• Best use: Amortization schedules, payment-split analysis, and equity-build reporting.