Skip to content

Amortization Schedule Templates

    Six free amortization schedule templates in Microsoft Excel, for mortgages, car loans and any other loan repaid in equal instalments. A monthly payment figure tells you what leaves your account. An amortization schedule tells you where it goes — and on a long loan, the answer is startling for the first two decades.

    • Schedules6 workbooks
    • FormatMicrosoft Excel
    • FormulasBuilt in and editable
    • CostFree, no signup

    These are calculators, not financial advice. The figures they produce depend entirely on the numbers you type in. Your lender’s own schedule is the authoritative one — use a template to understand a loan or to check a statement, not as the basis for a decision you cannot reverse. We are not financial advisers.

    What a schedule shows that a payment calculator doesn’t

    A payment calculator answers one question: how much per month. A full schedule lists every payment over the life of the loan and splits each one into interest and principal, so it answers the questions that actually matter.

    • How much of each payment reduces the debt. Early on, very little does. Interest is charged on the outstanding balance, and at the start the balance is the whole loan.
    • What the loan costs in total. On a long loan at a normal rate, the interest can exceed the amount borrowed.
    • When you cross the halfway mark. Not halfway through the term — considerably later.
    • What an extra payment is worth. Overpayments come straight off the principal, so they stop all the future interest that balance would have generated. This is the one calculation most borrowers never run.
    • What the balance will be on a given date. Needed if you are planning to sell, refinance or settle early.

    A worked example

    A $250,000 loan over 30 years at 6.5% interest, repaid monthly. Not a prediction about rates — just round numbers to show the shape of the thing. The monthly payment is $1,580.17.

    What the schedule shows Figure
    Interest in payment number 1 $1,354.17
    Principal in payment number 1 $226.00 — 14.3% of the payment
    Interest paid in year 1 $16,167.73
    Principal repaid in year 1 $2,794.31
    First payment where principal exceeds interest Payment 233, in year 20 of 30
    Point at which half the loan is repaid Payment 257, in year 22 of 30
    Total interest over the full term $318,861
    Total paid $568,861 on $250,000 borrowed

    Two things in that table surprise people. The interest comes to more than the sum borrowed. And three quarters of the way through the term, more of each payment is still going to the lender than to the debt. That is not a feature of a bad loan; it is how equal-instalment repayment works on any long term at any meaningful rate. Shorten the term or lower the rate and the lopsidedness shrinks, but it never disappears.

    The templates

    Six workbooks with the formulas already in place. Each download is a zip file — extract it before opening, as Excel will not calculate properly from inside a compressed folder.

    What overpaying actually saves

    Same loan as above. An extra amount added to every monthly payment, applied to the principal:

    Extra per month Loan paid off in Total interest Interest saved
    Nothing 30 years $318,861 —
    $100 25 years 4 months $260,001 $58,860
    $200 22 years 1 month $221,243 $97,618
    $500 16 years 3 months $155,345 $163,516

    $100 a month — 6% more than the required payment — removes nearly five years and $58,860 of interest. The leverage is this large because the extra comes off the balance immediately, and every future interest charge is calculated on that smaller balance. The effect is strongest in the early years, which is also when it is hardest to afford.

    For comparison, taking the same loan over 15 years instead of 30 means a payment of $2,177.77, which is $597.60 a month more, and total interest of $141,998 instead of $318,861. Whether a shorter term or a longer term with overpayments suits you better depends on how much flexibility you want, and that is a question for you and your lender rather than a spreadsheet.

    One practical warning. If you overpay, check how your lender applies the money. Some servicers hold an unexplained extra payment against the next instalment, or treat it as paying ahead, rather than reducing the principal — which produces almost none of the saving above. Many require you to designate it as principal-only, in writing or through a specific option when you pay. Ask before you start, and check the next statement to confirm the balance moved.

    Checking the numbers yourself in Excel

    Excel has the whole of loan amortization built into five functions. Worth knowing even if you only use the templates, because it lets you verify what a workbook is doing rather than trusting it.

    Formula What it returns
    =PMT(rate/12, years*12, -principal) The level monthly payment.
    =IPMT(rate/12, period, years*12, -principal) The interest portion of one specific payment. Put the payment number in period.
    =PPMT(rate/12, period, years*12, -principal) The principal portion of that same payment. IPMT plus PPMT always equals PMT.
    =CUMIPMT(rate/12, years*12, principal, start, end, 0) Total interest between any two payment numbers — use 1 and 12 for the first year, or 1 and 360 for the lot.
    =NPER(rate/12, -payment, principal) How many payments a given amount would take. This is the one that answers “what if I pay $1,780 instead of $1,580?”

    Two things trip people up. The rate argument must be the periodic rate, so divide an annual rate by 12 for monthly payments — entering 6.5% where Excel expects a monthly figure produces an answer that is wrong by an enormous margin. And Excel uses a sign convention where money paid out is negative, which is why the principal is entered as a negative number above; otherwise your payment comes back negative and every total looks like a credit.

    To build the schedule itself, lay out a row per payment with columns for opening balance, payment, interest, principal and closing balance. Interest for the row is the opening balance times the monthly rate. Principal is the payment minus that interest. The closing balance is the opening balance minus the principal, and becomes the next row’s opening balance. A correct schedule ends on a closing balance of zero, which is the first thing to check in any template — including ours. If the last row leaves a few dollars outstanding, the rounding is not being handled, and the final payment should absorb the difference.

    Why your lender’s schedule won’t match to the penny

    If you compare a template against a real statement, expect small differences. They are usually explained by one of these, not by an error:

    • Day-count conventions. Some lenders charge interest on actual days elapsed rather than a flat twelfth of the annual rate, so a 31-day month costs more than a 30-day one.
    • When the payment arrives. Interest accrues daily on many loans, so paying on the 1st rather than the 10th changes the split slightly.
    • Escrow and fees. A mortgage payment often bundles property tax and insurance. Those are not part of the loan and do not appear in an amortization schedule, so the figure leaving your account is larger than the figure in the template.
    • Rounding. Payments are rounded to the cent, and 360 roundings accumulate. The final payment is adjusted to clear the balance exactly.
    • A variable rate. A schedule for an adjustable-rate loan is only valid until the next reset. After that, every row is a projection based on a rate nobody knows yet.

    If the gap is larger than a few dollars, the usual cause is a different rate, a different term, or fees added to the principal at the start. Ask the lender for their own schedule and compare the two.

    The paperwork that comes with a mortgage

    A loan schedule is one document among several. For most consumer mortgages in the United States, the disclosure paperwork changed in 2015 and much of what older guides describe no longer exists.

    Document What it is
    Loan Estimate A three-page summary of the rate, payments, closing costs and cash needed, due within three business days of your application. It replaced the Good Faith Estimate and the early Truth in Lending disclosure.
    Closing Disclosure The final terms and costs, due at least three business days before closing so you can compare it with the Loan Estimate. It replaced the HUD-1 Settlement Statement and the final Truth in Lending form.
    Promissory note Your written promise to repay. Sets out the amount, rate, term and what happens on default.
    Mortgage or deed of trust The instrument that secures the loan against the property, giving the lender a claim if the note is not paid. Which of the two is used depends on the state.
    Deed Transfers ownership of the property to you. A separate thing from the loan documents, and often confused with them.
    Initial escrow statement What the lender will collect and pay out for taxes and insurance in the first year.

    The Loan Estimate and Closing Disclosure were introduced by the Consumer Financial Protection Bureau and apply to applications made on or after 3 October 2015. A few transactions sit outside the rule and still use the older forms, reverse mortgages and home-equity lines of credit among them, so a HUD-1 is not necessarily a sign that something is wrong. Outside the United States the paperwork differs entirely, though the arithmetic in the schedule does not.

    Common questions

    Do I need Microsoft Excel?

    Google Sheets and LibreOffice Calc both support PMT, IPMT, PPMT, CUMIPMT and NPER, so the calculations survive. Check a few rows after opening, as cell formatting and conditional formatting sometimes shift. Extract the zip first either way.

    Can I use these for a car loan or a personal loan?

    Yes. Any loan repaid in equal instalments at a fixed rate amortizes the same way, so only the principal, rate and number of payments change. Shorter terms make the early-payment imbalance much less pronounced.

    What about interest-only and balloon loans?

    They do not fully amortize, so a standard schedule will not describe them. An interest-only loan repays no principal during that period, meaning the balance at the end is the whole original sum. A balloon loan amortizes on a longer term than it actually runs, leaving a large final payment. Both need the template adapting, and both deserve care — the final amount owed is the point people misjudge.

    Is this the same as amortization in accounting?

    No, despite the shared name. In accounting, amortization means writing off the cost of an intangible asset over its useful life — the equivalent of depreciation for something you cannot touch. These templates deal with loan repayment only.

    Where does a loan schedule go in a business plan?

    In the financial section, as a debt service line feeding the cash flow forecast. Lenders reading a plan want to see that the repayment was modelled rather than assumed. Our business plan templates include the sections where it belongs, and a monthly sales forecast gives you the revenue side to set it against.

    Can I use these commercially?

    Yes, including in a business or for client work. You may not resell or redistribute the template files themselves. See our Terms of Use.

    Where the figure ends up

    A loan payment is rarely interesting on its own — it matters as a fixed line in something larger. If you are checking whether a mortgage will be clear before you stop working, the schedule gives you the payoff date and a retirement budget template gives you the income to weigh it against. If you are presenting a geared purchase to someone else, the debt service row in an investment proposal should come straight out of a schedule rather than from a rounded estimate, because that is the number a reader will check first.

    1 thought on “Amortization Schedule Templates”

    1. Pingback: Loan Payment Schedule Templates and the Two Ways to Repay

    Comments are closed.