Loan amortization schedule
Advanced Financial AnalystA loan amortization schedule breaks down each payment into its principal and interest components, showing how the loan balance decreases over time. This is essential for understanding loan costs, comparing financing options, and financial planning.
The three key financial functions for amortization are:
- PMT: Calculates the fixed periodic payment amount
- PPMT: Returns the principal portion of a specific payment
- IPMT: Returns the interest portion of a specific payment
All three functions use the same core arguments: rate per period, number of periods, and present value (loan amount as a negative number).
Your tasks:
Calculate the summary values (column B, rows 10-12):
- Monthly payment using PMT
- Total payments over the loan term
- Total interest paid
Build the amortization table (rows 15-20):
For each period, calculate:
- Payment (reference the monthly payment you calculated)
- Principal portion using PPMT with the period number from column A
- Interest portion using IPMT with the period number from column A
- Remaining balance (subtract cumulative principal from loan amount)
Important: Convert the annual rate to monthly by dividing by payments per year. Use absolute references for loan parameters so formulas can be extended down the table.
Need some help?
Hint 1
For PMT syntax: =PMT(rate, nper, pv) where rate is annual rate divided by 12, nper is years times 12, and pv is the loan amount as a negative.
Hint 2
PPMT and IPMT need the period number as their second argument. Reference the period number in column A so the formula works when copied down.
Hint 3
For the balance column: Period 1 balance is loan amount minus period 1 principal. Subsequent periods subtract their principal from the previous balance.
Loan amortization schedule
Advanced Financial AnalystA loan amortization schedule breaks down each payment into its principal and interest components, showing how the loan balance decreases over time. This is essential for understanding loan costs, comparing financing options, and financial planning.
The three key financial functions for amortization are:
- PMT: Calculates the fixed periodic payment amount
- PPMT: Returns the principal portion of a specific payment
- IPMT: Returns the interest portion of a specific payment
All three functions use the same core arguments: rate per period, number of periods, and present value (loan amount as a negative number).
Your tasks:
Calculate the summary values (column B, rows 10-12):
- Monthly payment using PMT
- Total payments over the loan term
- Total interest paid
Build the amortization table (rows 15-20):
For each period, calculate:
- Payment (reference the monthly payment you calculated)
- Principal portion using PPMT with the period number from column A
- Interest portion using IPMT with the period number from column A
- Remaining balance (subtract cumulative principal from loan amount)
Important: Convert the annual rate to monthly by dividing by payments per year. Use absolute references for loan parameters so formulas can be extended down the table.
Need some help?
Hint 1
For PMT syntax: =PMT(rate, nper, pv) where rate is annual rate divided by 12, nper is years times 12, and pv is the loan amount as a negative.
Hint 2
PPMT and IPMT need the period number as their second argument. Reference the period number in column A so the formula works when copied down.
Hint 3
For the balance column: Period 1 balance is loan amount minus period 1 principal. Subsequent periods subtract their principal from the previous balance.