Loan Amortization Excel: How to Build Your Own Schedule

Loan amortization excel formulas let a borrower see exactly how each payment divides between interest and principal over the life of a loan. A spreadsheet built from those formulas turns a monthly payment into a full schedule that shows the declining balance and the total interest paid. The value of doing it yourself is that the assumptions are visible and can be changed to test different rates, terms and extra payments.

By the LoanOctopus.com Editorial Team · Updated 2026-09-16

What an Amortization Schedule Shows

An amortization schedule is a table that lists every payment on a loan and splits each one into an interest portion and a principal portion. Early payments are mostly interest because interest is charged on the large remaining balance. As the balance falls, the interest portion shrinks and more of each payment goes toward principal.

The Consumer Financial Protection Bureau describes a personal installment loan as a fixed amount repaid in scheduled payments, which is exactly the structure an amortization schedule models. The schedule answers three practical questions: how much interest is paid in total, how much principal remains at any point, and what happens if payments are made faster.

Spreadsheets are popular for this because they recalculate instantly. A borrower can change the interest rate or the term and watch the total interest and payoff date shift, which is useful when comparing offers that quote different terms.

The Core Excel Inputs and Functions

A working schedule needs only a few inputs: the loan amount, the annual interest rate, the term in months and the monthly payment. The payment can be calculated rather than typed, which keeps the schedule consistent when the inputs change.

Three functions do most of the work. The PMT function returns the periodic payment for a loan given a rate, a number of periods and a present value. The IPMT function returns the interest portion of a specific payment, and the PPMT function returns the principal portion of that same payment. Using all three in adjacent columns produces a schedule that stays correct when the inputs change.

The rate input must match the payment period. Because most consumer loans are quoted as an annual rate but paid monthly, the annual rate is divided by twelve inside the formula. Mixing an annual rate with a monthly period count is the single most common error in a homemade schedule, and it produces a payment that is far too high.

Building the Schedule Step by Step

The sequence below produces a schedule in any spreadsheet application, and the logic is the same whether the software is a desktop package or an online tool.

1. Enter the loan amount, the annual rate and the term in months in labelled cells.
2. Calculate the monthly rate by dividing the annual rate by twelve.
3. Use the payment function to compute the fixed monthly payment from the amount, rate and term.
4. Create a column for the period number, starting at one and increasing by one each row.
5. Add an interest column that multiplies the prior balance by the monthly rate.
6. Add a principal column that subtracts the interest from the payment.
7. Add a balance column that subtracts the principal from the prior balance.
8. Copy the row down until the balance reaches zero and confirm the final balance is essentially nil.

When the schedule is complete, the sum of the interest column is the total interest cost, and the sum of the principal column should equal the original loan amount. Those two checks catch most formula errors. A loan amortization schedule calculator can generate the same table automatically, which is useful for a quick cross-check against a manual build.

Why the Interest Share Falls Over Time

Interest is charged on the outstanding balance, so the same rate produces a larger dollar charge when the balance is high and a smaller charge as it falls. That is why the first payment on a long loan is mostly interest and the last payment is almost entirely principal.

The Consumer Financial Protection Bureau explains that the annual percentage rate includes certain fees in addition to the interest rate, so a schedule built from the nominal rate alone will slightly understate the true cost of borrowing. Adding known fees to the schedule as an extra cost row gives a more complete picture.

Term length amplifies the effect. A longer term lowers the monthly payment but keeps the balance high for longer, which increases the total interest. A loan payoff calculator shows how adding to each payment shortens the schedule and reduces total interest, and that result can be verified by adding an extra-payment column to the spreadsheet.

Common Mistakes in Spreadsheet Schedules

Small errors produce misleading results, and the table below lists the ones that appear most often.

Keeping inputs in named cells and referencing them in every formula avoids most of these problems. A final reconciliation row that compares the sum of principal payments to the loan amount confirms the schedule is internally consistent.

When to Use a Calculator Instead

A spreadsheet is valuable when the assumptions need to be visible or when several scenarios are compared side by side. For a single quick answer, a dedicated calculator is faster and removes the risk of a formula mistake.

The Federal Reserve publishes selected interest rate data that can inform the rate assumption used in a schedule, though the rate a specific lender offers depends on the borrower's credit profile and the collateral involved.

For anyone who wants to compare a manual build against a standard result, the loan amortization schedule in Excel walkthrough covers a related approach, and the amortization calculator provides an independent figure. When the two agree, the spreadsheet is almost certainly correct.

Sharing and Reusing the Template

A schedule that works well is worth keeping as a reusable template. Clearing the input cells while leaving the formulas intact lets the same file model a new loan in seconds. Naming the input cells, such as amount, rate and term, makes the formulas easier to read and reduces the chance of referencing the wrong cell. Locking the formula columns while leaving the inputs open prevents an accidental overwrite that would corrupt the schedule.

It also helps to keep a second worksheet that compares two or three scenarios side by side. A shorter term with a higher payment and a longer term with a lower payment can be shown together, along with the total interest for each, so the trade-off is visible at a glance. That comparison is often more useful than a single schedule because it answers the question a borrower actually faces.

A schedule should be checked against a second source before it informs a decision. Comparing the result with a loan amortization schedule calculator confirms the formulas are correct. Once verified, the template becomes a reliable tool for evaluating any loan offer on equal terms, and it can be shared with a lender or advisor who wants to see the assumptions behind a projection.