Enter a loan to see the payment, total interest and payoff date, then download the full schedule as .xlsx. Every row is a formula on the loan inputs, so changing the rate or term in Excel recalculates the whole table.
100% private. The schedule is worked out in your browser. Nothing you type is sent to our servers.
This amortization schedule Excel generator turns a loan amount, rate and term into an .xlsx file with one row per payment, each split into interest and principal, all of it live formulas. Say you have a $250,000 mortgage at 6.5% over 30 years, paying $1,580.17 a month, and you're weighing whether to add $200. The schedule answers it: the loan clears 95 payments early and costs $97,618.44 less in interest.
Everything is worked out in your browser. Nothing you type is sent to a server, and the form isn't saved between visits.
The loan details sit in yellow input cells at the top of the sheet. Change any of them and every row recalculates, along with the summary. Checked in Excel for Mac: changing the rate from 6.5% to 7% moved the payment to $1,663.26 and changed every row.
| Cell or column | What it holds |
|---|---|
| B3 to B6 | Loan amount, annual rate, term in years, payments per year |
| B7 | Number of payments, =ROUND(B5*B6,0) |
| B8, B9 | First payment date, extra paid with each payment |
| Summary | Scheduled payment, payments made, payoff date, total interest, total paid, and Left after the last row |
| Schedule | #, Date, Opening balance, Payment, Principal, Interest, Extra, Closing balance, one row per scheduled payment |
The scheduled payment is Excel's own PMT, rounded to the cent: =ROUND(PMT(B4/B6,B7,-B3),2). Monthly, quarterly, twice-yearly and yearly dates step by whole months with EDATE, so a loan paid on 31 January pays next on 28 February. The header row and the inputs are frozen above the rows. For printing, the sheet is set to portrait, one page wide, with the header row repeated on every page.
Amounts round to the currency's smallest unit, so a yen loan works in whole yen. There are 14 currencies.
Each row's interest is the opening balance times the periodic rate, which is what a lender does:
=ROUND(C13*$B$4/$B$6,2)
// C13 this row's opening balance
// $B$4 annual rate
// $B$6 payments per year, so B4/B6 is the rate per paymentPrincipal is then the payment minus that interest. In the first month of the example loan, =ROUND(250000*6.5%/12,2) gives $1,354.17 of interest, leaving $226.00 to reduce the balance.
Most Excel tutorials use IPMT and PPMT here instead. Those two functions calculate from the original schedule, so once an extra payment drops the balance faster than they assume, IPMT goes on charging interest on money you've already paid back. With no extra payments, the two methods agree. At payment 40 the row shows $1,301.17 interest and $279.00 principal; IPMT returns 1301.165 and PPMT 279.0051. They part ways the moment you overpay.
The extra in B9 is capped at what is left after that period's principal, so the balance stops at exactly zero and rows after the payoff go blank. Without that cap, a home-made schedule goes on subtracting the extra after the loan is paid: the closing balance goes negative in the last year, and the total interest is wrong with it. In the $200-a-month example the file pays the loan off at payment 265, and the lowest closing balance in the sheet is $0.00.
The last payment is set to exactly what is owed, so it can differ from the others by a few cents. Rounding every row to the cent otherwise leaves a few cents over at the end of a schedule, or makes the last payment pay too much.
One extra amount applies to every payment. For a one-off lump sum, such as a bonus in March, type it over that row's Extra cell (column G). That replaces the row's formula and its cap, so keep the lump sum no larger than that row's balance.
Choosing "Every two weeks" sets B6 to 26 and spreads the same term across 26 payments a year. The loan still runs its full term. A 30-year loan becomes 780 payments of =ROUND(PMT(6.5%/26,780,-250000),2), which is $728.97.
The biweekly plans lenders advertise work differently: you pay half the monthly payment every two weeks. Twenty-six half payments make 13 monthly payments a year, and the thirteenth is what shortens the loan. To model that here, choose every two weeks and enter the difference as the extra payment:
=ROUND(1580.17/2,2)-728.97
// 790.09 half the monthly payment
// 728.97 the biweekly payment over the full term
// = 61.12 enter this as the extra paymentFor the example loan the schedule then ends after 628 payments, about 24 years, and saves $73,164.03 in interest. Treat that as a close estimate: the file charges the annual rate divided by 26 on each two-week period, while a lender may credit biweekly payments once a month or accrue interest daily. Biweekly dates step 14 days at a time, which is 364 days for 26 payments, so the payment day slides about a day earlier each year.
Interest here is per period: the annual rate divided by the payments per year, charged on the balance. Many car loans and some personal loans accrue interest daily instead, so a payment made three days late costs more interest and the split shifts. A per-period schedule can't follow that.
The payment is principal and interest only. A mortgage statement usually adds property tax and insurance held in escrow, and sometimes PMI, so the amount you pay the lender is higher than the scheduled payment in this file. Payments fall at the end of each period, which is how an ordinary mortgage or car loan works.
Excel ships loan templates: File > New, then search for amortization. Check that the table reaches your last payment before you rely on its totals.
To build one from a blank sheet:
That gets a plain schedule. Extra payments are where hand-built versions break: without a cap, the balance runs negative in the final year.
If the payment is going into a household plan, the budget template generator has a Rent or mortgage category with planned and actual rows for each month.
Put the loan amount, annual rate, term and payments per year in input cells, work out the payment with PMT, then add one row per payment. Each row takes interest as the opening balance times the periodic rate, principal as payment minus interest, and closing balance as opening minus principal. The generator on this page writes that workbook for you, with the inputs in yellow cells you can change afterwards.
The payment is PMT: =PMT(annual rate/payments per year, number of payments, -loan amount), so a $250,000 loan at 6.5% over 30 years, paid monthly, is =PMT(6.5%/12,360,-250000), which rounds to $1,580.17. Each row then needs interest, the opening balance times the rate per payment, and principal, the payment minus that interest. The minus sign in front of the loan amount makes the payment come out positive.
Add an Extra column and subtract it from the balance along with the principal, and cap it so the balance can't go below zero. In this file, the extra payment cell B9 is added to every payment and capped automatically. With $200 extra a month, a $250,000 loan at 6.5% over 30 years is paid off in 265 payments instead of 360.
Because they assume the loan follows its original schedule. Once an extra payment has lowered the balance, IPMT keeps reporting interest on a balance you no longer owe. Working interest out from each row's opening balance gives the same answer when there are no extra payments and the right answer when there are.
Type the lump sum into the Extra cell of the row where you make the payment. That replaces the row's formula, including the cap that stops the balance going negative, so keep the amount no larger than that row's balance. Every row below recalculates from the new balance.
Not in this file. One rate cell drives every row, so it models a fixed-rate loan only. An adjustable-rate mortgage needs a rate per row and a new payment worked out at each reset.
Yes. In Excel, go to File > New and search for amortization to see Microsoft's loan templates. If a template shows fewer rows than your loan has payments, extend its table down to the last payment.