PPMT Function in Excel
Master the PPMT function to calculate principal payments on loans in Excel and Google Sheets with practical examples and error solutions.
Excel
Google SheetsExcelGoogle Sheets=PPMT(rate, per, nper, pv, [fv], [type])Quick Answer
PPMT function PPMT function calculates the principal portion of a specific loan payment based on periodic, constant payments and a constant interest rate. Unlike PMT which returns the total payment amount, PPMT isolates just the principal portion—the part that actually reduces your loan balance.
=PPMT(rate, per, nper, pv)- rate - the interest rate per period (e.g.
- Unlike PMT which returns the total payment amount, PPMT isolates just the principal portion—the part that actually reduces your loan balance
Practical Examples
Calculate First Month Principal Payment
Determine how much of your first payment reduces the loan balance
Track Principal Payment Over Time
See how principal payments increase as loan matures
Mortgage Principal in Year 1
Calculate total principal paid in the first year of a mortgage
Car Loan with Balloon Payment
Calculate principal when loan has a remaining balance
Business Equipment Loan Principal
Calculate principal payment for equipment financing
Student Loan Principal with Grace Period
Calculate principal after interest-only grace period ends
Common Errors and Solutions
PPMT cannot calculate principal payment
The per argument is either less than 1 or greater than nper, which is outside the valid payment period range.
1. Check that the per argument is at least 1 2. Ensure per doesn't exceed nper (total payments) 3. Verify you're using the correct payment number 4. If calculating multiple periods, make sure your reference cell doesn't go beyond the loan term
Use data validation on payment period cells to restrict values between 1 and the total number of payments. For example: =AND(A1>=1, A1<=B$3*12) where B$3 contains years.
Example:
Value used in formula is wrong data type
One or more arguments contain text instead of numbers, dates are formatted incorrectly, or cells contain errors that propagate to PPMT.
1. Verify all input cells contain numbers, not text 2. Check that percentage rates are formatted as decimals (0.05) or divided properly (5%/12) 3. Remove any currency symbols or text from input cells 4. Use VALUE() function to convert text numbers: =PPMT(VALUE(A1)/12, ...) 5. Check for hidden characters or spaces in cells
Format input cells as numbers before entering data. Use =ISNUMBER() to test if cells contain valid numeric values. Wrap PPMT in IFERROR to handle unexpected text inputs gracefully.
Example:
Division by zero error
The rate argument is exactly -1 (or -100%), which creates an impossible mathematical scenario where the loan would never be paid off.
1. Check that the interest rate is positive and realistic (typically 0% to 20%) 2. Verify you're dividing annual rate by 12 correctly (not multiplying) 3. Ensure the rate cell isn't empty, which Excel interprets as 0 4. For zero-interest loans, use a very small rate like 0.0001% instead of 0%
Add data validation to rate cells: minimum 0%, maximum 30%. Use IFERROR to catch this edge case: =IFERROR(PPMT(...), "Invalid rate")
Example:
Formula returns unexpected principal amount
Common causes include: (1) Using annual rate instead of periodic rate, (2) Using years instead of total periods for nper, (3) Forgetting to make pv negative, (4) Inconsistent time periods between rate and nper.
1. Always divide annual rate by compounding periods: =PPMT(5%/12, ...) not =PPMT(5%, ...) 2. Multiply years by 12 for monthly payments: nper = 5*12, not 5 3. Make loan amount negative: -10000, not 10000 (or accept negative result) 4. Match periods: if rate is monthly (÷12), nper must be in months (×12) 5. Verify with PMT: PPMT + IPMT should equal PMT for any period
Create a validation formula: =ABS(PPMT(rate,per,nper,pv)+IPMT(rate,per,nper,pv)-PMT(rate,nper,pv))<0.01 to confirm PPMT+IPMT equals total payment.
Example:
Best Practices and Pro Tips
Create Dynamic Amortization Schedules
Build a complete payment schedule by combining PPMT with IPMT and PMT. In cell D8, use =PPMT($B$2/12, A8, $B$3*12, -$B$4) where A8 increments from 1 to total periods. Add IPMT in the next column and PMT for total. This creates a professional loan analysis.
Use Absolute References for Loan Parameters
When building payment schedules, use absolute cell references ($B$2) for rate, nper, and pv so you can copy the formula down without these values changing. Only the per argument (payment number) should be relative (A8) to increment automatically.
Understand Sign Conventions
PPMT follows Excel's cash flow convention: money you receive is positive, money you pay is negative. For a loan you receive (pv), use negative to get positive principal payments (money leaving your account). This can be confusing—test with small numbers first!
Combine with CUMPRINC for Cumulative Analysis
While PPMT shows principal for one period, use CUMPRINC to calculate total principal paid across multiple periods. This is useful for tax reporting (e.g., total principal paid in a calendar year) or analyzing payoff progress.
Validate Results with PMT Cross-Check
Always verify your amortization schedule: PPMT + IPMT should equal PMT for every period. Add a validation column: =ABS(PPMT(...)+IPMT(...)-PMT(...))<0.01. If this returns FALSE, there's an error in your formula setup.
Model Extra Principal Payments
To see the impact of additional principal payments, you can't simply add to PPMT—you need to adjust the remaining balance and recalculate. Better approach: use a running balance column and Goal Seek to find the payment amount that reaches $0 at your desired payoff date.
Need Help with PPMT Function in Excel?
Stop struggling with formula syntax. Use AskFormulas to generate validated formulas instantly with our AI-powered tool.
Example Excel formula:
Related Formulas
The FV function calculates future value of investments with constant payments and interest rate. Master retirement planning and savings goals with examples.
ExcelSheetsThe IPMT function calculates interest portion of loan payments. Learn syntax, examples, and solutions in Excel and Google Sheets.
ExcelSheetsThe PMT function calculates periodic payments for loans with constant payments and interest rate. Master loan calculations with examples.
ExcelSheetsThe PV function calculates the present value of an investment or loan with constant periodic payments and a constant interest rate. Master PV with examples,...
ExcelSheets