Financial
PPMT function: calculate principal paid down in a specific loan period
Measure how much loan principal each installment retires—and verify that summing all periods recovers the exact original principal.
Track balance reduction period by period
On a balance sheet, only the principal portion of a loan payment reduces the liability—interest is an expense. PPMT(rate, per, nper, pv, [fv], [type]) computes the principal retired in period per.
For an $18,000 equipment loan at 8% nominal annual interest over 36 months, total PMT is -$564.05 and Month 1 interest is -$120.00, so Month 1 principal repayment is -$444.05. By Month 36, principal grows to -$560.32.
Assume a constant nominal rate, monthly compounding and payments at month end, with no fees or extra repayments. An effective annual rate must be converted differently; dividing it by 12 is not equivalent.
| Parameter (A) | Value (B) |
|---|---|
| Equipment Loan (PV) | 18000 |
| Annual Rate | 0.08 |
| Term Months (NPER) | 36 |
| Target Period (PER) | 1 |
Calculate monthly principal and zero-interest loans
When Annual Rate is 0%, PPMT divides principal evenly across all periods (-18000 / 36 = -500.00).
=ROUND(PPMT(B2/12, B4, B3, B1), 2)Guard period bounds 1..NPER
Always keep per inside 1..nper with IF(OR(B4<1, B4>B3), ...) so schedule rows beyond loan maturity do not project phantom payments.
=IF(OR(B4<1, B4>B3), 0, ROUND(PPMT(B2/12, B4, B3, B1), 2))Try the example
Use the original values or change them to test your understanding.