Free Guide to Building Amortization Schedules in Excel

Understanding Amortization and Why Schedules Matter

An amortization schedule is a table that shows each loan payment over time, breaking down how much of each payment goes toward interest and how much goes toward the principal (the original amount borrowed). When you take out a loan—whether for a home, car, or business—the lender requires you to pay back the money plus interest. Understanding how these payments work helps you see the true cost of borrowing and track your progress toward owning your asset outright.

The term "amortization" comes from the Latin word meaning "to kill" or "to pay off." Each payment you make kills a portion of your debt. Early payments are heavily weighted toward interest, while later payments chip away more at the principal. This is why paying extra toward your principal early in the loan term can save you thousands in interest over time.

Amortization schedules are useful for several reasons. They show you exactly when your loan will be paid off, help you understand the real cost of borrowing, allow you to compare different loan terms side by side, and provide a roadmap for making extra payments if you want to pay off your loan faster. Many people are surprised to learn that on a 30-year mortgage, you might pay nearly as much in interest as you borrowed in principal.

Excel is an ideal tool for building amortization schedules because it's widely available, allows you to adjust variables instantly, and shows you exactly how changes (like extra payments) affect your loan. Unlike online calculators that give you static numbers, an Excel schedule lets you experiment with different scenarios and understand the mathematics behind your loan.

Practical Takeaway: Before building your schedule, gather your loan documents and note three pieces of information: the original loan amount (principal), the interest rate, and the loan term in months. These three numbers are the foundation for everything that follows.

Setting Up Your Excel Spreadsheet with Basic Information

Before you create formulas, you need to organize your loan information at the top of your spreadsheet. This setup section will contain the key data that feeds into all your calculations. Start by opening a blank Excel sheet and creating a clear section for "Loan Information" at the top. Use the first few rows to list the details that define your loan.

In cells down the left side, type labels: "Loan Amount," "Annual Interest Rate," "Loan Term (Years)," "Loan Term (Months)," and "Monthly Interest Rate." In the column to the right, enter your actual numbers. For example, if you borrowed $200,000 at 4.5% annual interest for 30 years, you would enter those values in your reference cells.

The monthly interest rate requires a calculation. If your annual rate is 4.5%, you divide by 12 to get the monthly rate (4.5% ÷ 12 = 0.375%, or 0.00375 in decimal form). Set up a formula that divides your annual interest rate by 12, so if you ever change the annual rate, the monthly rate updates automatically. Similarly, convert your loan term from years to months by multiplying years by 12.

Create another section below your loan information that will show your monthly payment amount. This is calculated using the PMT function in Excel. The syntax looks like this: =PMT(monthly_rate, number_of_months, -loan_amount). The negative sign before the loan amount tells Excel to treat it as money you owe. This formula will calculate the fixed payment amount you'd pay each month. For a $200,000 loan at 4.5% over 30 years, the monthly payment comes to approximately $1,013.37.

Use clear formatting to distinguish your input section from your calculations. You might use bold text for labels, shade the input cells with a light color, and separate this section from your amortization table with a few blank rows. This organization makes it easy to update your loan information later and lets anyone reading your sheet quickly understand what loan you're analyzing.

Practical Takeaway: Create a separate input area at the top of your sheet and use cell references (like =B2) in your formulas rather than typing numbers directly. This approach means you can change one loan amount and your entire schedule updates automatically, letting you quickly compare different scenarios.

Building Your Amortization Table Headers and First Payment Row

Now that your loan information is organized, you're ready to build the amortization table itself. This table will have one row for each payment, showing what happens with each payment you make. Start by creating column headers that clearly label what information appears in each column. You'll typically need six columns: Payment Number, Payment Date, Beginning Balance, Payment Amount, Principal, Interest, and Ending Balance.

Leave several blank rows between your loan information section and your table, then create your headers. "Payment Number" shows which payment this is (1, 2, 3, and so on). "Payment Date" helps you track when payments are due. "Beginning Balance" shows how much you owe at the start of that month. "Payment Amount" is your fixed monthly payment. "Principal" shows how much of this payment reduces your loan balance. "Interest" shows how much of this payment goes to the lender as interest. "Ending Balance" is what you still owe after this payment.

In the first data row (Payment 1), you'll start entering information and formulas. For Payment Number, simply type 1. For Payment Date, you might type the date you make your first payment or use a formula to calculate dates. In Beginning Balance, reference your original loan amount from your setup section above. For Payment Amount, reference the monthly payment you calculated using the PMT function.

The Interest column is calculated first: multiply your Beginning Balance by your Monthly Interest Rate. If you owe $200,000 and the monthly rate is 0.375%, the interest portion of your first payment is $750. The Principal portion is your Payment Amount minus the Interest portion. If your payment is $1,013.37 and interest is $750, then principal is $263.37. Finally, the Ending Balance equals Beginning Balance minus Principal. After your first payment on a $200,000 loan, you'd owe $199,736.63.

Format these cells to show currency with two decimal places. This makes it much easier to read and reduces confusion about whether you're looking at dollars or percentages. Set up your formulas carefully in this first row because you'll copy them down for every remaining payment, and any errors will compound throughout your schedule.

Practical Takeaway: Before copying formulas down, recalculate one payment by hand to verify your formulas are working correctly. For example, manually compute: (Beginning Balance × Monthly Rate) to check your Interest column, then Payment Amount minus Interest to verify Principal. This catches errors early.

Copying Formulas and Building Your Complete Schedule

Once your first payment row is complete and verified, you'll copy the formulas down to create rows for every payment over the life of your loan. If you have a 30-year mortgage, you'll have 360 rows (12 months × 30 years). Rather than typing each row, formulas do the work for you, and most formulas copy down automatically with one important adjustment.

Start by selecting the cells in your first payment row that contain formulas—typically from the Payment Amount column through the Ending Balance column. Copy these cells. Then, select the range where you want to paste them. If your first payment is in row 10 and you have 360 payments total, you'd select from row 11 down to row 370. Right-click and paste, or use Ctrl+V. Excel automatically adjusts your cell references as it copies down, so the second row references the second row's Beginning Balance instead of the first row's.

However, the Payment Number and Payment Date columns need special handling. For Payment Number, you might use a formula like =A9+1 (where A9 is the previous payment number), which adds 1 to each successive row. Alternatively, you can type 1 and 2 in the first two rows, select both, and drag down—Excel recognizes the pattern and auto-fills the sequence. For Payment Date, if you want dates that increment by one month, you can use a formula or simply type the first date and let Excel's auto-fill feature recognize the monthly pattern.

One critical detail: your Beginning Balance in each row must equal the Ending Balance from the previous row. Set up your formula so that in row 11