How to Build an Amortization Chart in Excel (with Examples)An amortization chart (often called an amortization schedule) shows how loan payments are applied to principal and interest over time, and how the loan balance changes with each payment. Building one in Excel lets you visualize repayment, track interest costs, and plan payoffs or extra payments. Below is a step-by-step guide, with formulas, a worked example, and tips for making a clear, useful chart.
What you’ll need
- Microsoft Excel (or Google Sheets — formulas are the same).
- Loan details: principal (loan amount), annual interest rate, loan term (years), and payment frequency (monthly is most common).
Overview of the process
- Set up input cells for loan parameters.
- Calculate the fixed periodic payment using the PMT function.
- Build the schedule rows showing payment number, payment amount, interest portion, principal portion, and remaining balance.
- Create a chart visualizing principal vs. interest or balance over time.
- Optionally add scenarios (extra payments, different rates) and summary metrics.
Step-by-step: create the amortization schedule
1) Set up input cells
In a new sheet, reserve a small block for inputs. Example cell layout:
- A1: “Loan Amount” — B1: 200000
- A2: “Annual Interest Rate” — B2: 4.5%
- A3: “Loan Term (Years)” — B3: 30
- A4: “Payments per Year” — B4: 12
- A5: “Start Date (optional)” — B5: 2025-09-01
Make these bold or color them to indicate user-editable inputs.
2) Compute derived values
Below or beside inputs compute:
- Periodic interest rate: in B6 enter =B2/B4
- Total number of payments: in B7 enter =B3*B4
- Periodic payment (fixed): in B8 enter:
= -PMT(B6, B7, B1)The negative sign makes the payment positive since PMT returns a negative cash flow by default.
3) Build the schedule table headers
Starting in row 10 (for example), create headers:
- A10: “Payment #”
- B10: “Payment Date” (optional)
- C10: “Beginning Balance”
- D10: “Payment”
- E10: “Interest”
- F10: “Principal”
- G10: “Ending Balance”
- H10 (optional): “Cumulative Interest”
Format headers bold and freeze panes to keep visible while scrolling.
4) Populate row 1 of the schedule (first payment)
Row 11 will represent payment 1.
- A11: Payment number = 1
- B11: If using dates: =EDATE(\(B\)5, A11-1*(12/\(B\)4)) — simpler for monthly: =EDATE(\(B\)5, A11-1)
- C11 (Beginning Balance): = \(B\)1
- D11 (Payment): = \(B\)8
- E11 (Interest): = C11 * \(B\)6
- F11 (Principal): = D11 – E11
- G11 (Ending Balance): = C11 – F11
- H11 (Cumulative Interest): = E11
Note: If the final payment causes a small negative balance due to rounding, you can adjust the last payment to exactly zero out the balance (see tips).
5) Fill down for remaining payments
For row 12 (payment 2) and down, use formulas that reference the prior row:
- A12: =A11+1
- B12: =EDATE(\(B\)5, A12-1) (for monthly)
- C12: =G11
- D12: = \(B\)8
- E12: = C12 * \(B\)6
- F12: = D12 – E12
- G12: = C12 – F12
- H12: = H11 + E12
Select the row and drag down through the number of payments (B7). Excel will auto-fill and compute each row.
6) Handle the final payment rounding
Because of rounding, after filling down you may see a tiny remaining balance in the final row. Two options:
- Adjust the final payment cell to =Cfinal + InterestFinal so Ending Balance becomes zero.
- Use the ROUND function for balances/payments (e.g., ROUND(value, 2)) to ensure cents.
Example: 30-year $200,000 mortgage at 4.5% (monthly)
Using inputs:
- Loan Amount = 200,000
- Annual Rate = 4.5%
- Term = 30 years
- Payments/year = 12
Derived:
- Periodic rate = 0.045/12 = 0.00375
- Total payments = 360
- Monthly payment (PMT) ≈ $1,013.37
First row calculations (rounded):
- Beginning balance: $200,000.00
- Interest (month 1): 200,000 * 0.00375 = $750.00
- Principal: 1,013.37 – 750.00 = $263.37
- Ending balance: 199,736.63
After 360 months the loan balance is zero and total interest paid ≈ $164,813.20 (sum of interest column).
Create charts to visualize amortization
Two useful charts:
- Principal vs. Interest per payment (stacked area or stacked column)
- Select Payment #, Principal, Interest columns.
- Insert → Stacked Column or Area.
- This shows how interest decreases and principal increases over time.
- Remaining Balance over time (line chart)
- Select Payment # and Ending Balance.
- Insert → Line Chart.
- This shows debt declining to zero.
Format axes (e.g., show every 60th payment tick for clarity), add data labels or a trendline if desired.
Variations and extras
- Extra payments: add a column “Extra Payment” and include it in the Payment or apply directly to Principal calculation to show accelerated payoff.
- Biweekly payments: set Payments per Year to 26; adjust Date formula to =B5 + (A11-1)*14.
- Interest-only period: set Principal paid to 0 for first N payments, then recalculate remaining schedule with remaining term/principal.
- Compare scenarios: create side-by-side schedules or use a pivot table to summarize total interest and payoff date.
Tips and common pitfalls
- Use absolute references (\(B\)1, \(B\)6) for input cells so formulas copy correctly.
- Format money with two decimals and use commas for thousands.
- Freeze header row and set column widths before charting.
- For very large loans or long terms, Excel floating-point rounding might leave a tiny final balance—round intermediate values to cents.
- If you want dynamic charts, convert the schedule range to an Excel Table (Insert → Table); charts will then update automatically when you change inputs.
Quick formula reference
- Periodic rate: =AnnualRate/PaymentsPerYear
- Number of payments: =Years*PaymentsPerYear
- Payment: =-PMT(periodicRate, totalPayments, principal)
- Interest for period: =BeginningBalance * periodicRate
- Principal for period: =Payment – Interest
- Ending balance: =BeginningBalance – Principal
This gives you a complete amortization chart in Excel plus ways to visualize and adapt it. If you want, I can generate a ready-to-download .xlsx example or show the exact cell formulas for a Google Sheets copy.
Leave a Reply