How Do You Build a Loan Amortization Schedule in a Spreadsheet?

A loan amortization schedule excel worksheet is simply a row-by-row table that shows how each payment splits between interest and principal. Building the schedule yourself is the clearest way to see why an early payment barely dents the balance while a late payment mostly reduces it. The arithmetic is repetitive rather than complicated, and a spreadsheet handles the repetition well.

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

What an Amortization Schedule Actually Shows

An amortization schedule is a complete list of every scheduled payment on an installment loan. Each row corresponds to one payment period and breaks the payment into the interest owed for that period and the amount that reduces the principal. The last row should land on a zero balance, which is how you confirm the schedule matches the loan term and the payment amount.

The Consumer Financial Protection Bureau describes a personal installment loan as credit repaid in a fixed number of scheduled payments, and an amortization schedule is the payment-by-payment detail behind that description. For a fixed-rate loan the payment amount stays level, but the internal composition of each payment shifts from period to period.

A schedule is not a sales document. It is a working table that answers concrete questions: how much interest will be paid in total, how much principal remains after a given number of payments, and what happens to the term if an extra payment is applied to principal. Lenders generate one for their records, and a borrower can reproduce the same table with the same inputs.

Why the Interest Share Falls Over Time

Interest is charged on the outstanding balance, so the interest portion of any payment depends on how much principal is still owed when that payment is made. Early in the loan the balance is close to its peak, so interest takes the largest bite and the principal portion is smallest. As the balance declines, the interest charge shrinks and a larger share of the same fixed payment goes toward principal.

That pattern explains why an extra payment early in the term has an outsized effect. An additional amount applied to principal in the first months removes dollars that would otherwise have generated interest for the entire remaining term. A loan payoff calculator models that effect and shows how a modest additional amount can shorten the schedule.

The reverse is also true. Stretching a loan over a longer term lowers the required payment but slows the decline of the balance, so more total interest is paid even when the rate is unchanged. The Consumer Financial Protection Bureau explains that the annual percentage rate expresses the cost of credit over a year, and a longer schedule is one reason two loans with the same rate can carry different APRs.

Columns a Workable Schedule Needs

Every amortization table, whether produced by a lender or assembled by hand, contains the same core columns. The table below lists them in the order that keeps the formulas simple, because each column generally depends on the one above it.

Two details cause most errors. First, the periodic rate is the annual rate divided by the number of payment periods in a year, so a monthly schedule uses one twelfth of the annual rate. Second, the final payment is usually slightly different from the others because of rounding, and a good schedule adjusts the last row so the ending balance reaches exactly zero.

Building the Schedule Step by Step

Once the columns are laid out, filling them in is mechanical. Work through the steps below in order and check the final balance before trusting the output.

1. Enter the loan amount, the annual interest rate and the number of payments in labeled cells so they can be changed easily.
2. Compute the periodic rate by dividing the annual rate by the number of payments per year.
3. Calculate the fixed payment using the standard amortization formula, which combines the loan amount, the periodic rate and the number of periods.
4. Fill the first row using the full loan amount as the beginning balance.
5. For each following row, pull the prior ending balance into the beginning balance column.
6. Multiply the beginning balance by the periodic rate to get the interest portion, then subtract it from the payment to get the principal portion.
7. Subtract the principal portion from the beginning balance to get the ending balance, and copy the row down to the final period.
8. Adjust the last payment by a few cents or dollars so the ending balance resolves to zero, then verify the total interest by summing the interest column.

Cross-checking against a lender-provided figure is the fastest way to catch a formula mistake. A amortization schedule calculator produces the same table instantly, which makes it a useful reference when your own rows disagree with the disclosure document.

Reconciling the Schedule With Your Loan Documents

A schedule is only useful if it matches the loan you actually signed. Start with the amount financed shown on the disclosure rather than the amount you originally requested, because an origination fee deducted from the proceeds means the amount you owe and the cash you receive can differ. The Consumer Financial Protection Bureau notes that personal installment loans may carry origination, late, returned payment and prepayment fees, and any fee rolled into the loan changes the balance you are amortizing.

Confirm the payment frequency as well. A loan advertised with a monthly payment but billed biweekly has a different schedule and a different total cost. Confirm the first payment date, because interest typically accrues from the disbursement date and a long gap before the first payment can add interest that a simple schedule does not capture.

Finally, compare the total of your interest column with the finance charge on the disclosure. Small differences can come from timing conventions, but a large gap usually means the rate, the term or the amount financed in your spreadsheet is wrong. A personal loan calculator can be used to test alternative inputs until the payment matches the document, which isolates which figure is off.

Where a Spreadsheet Helps and Where It Misleads

A homemade schedule is excellent for understanding structure. It shows clearly how much of an early payment is interest, how much total interest a loan will cost, and how a different term would change both. It is also a good planning tool for comparing two offers at the same loan amount, because you control the inputs and can hold everything else constant.

It is less reliable for predicting the exact payoff balance on a real account. Payments posted late, additional fees, a variable rate, or a servicer that applies payments differently can all push the actual balance away from the projection. Treat the spreadsheet as a model of how the loan is designed to behave, not as a statement of what you owe today.

If the goal is to decide whether to pay extra, run both scenarios in the same file: one schedule at the contractual payment and one with the extra amount applied to principal. The difference in total interest is the saving, and the difference in the number of rows is the time saved. That comparison is the most practical use of an amortization schedule for most borrowers, and it requires no special software beyond a basic spreadsheet.