Excel Amortization Schedule Generator

About this template

An amortization schedule is the payment-by-payment story of a loan: how much of each payment is interest, how much comes off what you owe, and what is left afterwards. This workbook builds that table for a loan you describe, and builds it out of formulas so it stays a working model rather than a printout.

It is for anyone holding a loan and wanting to see inside it - a mortgage, a car loan, a personal loan, a business facility - and particularly for testing overpayments, which is the question a lender's own statement never answers. It is a calculator, not a statement: it models a fixed-rate loan with equal payments and does not know about fees, insurance, or a rate that changes partway through.

The sheet opens with four input cells in B3 to B6: loan amount, annual rate, term in years, and payments per year. To their right, three summary formulas give the scheduled payment, the total paid and the total interest. Row 8 heads the schedule with Payment #, Payment, Interest, Principal, Balance and, if you asked for it, Extra payment, and one row follows for every payment in the term - 360 rows for a thirty-year monthly loan.

Everything below row 8 reads the four input cells, so the schedule is not a snapshot of the numbers you typed on this page. Change the rate in B4 after you download and all 360 rows and the summary follow it.

How to use it

1. Change the loan in the sheet, not on this page - B3 to B6 are the only cells you should type in. Loan amount, annual rate, term in years, payments per year - every formula points at them, so the whole schedule re-runs the moment one changes.
2. Enter the rate as a percentage - B4 is percent-formatted, so typing 5.5 gives 5.5% and the cell holds 0.055. Every row divides that annual rate by the payments-per-year cell to get the rate for one period.
3. Type overpayments into the Extra payment column - Put an amount in the Extra payment cell on any row and it comes straight off that period's closing balance. Every later row recalculates from the new balance, so the interest genuinely falls rather than being averaged out.
4. Watch the schedule end early - Once the balance reaches zero the remaining rows go blank instead of showing negative balances, so where the numbers stop is the payoff point. Compare Total interest in E5 before and after an overpayment to see what it saved you.
5. Read the summary block - E3 is the scheduled payment from PMT, E5 the total interest over the schedule, and E4 the total paid - interest, principal and any extra payments added together. Those three are the figures worth comparing between two versions of a loan.
6. Extend the table if you lengthen the term - The workbook is generated with exactly as many rows as the term you chose here. Shortening the term in the sheet is fine - surplus rows blank themselves - but lengthening it needs more rows: select the last row of the schedule and drag its fill handle down, and the formulas continue correctly because every reference is relative.

Formulas in this workbook

Every formula below is written into the workbook itself, exactly as shown. The cell references are the ones the default options produce; change the row or column counts and they shift to match.

• =-PMT(B4/B6,B5*B6,B3) - The scheduled payment. PMT takes the rate for one period (annual rate divided by payments per year), the number of periods (years times payments per year) and the amount borrowed. It returns a negative number because it is money leaving you, so the formula negates it to keep the sheet readable.
• =IF(E9<=0,"",E9*B4/B6) - Interest for a period: the balance carried in from the row above, times the annual rate, divided by the number of payments a year. It is calculated from the running balance rather than with IPMT, which is what keeps it honest once an extra payment has changed that balance.
• =IF(E9<=0,"",B10-C10) - Principal: the payment for the row minus the interest for the row. Whatever the interest does not consume comes off the debt, which is why the principal column grows over the life of the loan while the interest column shrinks.
• =IF(E9<=0,"",MAX(0,E9-D10-N(F10))) - The closing balance: the previous balance, less the principal, less anything typed into the Extra payment cell. N() turns an empty extra-payment cell into a zero rather than an error, and MAX(0, ...) stops the last row overshooting into a negative balance.
• =IF(E9<=0,"",MIN(E3,E9+E9*B4/B6)) - The payment column: the scheduled payment, unless what is left plus its interest is less than that - in which case you only pay what you owe. This is the formula that makes the final payment smaller than all the others.
• =SUM(C9:C368) - Total interest, summing the whole interest column. Total paid uses the same idea across the interest, principal and extra-payment columns, so it reflects what actually leaves your account rather than the original plan.

Customising it

• Add a date column: put the first payment date in the empty column to the right of the schedule and =EDATE(G9,1) beneath it for a monthly loan (or =G9+14 for fortnightly), then fill down to date every row.
• Track interest to date with a running total such as =SUM($C$9:C9) filled down a spare column - it shows how much of the loan's cost you have already met.
• Highlight the crossover point where the principal first exceeds the interest by selecting the schedule and adding a conditional format with the rule =$D9>$C9.
• Compare two loans by copying the sheet and changing one input on the copy - a rate, a term, a deposit - then reading Total interest on both. That is the comparison lenders' calculators rarely put side by side.
• Match a lender's statement to the cent by wrapping the payment cell in ROUND, for example =ROUND(-PMT(B4/B6,B5*B6,B3),2), since real lenders round the instalment before they apply it.