Financial
PPMT Function in Excel
Returns the principal payment for a given period of an investment.
Syntax
- =PPMT(rate, per, nper, pv, [fv], [type])
Arguments
- rate (required): Interest rate per period
- per (required): Period for which to find principal
- nper (required): Total number of periods
- pv (required): Present value
Examples
- =PPMT(5%/12, 1, 60, 25000) - First month principal - Result: -$367.44
Excel PPMT function documentation
- PPMT returns the principal portion of a payment for a specific period in a fixed-rate loan.
- Syntax: =PPMT(rate, per, nper, pv, [fv], [type]) and it complements [IPMT](/functions/ipmt/) plus [PMT](/functions/pmt/).
- Use PPMT to track balance paydown, principal-heavy years, and debt payoff acceleration scenarios.
- Related analysis: [PV](/functions/pv/), [NPV](/functions/npv/), [IRR](/functions/irr/), [Financial functions](/categories/financial/).
PPMT syntax details
- rate must be periodic and aligned with nper frequency.
- per identifies the period number and must be within the amortization horizon.
- pv is the present value (loan amount) and controls sign orientation.
- type is 0 for end-of-period and 1 for beginning-of-period payment timing.
Step-by-step: principal schedule by period
- Step 1 — Build a period column from 1 to nper.
- Step 2 — Add formula =PPMT(rate_per_period, period, nper, pv).
- Step 3 — Fill down to obtain principal portion each month.
- Step 4 — Add IPMT column and confirm total equals [PMT](/functions/pmt/).
- Step 5 — Sum PPMT values to validate total principal repaid equals original pv.
PPMT vs IPMT behavior over time
- In fixed-rate amortization, PPMT usually increases over time.
- IPMT usually decreases as remaining balance declines.
- PMT often stays constant unless rate resets.
- Use both metrics to explain why early loan payments are interest-heavy.
Worked examples to copy
- Principal in payment 1: =PPMT(5%/12,1,360,300000).
- Principal in payment 120: =PPMT(5%/12,120,360,300000).
- Annual model: =PPMT(0.08,3,10,50000).
- Cross-check total payment: =PPMT(rate,per,nper,pv)+IPMT(rate,per,nper,pv).
People also ask
- Why does PPMT change each month? — Interest allocation changes with declining balance.
- Can I use PPMT for credit cards? — Only for fixed-rate fixed-payment assumptions.
- PPMT returns negative values? — Typical outflow sign convention.
- How to compare with project returns? — Pair debt cash flows with [IRR](/functions/irr/) or [NPV](/functions/npv/).
Common errors
- per must be between 1 and nper
Use cases
- Loan amortization
- Principal breakdown
- Payment schedules
Frequently asked questions
- How do I calculate how much principal I've paid after N payments? Use CUMPRINC: =CUMPRINC(rate, nper, pv, 1, N, 0) gives cumulative principal from payment 1 to N. Or sum PPMT: =SUMPRODUCT(PPMT(rate, ROW(INDIRECT("1:"&N)), nper, pv)).
- Why does principal payment increase over time? As you pay down the loan, less goes to interest and more to principal. PPMT increases each period while IPMT decreases, but their sum (PMT) stays constant. By the end, almost all of each payment is principal.
- How do I build an amortization schedule? Create columns for Period (1 to nper), Payment (=PMT), Interest (=IPMT for that period), Principal (=PPMT for that period), and Balance (previous balance + PPMT). Each row shows the breakdown for that payment.
Editorial review
- Reviewed by Excel.Directory Editorial Team. Updated May 2026.
When to use PPMT
- Loan amortization — common Financial scenario for PPMT.
- Principal breakdown — common Financial scenario for PPMT.
- Payment schedules — common Financial scenario for PPMT.
PPMT in the Financial category
- Browse all Financial functions at /categories/financial/ for related formulas.
- PPMT syntax: =PPMT(rate, per, nper, pv, [fv], [type])
- rate (required): Interest rate per period
- per (required): Period for which to find principal
- nper (required): Total number of periods
- pv (required): Present value
- Confirm PPMT arguments match the syntax shown above before filling down.
- Lock table and range references with $ when copying formulas across rows or sheets.
Formula checklist before you copy down
- Confirm PPMT arguments match the syntax shown above before filling down.
- Lock table and range references with $ when copying formulas across rows or sheets.
- If results look wrong, check for text stored as numbers and invisible spaces with TRIM.
- Spot-check three known input rows manually against expected output.
Related Excel functions
- IPMT (/functions/ipmt/): Returns the interest payment for a given period of an investment.
- PMT (/functions/pmt/): Calculates the payment for a loan based on constant payments and a constant interest rate.
- CUMPRINC (/functions/cumprinc/): Returns the cumulative principal paid between two periods.
Errors to watch for
- per must be between 1 and nper — review causes on linked error pages in the directory.
Copy-paste audit workflow
- Enter PPMT on three test rows with known expected output documented on a QA tab.
- Fill down only after absolute references are locked on lookup tables and rate tables.
- Compare against manual calculation or a calculator for financial and statistical functions.
- Search this directory for comparison guides when choosing between similar functions in the same category.
PPMT worked examples to copy
- =PPMT(5%/12, 1, 60, 25000) — First month principal. Expected result: -$367.44.
PPMT reference summary for crawlers and offline review
- PPMT belongs to the Financial category in Excel. Returns the principal payment for a given period of an investment.
- Full syntax: =PPMT(rate, per, nper, pv, [fv], [type]). Open /functions/ppmt/ for parameters, FAQs, and related pages.
- Common mistakes: per must be between 1 and nper
- Pair this function with comparison guides when another Excel formula might fit the same task better.
- Review fix-excel-formula-errors when unexpected errors appear after upgrading Excel or sharing across locales.