Free Excel Amortization Schedule Guide for Beginners

Understanding What an Amortization Schedule Is

An amortization schedule is a table that breaks down loan payments over time. It shows exactly how much of each payment goes toward principal (the money you borrowed) and how much goes toward interest (the cost of borrowing). This document helps you understand the true cost of a loan and track your progress as you pay it off.

Understanding Emeritus Status in Academia →

The word "amortize" comes from Latin and means "to kill" or "to pay off." When you amortize a loan, you're systematically killing the debt through regular payments. Most amortization schedules cover loans with fixed interest rates and set payment amounts, such as mortgages, car loans, and personal loans.

A typical amortization schedule includes several columns. The first column shows the payment number (1, 2, 3, and so on). The second column displays the payment date or period. The third column shows your payment amount—usually the same for every row. The fourth column breaks down how much of that payment covers interest. The fifth column shows how much pays down principal. The final column displays your remaining balance after that payment.

Here's a real-world example: If you take out a $200,000 mortgage at 5% interest over 30 years, your monthly payment will be about $1,073. In your first month, roughly $833 goes to interest and only $240 goes to principal. But as you continue paying, less goes to interest and more goes to principal. By month 300, almost the entire $1,073 payment goes to principal, with just a few dollars in interest.

Understanding amortization helps you see the real cost of borrowing. A $200,000 mortgage with a 5% interest rate over 30 years costs about $186,280 in total interest payments—nearly the size of the original loan. This knowledge can help you decide whether to pay off loans faster or negotiate better rates.

Practical Takeaway: An amortization schedule is a roadmap showing how each payment is split between interest and principal, revealing the true cost of your loan over time.

Setting Up Your First Amortization Schedule in Excel

Creating an amortization schedule in Excel requires only basic setup and a few formulas. Start by opening a blank Excel spreadsheet and labeling your columns. In the first row, create headers for: Payment Number, Payment Date, Payment Amount, Interest Paid, Principal Paid, and Remaining Balance.

Get Your Free Steam Account Name Change Guide →

Before building the schedule, gather your loan information. You'll need: the loan amount (principal), the annual interest rate, the loan term (number of years or months), and whether payments are monthly, quarterly, or annual. For example, if you're working with a $50,000 car loan at 4.5% annual interest over 5 years with monthly payments, you have all the information needed.

Next, create a separate section above your schedule to store key loan details. In cell A1, type "Loan Amount:" and in B1, enter 50000. In A2, type "Annual Interest Rate:" and in B2, enter 0.045 (which is 4.5% written as a decimal). In A3, type "Loan Term (Years):" and in B3, enter 5. In A4, type "Payment Frequency:" and in B4, enter "Monthly."

Now you need to calculate the monthly payment amount. Excel has a built-in function called PMT that does this. In cell A5, type "Monthly Payment:" and in cell B5, enter the formula: =PMT(B2/12, B3*12, -B1). This formula divides the annual interest rate by 12 (for monthly payments), multiplies the years by 12 (for total number of payments), and uses the negative loan amount.

For a $50,000 loan at 4.5% over 5 years, this formula produces a payment of approximately $922.99 per month. Now you have your baseline information ready. The monthly payment amount will be used in every row of your schedule.

Practical Takeaway: Setting up loan information separately makes your schedule organized and allows you to easily change values to see how different loan terms affect your payments.

Building the Payment Schedule Step by Step

Start filling in your payment schedule by entering the payment numbers in the first column. In cell A10 (leaving some space below your loan info), type 1. In A11, type 2. Select both cells, then drag the fill handle (small square at the bottom-right corner) down to create sequential numbers for all your payments. If your loan is for 5 years with monthly payments, you'll have 60 rows.

Learn About Device Battery Health and Usage →

For the Payment Date column, enter the first payment date in cell B10. If your first payment is January 1, 2024, type that date. Then in B11, enter the formula: =DATE(YEAR(B10),MONTH(B10)+1,DAY(B10)). This adds one month to the previous date. Copy this formula down for all rows.

In the Payment Amount column (column C), you'll reference the monthly payment you calculated earlier. In C10, enter the formula: =$B$5 (using dollar signs to "lock" this reference). Copy this formula down all rows—every payment amount will be identical because the loan has a fixed rate and fixed term.

The Interest Paid column requires a different approach for the first row versus subsequent rows. In D10 (first payment), enter: =(F9*($B$2/12)). This takes the remaining balance from the previous period (which starts as your original loan amount) and multiplies it by the monthly interest rate. For the first payment, the remaining balance from period zero is your original loan amount.

To handle this, add one row above your first payment. In F9, enter your original loan amount ($50,000). This becomes your starting balance before any payments.

For Principal Paid (column E), enter the formula: =C10-D10. This subtracts the interest portion from the total payment, giving you how much goes toward principal. For Remaining Balance (column F), enter: =F9-E10. This subtracts the principal payment from the previous balance.

Once your first row is complete with all formulas, select the entire row and copy the formulas down to your last payment. Excel will automatically adjust the cell references for each row.

Practical Takeaway: Building row-by-row with correct formulas creates a schedule that accurately tracks how your balance decreases with each payment.

Reading and Interpreting Your Completed Schedule

Once your amortization schedule is complete, you can observe several important patterns. Look at the Interest Paid column. Notice that the first payment contains significantly more interest than the last payment. For the $50,000 car loan, the first payment might include $187.50 in interest, while the final payment includes just a few dollars.

Learn About E-Payment Systems And How They Work →

This pattern exists because interest is calculated on the remaining balance. Early in the loan, your balance is high, so interest charges are high. As you pay down principal, the balance shrinks, and interest calculations become smaller. This is true for nearly all amortized loans.

The Principal Paid column tells you how much you're actually reducing your debt with each payment. Early on, this number is low ($735.49 of your $922.99 payment, in this example). By the end of the loan, principal payments are nearly the full payment amount ($920+ goes to principal).

Total up your Interest Paid column to see the true cost of the loan. For this $50,000 car loan, you might pay about $5,379 in total interest over 5 years. That means the car actually costs $55,379 when you account for borrowing costs.

You can use your schedule to answer specific questions. Want to know your balance after 24 months? Look at row 34 (payment 24 for monthly payments). After two years, you might still owe $27,500—showing that you've only paid down about half the principal despite making 24 payments. Want to know how much you've paid in interest after 3 years? Add up all interest payments through row 46.

The schedule also shows why paying extra principal can save money. If you could add $100 to each payment, you'd