What an amortization schedule shows you
An amortization schedule is a month-by-month breakdown of your mortgage payment. It shows how much of each payment goes toward interest, how much goes toward principal, and what you still owe after each payment. Most people never see one unless they ask for it, but building one yourself takes about ten minutes and a spreadsheet.
The schedule answers questions that a single payment amount cannot: How much interest will you pay over the life of the loan? When does the balance tip and you start paying down principal faster than interest? What happens to your remaining balance if you pay extra? A lender will provide one when you close, but understanding how it works means you can spot errors and model different payment scenarios on your own.
Key Takeaways
- An amortization schedule breaks down each payment into principal and interest portions, showing your remaining balance after every payment.
- You need four numbers to build one: loan amount, interest rate, loan term in months, and the monthly payment amount.
- The first payment is mostly interest; later payments are mostly principal, because interest is calculated on the shrinking balance.
- You can build a schedule in a spreadsheet using the same formula your lender uses, or use an online calculator to verify your lender's numbers.
The four numbers you need
Before you open a spreadsheet, gather these four pieces of information. Your loan documents or closing disclosure will have all of them.
Loan amount (principal): The total amount you borrowed. If you put down $50,000 on a $300,000 house, your loan amount is $250,000, not $300,000.
Annual interest rate: The percentage rate on your note. This is the rate you locked in, not the current market rate. It appears on your promissory note and closing disclosure.
Loan term in months: A 30-year mortgage is 360 months. A 15-year mortgage is 180 months. Multiply the years by 12.
Monthly payment amount: The principal and interest payment only—not taxes, insurance, or HOA fees. This is the number your lender calculated using the three numbers above. If you already have a payment amount, you can verify it matches the formula. If you do not, you will calculate it first.
Calculate the monthly payment if you do not have it
If your lender has not given you the payment amount yet, or you want to verify it, use this formula. It looks complicated but a spreadsheet does the work.
The formula is: M = P × [r(1 + r)^n] / [(1 + r)^n − 1]
Where:
- M = monthly payment
- P = loan amount (principal)
- r = monthly interest rate (annual rate ÷ 12)
- n = total number of payments (years × 12)
Example: $250,000 loan at 6.5% for 30 years.
- P = 250,000
- r = 0.065 ÷ 12 = 0.00542
- n = 30 × 12 = 360
In a spreadsheet, you would enter this in a cell as: =250000*((0.00542*(1+0.00542)^360)/((1+0.00542)^360-1))
The result is approximately $1,580.17 per month. Your lender's payment amount should match this within a few cents (rounding differences).
Build the schedule row by row
Now that you have the monthly payment, create a spreadsheet with these column headers: Payment Number, Payment Amount, Principal, Interest, and Remaining Balance.
Start with payment 1. Here is what goes in each column:
- Payment Number: 1
- Payment Amount: $1,580.17 (the same every month)
- Interest: Remaining balance × monthly interest rate. For payment 1, that is $250,000 × 0.00542 = $1,355.00
- Principal: Payment amount − interest. That is $1,580.17 − $1,355.00 = $225.17
- Remaining Balance: Previous balance − principal paid. That is $250,000 − $225.17 = $249,774.83
For payment 2, the remaining balance from payment 1 becomes your starting point. Interest is now $249,774.83 × 0.00542 = $1,353.74. Principal is $1,580.17 − $1,353.74 = $226.43. The new remaining balance is $249,774.83 − $226.43 = $249,548.40.
Notice that interest went down by $1.26 and principal went up by $1.26. This pattern continues for the entire loan. Early payments are mostly interest; later payments are mostly principal.
Use formulas to fill the entire schedule
Rather than calculate each row by hand, set up formulas that copy down. In a spreadsheet, your second row would look like this:
| Column | Formula |
| Payment Number | =A1+1 (adds 1 to the previous payment number) |
| Payment Amount | =$1580.17 (use absolute reference so it does not change) |
| Interest | =E1*0.00542 (remaining balance from previous row × monthly rate) |
| Principal | =B2-D2 (payment amount − interest) |
| Remaining Balance | =E1-C2 (previous balance − principal paid) |
Once you have row 2 set up with formulas, select all five cells and copy them down to row 361 (360 payments plus the header row). The spreadsheet will adjust the cell references automatically. Your final remaining balance should be $0 or within a few cents due to rounding.
What the schedule tells you
Once your schedule is complete, you can see patterns that a single payment number hides. In the $250,000 example at 6.5%, the first payment is $1,355 interest and $225 principal. By payment 180 (halfway through), interest and principal are nearly equal. By payment 360, interest is $6 and principal is $1,574.
Total interest paid over 30 years: approximately $318,662. That is more than the original loan amount. If you paid an extra $100 per month, you could recalculate the schedule with a new payment amount and see how many months you would save and how much interest you would avoid.
You can also spot errors. If your lender's schedule shows a remaining balance that does not match yours, ask them to explain the difference. It could be a rounding method, an escrow account entry, or a genuine mistake.
When to verify your lender's schedule
Your lender must provide an amortization schedule at closing or shortly after. Compare it to one you build yourself using the numbers above. They should match exactly or within a few cents per row.
If they do not match, the most common causes are: a different rounding method (some lenders round each payment to the nearest cent, which can shift the final payment), an escrow account for taxes and insurance mixed into the schedule (your schedule should show principal and interest only), or a different interest rate than you thought (check your closing disclosure).
If you cannot explain the difference, contact your lender's loan servicer and ask them to walk you through their calculation. They are required to explain how your payment is divided.
Frequently Asked Questions
Can I use an online calculator instead of building a spreadsheet?
Yes. Many online amortization calculators will generate a full schedule if you enter the loan amount, rate, and term. They are faster than building one yourself, but you cannot modify them as easily if you want to model extra payments or a different scenario. Using both—a calculator to verify your lender's numbers and a spreadsheet to explore what-ifs—is a practical approach.
Why is my first payment mostly interest?
Interest is calculated on the outstanding balance. At the start, you owe the full loan amount, so interest is at its highest. As you pay down principal, the balance shrinks and interest shrinks with it. By the end of the loan, almost all of your payment goes to principal because very little balance remains.
What if I want to pay extra toward principal?
Add a column for extra payments and subtract it from the remaining balance. If you pay an extra $100 in month 1, your new remaining balance is $249,774.83 − $100 = $249,674.83. Recalculate interest on that lower balance for month 2. You can model this for all 360 months to see how much faster you would pay off the loan and how much interest you would save.
Does the amortization schedule change if interest rates change?
Only if you refinance. Your original schedule is locked in at the rate you signed. If you refinance to a new loan, you build a new schedule based on the new rate, new term, and new remaining balance. The old schedule becomes historical—it shows what you actually paid, not what you will pay going forward.
Why does my lender's schedule show a different final payment?
The final payment is often slightly different because of rounding. If you pay $1,580.17 for 359 months, the 360th payment might be $1,580.14 or $1,580.21 to account for cents that accumulated. This is normal and expected. Your lender will show the exact final payment on your schedule.