How to Create an Amortization Schedule in Excel (Step-by-Step)

What You Need

Before you start, gather the key loan details: the loan amount (principal), the annual interest rate, the loan term in years, and the number of payments per year (usually 12 for monthly payments). You will also need spreadsheet software such as Microsoft Excel or Google Sheets, which both support the formulas used in this tutorial.

• Loan amount (principal): the total borrowed amount.
• Annual interest rate (as a percentage, e.g., 5% for 5%).
• Loan term in years (e.g., 30 for a 30-year mortgage).
• Payments per year (typically 12).
• Spreadsheet software (Excel or Google Sheets).

Setting Up Your Spreadsheet

Open a new spreadsheet and set up columns for the payment schedule. Label columns as: Payment Number, Payment Date (optional), Beginning Balance, Payment Amount, Interest, Principal, and Ending Balance. You will also need a few input cells for the loan parameters so you can change them later and have the schedule update automatically.

• Enter the loan inputs in a dedicated area: Loan Amount, Annual Interest Rate, Term (years), Payments per Year.
• Create column headers in row 1: Payment Number, Payment Date, Beginning Balance, Payment Amount, Interest, Principal, Ending Balance.
• Reserve column A for the payment number, starting at 1 in row 2.
• Optionally, add a column for extra payments if you plan to pay more than the scheduled amount.

Calculating the Monthly Payment (PMT)

The most important formula is the PMT function, which calculates the fixed monthly payment for a loan based on constant payments and a constant interest rate. In Excel or Google Sheets, use the PMT function with the rate per period, number of periods, and the loan amount.

The rate per period is the annual interest rate divided by the number of payments per year. The number of periods is the loan term in years multiplied by the number of payments per year.

• Rate = Annual Interest Rate / Payments per Year (e.g., 5% / 12 = 0.4167%).
• Nper = Loan Term (years) * Payments per Year (e.g., 30 * 12 = 360).
• PMT formula: =PMT(rate, nper, -loan_amount). Enter the loan amount as a negative number so the PMT function returns a positive payment, or enter it as positive and wrap the PMT result with a minus sign.
• In Excel, the PMT function returns a negative value; you can format it as positive for easier reading.

Building the Amortization Table

Now build the amortization table row by row. Start with the first payment: the beginning balance is the original loan amount. For each payment, calculate interest, principal, and the new ending balance, then copy the formulas down for each period.

Use absolute cell references for the input cells so you can copy the formulas without changing the loan parameters. For example, lock the rate, nper, and loan amount cells with $ signs.

• In the first row, set the beginning balance equal to the loan amount.
• Interest for the period = Beginning Balance * Rate per period (e.g., =B2*$C$4).
• Principal = Payment Amount - Interest (e.g., =$C$6-E2).
• Ending Balance = Beginning Balance - Principal (e.g., =B2-F2).
• For the next payment, the beginning balance equals the previous ending balance, and copy the formulas down for all periods.

Adding Interest and Principal Columns

Your table already includes interest and principal columns from the previous step. To make the schedule more useful, you can add a column for cumulative interest and a running total of principal paid. This helps you see how much of each payment goes toward interest versus reducing the balance.

You can also create a chart to visualize the portion of interest versus principal over time.

• In the interest column, use the formula =Beginning Balance * Rate per period.
• In the principal column, use =Payment Amount - Interest.
• Optionally, add a cumulative interest column that sums the interest from the first payment through the current period.
• Format the columns as currency to match the payment amounts.

Step-by-Step Instructions

Follow these steps in order to create your amortization schedule from scratch.

1. Enter the loan parameters (loan amount, annual interest rate, term in years, payments per year) in separate cells.
2. Calculate the monthly payment using the PMT function: =PMT(rate, nper, -loan_amount).
3. Set up the column headers: Payment Number, Payment Date, Beginning Balance, Payment Amount, Interest, Principal, Ending Balance.
4. In the first row, reference the loan amount as the beginning balance.
5. Calculate interest for the period: =Beginning Balance * (Annual Rate / Payments per Year).
6. Calculate principal: =Payment Amount - Interest.
7. Calculate ending balance: =Beginning Balance - Principal.
8. Copy the formulas down for all periods (total number of payments).
9. For each subsequent row, set the beginning balance equal to the previous row's ending balance.
10. Optional: Add a cumulative interest column and a chart for visualization.

Build a Reusable Template

After the first schedule works, keep the input cells separate from the table and save a clean copy as your reusable starting point. Add validation notes beside the inputs so a future user knows the required rate format, payment frequency, and term units.

• The template includes the PMT formula and the full amortization table.
• It supports monthly payments and can be adapted for other payment frequencies.
• You can add extra payments to the template by inserting an extra payment column and adjusting the formulas accordingly.