Amortization Schedule Calculator

An amortization schedule shows where every loan payment actually goes. Each fixed monthly payment splits into interest on the remaining balance and principal that reduces what you owe, and the split shifts every month: interest dominates early in the loan, principal takes over late. The calculator below builds the complete schedule for any fixed-rate loan: the monthly payment, total interest, payoff date, a month-by-month table, a year-by-year summary, and the exact month when principal first beats interest.

Prefer to keep the schedule in a spreadsheet you own? A section further down rebuilds this same schedule in Excel with the PMT, IPMT and PPMT formulas, so you can model the loan offline, add your own extra-payment columns, and keep every row. The two methods agree to the cent, and this page shows both.

Quick Answer

How to read the schedule, row by row

Each row is one payment: what you paid, how much of it was interest, how much cut the balance, and what is left to pay. Read down the interest column and it shrinks every month; read down the principal column and it grows by roughly the same amount, because the payment itself never changes on a fixed-rate loan.

The balance column is the number borrowers actually act on. It is your payoff figure if you sold or refinanced that month, and the equity you have built is the original loan minus that balance. Your lender shows the same split on your closing documents: the CFPB Loan Estimate explainer walks through where principal, interest and total payment appear on the form, so you can check a real offer against the schedule you build here.

The year-by-year summary under the monthly table compresses the same rows into annual totals. It is the view to use for tax planning and quick comparisons, because it shows a full year of interest without scrolling through twelve rows at a time.

The amortization formula behind every row

The payment comes from one formula, and every row after that is simple subtraction. There is no hidden lender math: if you can run the four lines in the box below, you can rebuild any schedule on paper, in code, or in a spreadsheet.

Two edge cases matter. At a 0% rate the formula divides by zero, so the calculator switches to the obvious fallback: the payment is the loan amount divided by the number of payments, and every payment is 100% principal. That case is common on promotional auto financing, and plenty of online tools reject it outright. At the other end, rounding means the final payment is adjusted by a few cents so the balance lands on exactly $0.00 instead of overshooting below zero.

Enter the contract interest rate from your loan documents, not the APR. As Freddie Mac’s guide to interest rate versus APR explains, the APR folds lender fees into the rate, which makes it the right number for comparing offers and the wrong number for building a payment schedule. The schedule is driven by the rate the lender actually charges on the balance.

Worked example: $300,000 at 6.5% over 30 years

The calculator is pre-filled with this loan, so you can reproduce every number below with one click. The payment is $1,896.20, total interest is $382,633.47, and the total cost of the loan is $682,633.47. Interest alone is 56% of everything you repay.

The first five rows show how slow the start is: after five payments and $9,481.00 paid, the balance has fallen by only $1,370.79. The annual view tells the same story at a glance. Year 1 pays $19,401.27 of interest against $3,353.18 of principal; by year 30 the proportions have flipped to $781.30 of interest against $21,973.15 of principal.

Nothing about the loan changes between those bars. The payment is identical, the rate is identical, and the only moving part is the balance the interest is charged on. That is why the early years feel like running in place, and why the schedule bends so sharply once the balance is genuinely smaller.

The crossover month: when principal passes interest

The crossover month is the first payment where the principal portion beats the interest portion. On the worked example it is payment #233, in year 19: for the first 232 payments, most of every check went to the lender as interest.

The crossover is the most useful single number on the schedule for planning. Before it, extra payments and refinancing do their heaviest work, because the balance they act on is still large. After it, you are mostly paying yourself. If you are weighing a refinance late in a loan, compare the interest left on this schedule with the cost to refinance the mortgage before assuming a lower rate automatically wins: a fresh 30-year schedule restarts the interest-heavy years.

Most calculators never label this month. You would have to scan hundreds of rows and spot the flip yourself, which is why the summary on this page names it directly, with its date when you give the tool a start date.

Extra payments rewrite the schedule

An extra payment goes entirely to principal, because the interest for that month is already fixed by the balance. That makes extras the one lever that changes the schedule itself rather than just the payment: the balance falls faster, next month’s interest is smaller, and the loan ends early. These figures are the calculator’s own output for the worked example:

