How to Create an Amortization Schedule
Learn how to build a loan amortization schedule step by step, showing how each payment is split between interest and principal over the life of a loan.
What Is an Amortization Schedule?
An amortization schedule is a complete table of periodic loan payments showing the breakdown of each payment into its principal and interest components, along with the remaining balance after each payment. The word "amortize" comes from the Latin for "to kill off," reflecting how the schedule shows the loan balance being systematically reduced to zero. Every fixed-rate installment loan — mortgage, auto, personal — follows an amortization schedule.
Step 1: Calculate the Monthly Payment
Start by computing the fixed monthly payment using the formula: M = P[r(1+r)^n] / [(1+r)^n - 1], where P is the principal, r is the monthly interest rate (annual rate ÷ 12), and n is the total number of monthly payments. For a $200,000 mortgage at 6.5% for 30 years: r = 0.065/12 ≈ 0.005417, n = 360, giving M ≈ $1,264.14. This same payment amount applies to every row of the schedule.
Step 2: Calculate the First Month's Interest
For each period, interest is calculated on the outstanding balance: Interest = Balance × Monthly Rate. For the first payment on our $200,000 loan at r = 0.005417: Interest = $200,000 × 0.005417 = $1,083.33. The principal portion is the total payment minus interest: Principal = $1,264.14 - $1,083.33 = $180.81. The new balance is $200,000 - $180.81 = $199,819.19.
Step 3: Repeat for Each Subsequent Period
Each new row uses the previous period's ending balance as the new starting balance, then recalculates interest, principal, and the new ending balance using the same formulas. Because the balance decreases slightly each month, the interest portion of each payment also decreases slightly and the principal portion increases correspondingly. This pattern — growing principal repayment and shrinking interest — is the defining characteristic of amortizing loans.
Front-Loading of Interest in Early Payments
In the early months of a long-term loan, the vast majority of each payment goes toward interest. On a 30-year mortgage, approximately 85% of the first payment is interest. By the halfway point (year 15), the split is closer to 50/50. Only in the final years does the principal portion dominate. This front-loading is why refinancing or selling early in a loan's life means you have paid relatively little of the principal despite years of payments.
Effect of Extra Payments on the Schedule
Any extra principal payment made reduces the outstanding balance immediately, which in turn reduces all future interest calculations. Extra payments do not change the required monthly payment amount — they reduce the number of payments needed to reach a zero balance. Even $100 in extra principal each month on a 30-year $200,000 mortgage at 6.5% saves over $28,000 in interest and shortens the loan by more than 3 years.
Building the Schedule in a Spreadsheet
To create an amortization schedule in Excel or Google Sheets, set up columns for Period, Beginning Balance, Payment, Interest, Principal, and Ending Balance. Row 1 uses your loan values; each subsequent row references the prior row's Ending Balance as its new Beginning Balance. Use the PMT function (=PMT(monthly_rate, total_periods, -principal)) to calculate the fixed payment. Copy the formulas down for all n periods to complete the full schedule.
Try These Calculators
Put what you learned into practice with these free calculators.
Related Guides
How to Calculate Mortgage Payments
Learn how to calculate mortgage payments step by step. Understand the mortgage payment formula, principal vs. interest breakdown, escrow, PMI, and how to use amortization schedules.
How to Calculate Loan Payments
Learn the formula for calculating monthly loan payments on any installment loan, including personal loans, auto loans, and student loans.
How to Calculate APR vs Interest Rate
Understand the difference between APR and interest rate, how APR is calculated to include fees, and why APR is the better metric for comparing loan offers.