What an amortization schedule shows you
An amortization schedule is a month-by-month breakdown of your mortgage payment: how much goes toward principal (the amount you borrowed), how much goes toward interest, and what you still owe. It answers the question most borrowers have: "Why am I paying so much interest at the start, and when does that flip?"
The schedule exists because of how mortgage interest works. Your lender calculates interest on whatever balance remains. In month one, that balance is nearly the full loan amount, so interest is high and principal paydown is low. As you pay down principal, the interest portion shrinks and the principal portion grows. A full amortization schedule shows this shift for every single payment over the life of the loan.
You do not need a lender to build one. The math is straightforward, and a spreadsheet or calculator can do it in seconds. Understanding how to read one—or build one yourself—tells you exactly when you will own a meaningful piece of your home, what happens if you pay extra, and whether refinancing makes sense.
Key Takeaways
- An amortization schedule breaks down each payment into principal and interest, showing how your loan balance shrinks over time.
- The first step is gathering four numbers: loan amount, interest rate, loan term in months, and monthly payment amount.
- Each row uses the same formula: interest for that month equals the remaining balance multiplied by the monthly interest rate, and principal equals the payment minus that interest.
- You can build a schedule in a spreadsheet in under five minutes, or use an online calculator if you only need to see a few months or the final payoff date.
- Comparing schedules side by side shows the real cost of paying extra principal or refinancing to a shorter term.
The four numbers you need to start
Before you calculate anything, gather these four pieces of information from your loan documents or your lender:
Loan amount (principal): The total amount you borrowed. If you put down 20% on a $300,000 house, your loan amount is $240,000. This is the starting balance in your schedule.
Interest rate: The annual percentage rate (APR) on your mortgage. Your loan documents will state this clearly—for example, 6.5%. You will convert this to a monthly rate by dividing by 12.
Loan term: The number of years you have to repay the loan, usually 15, 20, or 30 years. Convert this to months by multiplying by 12. A 30-year mortgage is 360 months.
Monthly payment: The amount you pay each month (principal plus interest, not including property tax or insurance). Your lender provided this number when you locked the rate. If you do not have it, you can calculate it using the payment formula, but most borrowers find it on their loan estimate or closing disclosure.
The formula for each month's breakdown
Every row in an amortization schedule follows the same three-step logic:
- Calculate interest for that month: Take the remaining balance at the start of the month and multiply it by the monthly interest rate. If your balance is $240,000 and your annual rate is 6.5%, your monthly rate is 6.5% ÷ 12 = 0.541667% (or 0.00541667 as a decimal). Interest = $240,000 × 0.00541667 = $1,300.
- Calculate principal for that month: Subtract the interest from your monthly payment. If your payment is $1,520 and interest is $1,300, principal = $1,520 − $1,300 = $220.
- Calculate the new balance: Subtract the principal payment from the old balance. New balance = $240,000 − $220 = $239,780.
That new balance becomes the starting balance for next month. Repeat this process for every month of the loan. In month two, you calculate interest on $239,780, not $240,000. Because the balance is slightly lower, the interest is slightly lower, and the principal portion is slightly higher. This pattern continues for the entire loan.
Building a schedule in a spreadsheet
Open a spreadsheet (Excel, Google Sheets, or any similar tool) and set up columns for: Month, Beginning Balance, Payment, Interest, Principal, and Ending Balance.
In row one, enter your starting values. Month = 1. Beginning Balance = your loan amount. Payment = your monthly payment (this stays the same every row). Interest = Beginning Balance × (Annual Rate ÷ 12). Principal = Payment − Interest. Ending Balance = Beginning Balance − Principal.
In row two, Beginning Balance = the Ending Balance from row one. Copy the formulas down from row one, and the spreadsheet will automatically adjust the cell references. If your loan is 360 months, copy the formulas down 360 rows. The final Ending Balance should be $0 (or very close, within a few cents due to rounding).
Most spreadsheets let you copy a formula down by selecting the cell, then dragging the small square at the bottom-right corner down as far as you need. Alternatively, select the range of cells where you want the formula, then use Ctrl+D (Windows) or Cmd+D (Mac) to fill down.
What the schedule reveals about your payoff timeline
Once you have built the schedule, scan the Principal column. In the first few years, principal payments are tiny—often less than $300 per month on a $240,000 loan. Interest dominates. This is why paying extra principal early in the loan saves so much money: every extra dollar goes straight to reducing the balance, which immediately lowers next month's interest.
Halfway through the loan (month 180 on a 30-year mortgage), the split is usually close to 50-50. By the final year, principal is nearly the entire payment and interest is minimal. This is also why refinancing a 30-year mortgage into a new 30-year mortgage after 10 years does not save as much as people expect—you have already paid most of the interest, and starting a new 30-year clock means paying interest on a smaller balance for a longer time.
Use the schedule to find the payoff date if you pay extra. If you pay $1,700 instead of $1,520, the loan ends years earlier. Build a second schedule with the higher payment and compare the two. The difference in total interest paid is your savings.
When to use a calculator instead of building your own
If you only need to see a few specific months or want to know the total interest paid without building the full schedule, an online amortization calculator is faster. Enter your loan amount, rate, and term, and it generates the schedule instantly. Most are free and do not require any personal information.
Calculators are also useful for testing scenarios. Want to see what happens if you refinance to a 15-year loan? Enter the new numbers and compare. Want to see the effect of a $200 extra payment? Most calculators have a field for that.
The downside of a calculator is that you cannot easily modify it or save your own versions for comparison. If you are serious about understanding your mortgage or planning payoff strategies, building your own spreadsheet takes 10 minutes and gives you full control.
Common mistakes when building a schedule
The most frequent error is forgetting to convert the annual interest rate to a monthly rate. If you use 6.5 instead of 0.00541667, your interest calculations will be wildly wrong. Always divide the annual rate by 12 before multiplying by the balance.
The second mistake is rounding too early. If you round the monthly interest rate to 0.0054 instead of keeping more decimal places, small errors compound over 360 months and your final balance will not be zero. Let your spreadsheet carry full precision and only round the display if you want to see dollars and cents.
The third mistake is entering the payment as a formula instead of a fixed number. Your payment does not change month to month (unless you have an adjustable-rate mortgage, which requires a different approach). Enter it as a constant value so it stays the same in every row.
Frequently Asked Questions
Why is my first payment mostly interest?
Interest is calculated on the full loan balance at the start. On a $240,000 loan at 6.5%, the first month's interest alone is about $1,300. Your payment might be $1,520, leaving only $220 for principal. As the balance shrinks, interest shrinks with it, and principal grows.
Does paying extra principal really shorten the loan?
Yes. Every extra dollar you pay reduces the balance immediately, which lowers next month's interest and speeds up payoff. A $200 extra payment per month on a 30-year mortgage typically cuts 5 to 7 years off the loan and saves tens of thousands in interest. Your amortization schedule will show the exact difference.
What if my interest rate changes mid-loan?
If you have an adjustable-rate mortgage (ARM), your rate changes on a set date. Build the schedule in two parts: one for the fixed-rate period using the original rate, and a second schedule starting from the new balance when the rate adjusts. Your lender will tell you the new rate and new payment amount.
Can I use the schedule to decide whether to refinance?
Yes. Build a schedule for your current loan showing the remaining balance and interest yet to be paid. Then build a schedule for the refinanced loan at the new rate and term. Subtract the refinance closing costs from the interest savings. If the savings exceed the costs, refinancing makes financial sense.
What if my final balance is not exactly zero?
Rounding errors of a few cents are normal over 360 months. Your lender will adjust the final payment by a few dollars to bring the balance to exactly zero. If your final balance is off by more than a dollar or two, check that you converted the annual rate to a monthly rate and that you did not round intermediate calculations.