Quick answer
The PPMT formula calculates the principal portion of a payment for a specific period.
=PPMT(B2/12,A2,B3*12,-B4)Syntax
Use this syntax as the pattern, then replace the sample arguments with your own cells, ranges, and criteria.
=PPMT(rate, per, nper, pv, [fv], [type])When to use it
Financial formulas turn business and investment assumptions into numbers you can compare. They are useful for loans, budgets, pricing, margin analysis, investment decisions, and business cases.
- Calculate monthly loan payments
- Estimate return on investment
- Calculate gross margin and markup
- Evaluate investment cash flows
Example worksheet layout
Test the formula in a small worksheet first. This makes it easier to confirm the result before copying the formula down hundreds of rows.
| Column or range | What it represents |
|---|---|
| A | Assumption label |
| B | Input value such as rate, periods, or amount |
| C | Financial result |
Copy-paste examples
These examples move from simple to more practical versions. Start with the beginner examples, confirm the result, then adapt the intermediate and advanced versions.
Basic PPMT example
=PPMT(B2/12,A2,B3*12,-B4)Use the simple version to check the business logic first.
Use locked assumption cells
=PPMT($B$2/12,A2,$B$3*12,-$B$4)Lock assumptions when every row should use the same rate, cost, target, or starting value.
Avoid missing-input errors
=IFERROR(PPMT(B2/12,A2,B3*12,-B4),0)Useful when some rows are incomplete or when a denominator can be zero.
Create a display label
=IFERROR("Amount: "&TEXT(PPMT(B2/12,A2,B3*12,-B4),"$#,##0.00"),"Check inputs")Use TEXT only for a display cell; keep the raw numeric result for calculations.
Use LET for auditability
=LET(result,PPMT(B2/12,A2,B3*12,-B4),result)LET makes financial model formulas easier to review.
Dashboard status label
=IFERROR(IF(PPMT(B2/12,A2,B3*12,-B4)>=0,"Positive","Negative"),"Check inputs")Use a label when the calculation feeds a dashboard status or exception report.
How to use it step by step
- Put your raw data in clear columns with headers such as Amount, Date, Status, Region, or Customer.
- Paste the starting formula into the first result cell, usually row 2.
- Replace sample references such as A2, B2, or $F$2:$F$100 with the cells in your own worksheet.
- Check the result on a few rows where you already know the expected answer.
- Copy the formula down only after the first row returns the right result.
Common mistakes and fixes
- Percentages are entered as whole numbers instead of percentages, such as 12 instead of 12%.
- Cash inflows and outflows use inconsistent signs.
- The result cell is not formatted as currency, number, or percentage as appropriate.
- Test the formula on a few known rows before using it across a full report.
Beginner tips
- Use a small sample first so you can see exactly how the PPMT formula behaves.
- Convert your source range into an Excel Table when the data will grow over time.
- Add a short note near important formulas so future users know what each input represents.