Mastering Loan Amortization: PMT, IPMT & PPMT
Loans are more than a single monthly payment. To correctly record interest expense and principal reduction in your books, you must break that payment apart.
The Essential Trio
Every loan payment has two parts: Interest (the cost of borrowing) and Principal (the reduction of the debt). Use these functions to build your schedule:
=PMT(rate, nper, pv): Calculates the total monthly payment.=IPMT(rate, per, nper, pv): Calculates the interest portion for a specific period.=PPMT(rate, per, nper, pv): Calculates the principal portion for a specific period.
The Logic of an Amortization Schedule
To record payroll or loan entries, you need a schedule where the interest expense declines each month as the principal is paid down:
| Period | Payment | Interest | Principal | Balance |
|---|---|---|---|---|
| 1 | $500 | $100 | $400 | $9,600 |
| 2 | $500 | $96 | $404 | $9,196 |
Accounting Implementation
When you make the payment, your journal entry is:
Debit: Interest Expense (IPMT result)
Debit: Notes Payable (PPMT result)
Credit: Cash (PMT result)
Debit: Notes Payable (PPMT result)
Credit: Cash (PMT result)
Common Mistakes
- Ignoring Interest Rates: The
ratemust be the periodic rate. If you have an annual interest rate of 6%, use6%/12for monthly payments. - Negative Values: Excel treats money going out (payments) as negative numbers. Use
-PMT(...)if you want a positive payment amount. - Period Errors: The
perargument for IPMT/PPMT must change every row (1, 2, 3...) as you go down the schedule.