Financial
Loan Amortization Schedule
Create a loan amortization schedule that breaks down each payment into principal and interest portions. Track the remaining balance over the life of the loan.
Excel Formula
=PPMT(rate/12, period, total_periods, -principal)Step-by-Step Explanation
1
PPMT returns the principal portion of a given payment
2
IPMT returns the interest portion of a given payment
3
rate/12 converts annual rate to monthly
4
period is the specific payment number (1, 2, 3...)
5
total_periods is the total number of payments
6
Use both PPMT and IPMT together for the full schedule
Example
| Period | Payment | Principal (PPMT) | Interest (IPMT) | Balance |
|---|---|---|---|---|
| 1 | $1,896.20 | $271.20 | $1,625.00 | $299,728.80 |
| 2 | $1,896.20 | $272.67 | $1,623.53 | $299,456.13 |
| 3 | $1,896.20 | $274.15 | $1,622.05 | $299,181.99 |
Result: Principal: =PPMT(6.5%/12,A2,360,-300000) | Interest: =IPMT(6.5%/12,A2,360,-300000)
Common Variations
Interest only
=IPMT(A2/12,B2,C2*12,-D2)Just the interest portion
Cumulative interest
=CUMIPMT(A2/12,B2*12,C2,1,D2,0)Total interest paid through period D2
Need a Custom Version?
Use our AI generator to create a formula tailored to your specific data and requirements.
Try It with AI →