Need to break down your loan payments? An amortization schedule helps you see how each payment affects your principal and interest over time. With Excel, you can easily build one yourself.
💡 What is an Amortization Schedule?
An amortization schedule shows how each payment is split between principal (reducing the loan) and interest. It also tracks your remaining balance after each payment.
🛠 Step-by-Step: Build Your Amortization Schedule in Excel
1. Input Loan Details
At the top of your sheet, create input cells for:
- Loan Amount (e.g., $100,000)
- Annual Interest Rate (e.g., 5%)
- Loan Term (e.g., 15 years)
2. Calculate the Monthly Payment
Use Excel’s PMT function:
=PMT(InterestRate/12, LoanTerm*12, -LoanAmount)
This gives the fixed monthly payment amount.
3. Create the Schedule Table
Add columns:
- Payment #
- Payment
- Principal
- Interest
- Balance
4. Add Formulas
For each row:
- Interest = Previous Balance × Monthly Rate
- Principal = Payment – Interest
- New Balance = Previous Balance – Principal
5. Fill Down for All Payments
Drag the formulas to generate the full schedule for the loan term (e.g., 180 months for a 15-year loan).
6. Format for Clarity
- Use currency formatting for amounts
- Bold headers
- Freeze top rows for easier navigation
📥 Download the Ready-to-Use Template
Save time with our prebuilt Excel amortization schedule. Just input your loan details and view the full breakdown instantly.
📥 Download Amortization Schedule Template
🚀 Final Thoughts
Whether for a mortgage, car loan, or business financing, an amortization schedule helps you track payments and interest over time.
Need a version with extra payment tracking or visual charts? I can create a customized one for you!