Its calculates the principal payment portion of a fixed payment (annuity) for a specific period of an investment or loan with constant payments and interest rate.
Syntax
PPMT(Rate; Per; Nper; Pv; [Fv]; [Type])
Arguments
| Argument | Requirement | Description |
| Rate | Required | Interest rate per period |
| Per | Required | Period number for which to calculate principal payment (1 to Nper) |
| Nper | Required | Total number of payment periods |
| Pv | Required | Present value (total loan amount) |
| [Fv] | Optional | Future value/remaining balance after last payment (default=0) |
| [Type] | Optional | Payment timing: 0=end of period (default), 1=beginning |
Background
In annuity-based loan repayment:
- Each payment contains:
- Principal portion (increases over time)
- Interest portion (decreases as loan balance reduces)
- First period principal = Payment – (Loan amount × Period interest rate)
- Subsequent principal amounts grow geometrically by:
(First principal) × (1 + Rate)^(Period-1)
Example
Loan Scenario:
- Amount: $176,121.76
- Term: 30 years (360 months)
- Rate: 5.5% annual (0.4583% monthly)
- Payment: $1,000/month
18th Month Calculation:
=PPMT(5.5%/12, 18, 30*12, -176121.76)
Returns: $208.36 principal portion
(Interest portion = $1,000 – $208.36 = $791.64)
Important Notes
- Rounding Considerations:
- Bank calculations use 2 decimal places
- For precise amortization schedules, wrap in ROUND():
=ROUND(PPMT(…), 2)
- Sign Convention:
- Negative Pv returns positive principal (cash inflow to borrower)
- Payment amounts are typically negative (cash outflow)
- Data Validation:
- Ensure Per ≤ Nper
- Match Rate to payment frequency (annual rate/12 for monthly)