Two checks before you commit to extras. First, confirm the loan has no prepayment penalty: the CFPB Closing Disclosure explainer shows where that clause appears on a mortgage. Second, tell the lender to apply the extra amount to principal, in writing if the portal is unclear, because an advance on next month’s payment does nothing for your interest. If several debts are competing for the same spare cash, an early debt payoff calculator helps you decide which balance the extra money should hit first.

Build this schedule in Excel with PMT, IPMT and PPMT

Excel builds the identical schedule with three functions: PMT computes the fixed payment, IPMT returns the interest inside any single period, and PPMT returns the principal. Put your inputs in one small block, write one correct row, and fill it down. Everything below uses the same layout: loan amount in B1, annual rate in B2, term in years in B3, payments per year in B4, and the payment itself in B6.

The example loan here is $250,000 at 6% over 30 years, paid monthly. Run it through the calculator above and you get the same figures the formulas return: a $1,498.88 payment, $1,250.00 of interest and $248.88 of principal in period 1, a balance of $249,751.12 after that payment, and $289,595.47 of interest over the full term. Microsoft’s PMT documentation uses the same rate-per-period and number-of-periods logic, which is why the spreadsheet and this page cannot disagree when both are set up correctly.

About the minus signs: Excel’s cash-flow convention treats money you receive as positive and money you pay out as negative, so a plain PMT on a positive loan returns a negative payment. Negating the loan (-B1) flips the result to the positive payment you expect. Google Sheets documents its PMT function with the same arguments and the same convention, so every formula in this section works in a Google sheet unchanged. For the vehicle version of this build, the Excel auto loan calculator and the Excel car payment calculator map the same functions onto car-loan inputs.

Mistakes that break Excel amortization sheets

Four errors account for nearly every broken sheet. Each one produces numbers that look plausible at a glance, which is what makes them dangerous.

Forgetting to divide the rate. PMT expects the rate per period. Feeding it 6% as the monthly rate instead of 0.5% inflates the payment absurdly, and on longer terms the schedule may never reach zero. The rate argument is always annual rate divided by payments per year, matched by years multiplied by payments per year for the count.

Letting the input references drift. If the payment cell or the rate is referenced relatively, filling down drags the reference with it, and by row 40 the sheet is calculating a different loan. Lock every input reference with dollar signs ($B$2, $B$6) and let only the period and balance references move.

Rounding the balance instead of the display. Rounding each row’s balance to cents and feeding the rounded figure forward compounds small errors across 360 rows. Format cells to show two decimals; keep the underlying math at full precision, the same way the calculator on this page works.

Fighting the negative sign. A negative PMT result is the sign convention working as designed, not an error to patch with scattered minus signs. Negate the present value once, in one place, and every function in the sheet reads consistently.

What most amortization calculators miss

Calculator.net, the tool most people land on first, leads with a yearly summary; the monthly rows are there, but the headline view compresses exactly the detail a schedule page exists to show, and nothing labels the crossover month. Bankrate hides its schedule behind a Show Amortization Schedule step inside a mortgage-only flow that steers you toward rate offers. Omni Calculator handles more payment frequencies than anyone, a genuine strength, but offers no spreadsheet build and no crossover. The Excel template sites, Vertex42 being the best known, hand you a downloadable file: useful once it is open, but you see no numbers until you have downloaded and opened it, and the template knows nothing about your loan until you retype it.

Two honest gaps on this page, stated plainly. The extra-payment model here is monthly only, while Calculator.net and Bankrate also model yearly and one-time extras. And there is no CSV export button; the one top tool that offers a download, calculators.im, wins that specific point. What this page does that none of the tools above combine: the monthly table and the annual summary in a single result, the crossover month named in the summary, 0% loans handled correctly, and a full Excel build with the actual PMT, IPMT and PPMT formulas laid out next to the calculator, so the schedule you see on screen is the schedule you can keep.

What this calculator does not model

The schedule covers principal and interest on a fixed-rate loan, and nothing else. It does not add property taxes, homeowners insurance, PMI, HOA dues or lender fees, and it cannot follow an adjustable rate that resets mid-term. Lenders also round and accrue slightly differently, so treat the output as an accurate model of your loan, not a statement: confirm payoff figures with your lender before acting on them. If the loan is a mortgage, the IRS Publication 936 rules decide how much of the interest in the schedule is deductible. This page is educational and is not financial advice.

Frequently asked questions

Add This Calculator to Your Website

References