What's the difference between PPMT and IPMT?

PPMT returns the principal paid in a period; IPMT returns interest paid. Together they sum to the total payment (PMT). Over the life of a loan, PPMT increases while IPMT decreases as the balance shrinks.

Why do I need to use a negative pv value?

By accounting convention, loans are represented as negative (cash leaving your account). Using negative pv makes PPMT return a positive principal payment. Positive pv still works but reverses signs, which is unconventional.

Can I use PPMT for investments or savings plans, not just loans?

Yes. PPMT works for any fixed-payment annuity: mortgages, bonds, leases, and structured savings. It separates principal from interest in any regular-payment stream.

How do I build a full amortization table with PPMT?

Create columns for Period, Principal (PPMT), Interest (IPMT), and Total (PMT). Use row references for period numbers and verify PPMT + IPMT = PMT. Array formulas or SUMPRODUCT speed this up across many rows.

PPMT function

Returns the principal payment for a given period of a loan or investment, given constant periodic payments and a constant interest rate.

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

Generate a PPMT formula

Describe what you need. The generator will reach for PPMT where PPMT is the right tool, and tell you when it is not.

Arguments

How PPMT reads its arguments

Argument	Required	Description
rate	Required	The interest rate per period as a decimal (e.g., 0.01 for 1% per month). Required; must be numeric and non-negative.
per	Required	The period number for which to calculate principal payment, ranging from 1 to nper. Required; must be an integer within valid range.
nper	Required	The total number of payment periods in the loan or investment. Required; must be a positive integer.
pv	Required	The present value (initial loan amount or principal). Required; conventionally negative for loans to return positive principal results.
fv	Optional	The future value or remaining balance after all payments; optional, defaults to 0 (fully repaid at end).
type	Optional	Timing of payments: 0 for end of period, 1 for beginning of period. Optional; defaults to 0.

Returns

A number representing the principal portion of the payment for the specified period, expressed in the same currency units as pv.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Find principal paid in first month of a financed order

Order ID	Region	Rep	Units	Unit Price	Order Total	Monthly Rate	Months
001	North	Alice	100	50	5000	0.01	12

=PPMT(0.01, 1, 12, -5000)

Result: 383.26

Order 001 totals $5,000. Financed at 1% monthly over 12 months, the first $433.23 payment contains $383.26 principal and $49.97 interest. Early payments are heavily weighted toward interest due to the large remaining balance.

2. Compare principal in early vs. late payment periods

Period	Principal Paid	Interest Paid	Total Payment	Remaining Balance
1	383.26	49.97	433.23	4616.74
6	416.52	16.71	433.23	2277.71

=PPMT(0.01, 6, 12, -5000)

Result: 416.52

By month 6, the balance has shrunk, so interest charges fall from $49.97 to $16.71, and principal portion rises from $383.26 to $416.52. This demonstrates how amortization schedules front-load interest in early periods.

3. Calculate total principal paid in first half of financing

Order ID	1H Principal Paid	1H Interest Paid	1H Total Payments
001	2400.04	1598.35	3998.39

=SUMPRODUCT(PPMT(0.01,ROW(INDIRECT("1:6")),12,-5000))

Result: 2400.04

Summing PPMT for periods 1–6 shows approximately $2,400 of principal reduction in the first six months, while $1,598 goes toward interest. This illustrates how the first half of a loan's life emphasizes interest over balance reduction.

Common errors

Which PPMT error are you seeing?

Error	Why it happens	How to fix it
#NUM!	The per argument is outside the valid range (less than 1 or greater than nper). Example: =PPMT(0.01, 13, 12, -5000) requests period 13 when only 12 periods exist.	Verify per is between 1 and nper inclusive. Use ROW(INDIRECT("1:12")) or similar array if iterating across all periods.
#VALUE!	An argument is non-numeric or wrong type. Example: =PPMT("1%", 1, 12, -5000) passes rate as text instead of decimal 0.01.	Convert all arguments to numeric format. Use 0.01 for 1%, ensure per/nper/pv/fv are numbers (not text references or dates).
#NUM!	The nper argument is zero or negative. Example: =PPMT(0.01, 1, 0, -5000) specifies zero periods, which is invalid.	Ensure nper is a positive integer ≥ 1. The loan or investment must span at least one payment period.

Tips and when to use something else

PPMT returns principal only; use IPMT to find interest paid, and PMT for the total payment. Remember: PPMT + IPMT = PMT in each period.
Use negative pv (standard accounting convention) to get positive principal results. Positive pv reverses the sign of PPMT output.
Combine PPMT with SUMPRODUCT or array formulas to build full amortization schedules, showing principal/interest for every period at once.
For loans with irregular payments, variable rates, or missing periods, use NPV or XIRR instead of PPMT, which assumes fixed amounts.

Related functions

PMTPMT calculates the constant periodic payment needed to repay a loan or investment over a fixed number of periods at a fixed interest rate.IPMTIPMT returns the interest portion of a loan or investment payment for a specific period with constant payments and interest rate.PVReturns the present value of an investment based on a series of regular payments and a constant interest rate.NPERNPER returns the number of payment periods needed to pay off a loan or reach an investment goal at a fixed interest rate.RATERATE calculates the interest rate per period for an investment or loan given regular periodic payments and present/future values.

Frequently asked questions

What's the difference between PPMT and IPMT?: PPMT returns the principal paid in a period; IPMT returns interest paid. Together they sum to the total payment (PMT). Over the life of a loan, PPMT increases while IPMT decreases as the balance shrinks.
Why do I need to use a negative pv value?: By accounting convention, loans are represented as negative (cash leaving your account). Using negative pv makes PPMT return a positive principal payment. Positive pv still works but reverses signs, which is unconventional.
Can I use PPMT for investments or savings plans, not just loans?: Yes. PPMT works for any fixed-payment annuity: mortgages, bonds, leases, and structured savings. It separates principal from interest in any regular-payment stream.
How do I build a full amortization table with PPMT?: Create columns for Period, Principal (PPMT), Interest (IPMT), and Total (PMT). Use row references for period numbers and verify PPMT + IPMT = PMT. Array formulas or SUMPRODUCT speed this up across many rows.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17