MS Excel: PPMT function to calculate loans / mortgages / installment
- Fakhriddinbek
- Apr 29
- 2 min read
If you're working with loans, mortgages, or installment plans, Excel’s PPMT function helps break down your payments and show how much of each one goes toward the principal—not just the total payment.

Let's explore how it works step by step, from the basics to more advanced, real-world uses.
The PPMT function calculates the principal portion of a loan payment for a given period, based on constant interest and fixed payments.
Syntax:
=PPMT(rate, per, nper, pv, [fv], [type])
Argument | Description |
rate | Interest rate per period |
per | Payment period (e.g., 1 for first month) |
nper | Total number of periods |
pv | Present value (loan amount) |
fv | [Optional] Future value (default = 0) |
type | [Optional] 0 = end of period (default), 1 = beginning |
Example 1 – Basic Monthly Loan Calculation
Imagine you take a $10,000 loan with 10% annual interest, paid over 12 months.
=PPMT(10%/12, 1, 12, -10000)
Returns –$791.59
This is the principal part of your first monthly payment.
The loan amount is entered as negative (-10000) because it's an outgoing payment (money you owe).
To find the interest part, use the IPMT function:
=IPMT(10%/12, 1, 12, -10000)
To find the total payment, use PMT:
=PMT(10%/12, 12, -10000)
This shows you how much of each payment goes to:
Principal (PPMT)
Interest (IPMT)
Combined (PMT)
Example 2 – View Payment Breakdown Over Time
Set up a payment schedule:
Period | Principal (PPMT) | Interest (IPMT) | Total Payment (PMT) |
1 | =PPMT(rate,1,12,-10000) | =IPMT(rate,1,12,-10000) | =PMT(rate,12,-10000) |
2 | =PPMT(rate,2,12,-10000) | =IPMT(rate,2,12,-10000) | =PMT(rate,12,-10000) |
... | ... | ... | ... |
As months go by:
PPMT increases (you pay more principal)
IPMT decreases (less interest is due)
This reflects how amortization works in real loans.
Advanced Usage: Start Payments at Beginning of Period
Add the [type] argument to make payments at the start of each period:
=PPMT(10%/12, 1, 12, -10000, 0, 1)
This slightly changes the numbers, since interest is calculated differently.
Use Case: Loan Tracker or Amortization Table
Set up a dynamic table using:
PPMT() for each period’s principal
IPMT() for each period’s interest
PMT() for total
A running balance formula:
=Previous Balance - Current Principal
Combine these in Excel with dropdowns or sliders (via Data Validation) to create an interactive loan planner.
Summary Table
Function | Purpose |
PPMT | Principal part of payment |
IPMT | Interest part of payment |
PMT | Total periodic payment |
Final Thoughts
The PPMT function is ideal for:
Financial modeling
Loan tracking
Understanding amortization
Creating dashboards
Use it with PMT, IPMT, and optional arguments (fv, type) for complete flexibility.
Wrap PPMT in ROUND() or ABS() if you want cleaner numbers or positive values.
Comments