Home » Excel Templates » How To Create Amortization Schedule In Excel With Extra Payments

How To Create Amortization Schedule In Excel With Extra Payments

Sunday, November 2nd 2025. | Excel Templates

amortization spreadsheet extra payments google sheets db excelcom

Okay, here’s an HTML-formatted guide on creating an amortization schedule in Excel with extra payments, aiming for around 1000 words and skipping unnecessary tags:

Creating an amortization schedule in Excel is a fundamental skill for anyone managing loans, whether it’s a mortgage, car loan, or personal loan. This schedule outlines each payment, breaking it down into the portion that goes toward the principal balance and the portion that covers interest. Adding the complexity of extra payments allows you to visualize how accelerated repayment can significantly reduce the loan term and overall interest paid. Here’s a comprehensive guide on how to build such a schedule in Excel.

Setting Up the Initial Loan Information

First, create a dedicated area in your Excel sheet to store the initial loan parameters. This makes it easy to adjust these values and see the impact on the schedule. I suggest using the following labels in separate cells:

Loan Amount: The initial principal of the loan (e.g., $200,000).
Annual Interest Rate: The annual interest rate expressed as a decimal (e.g., 0.05 for 5%).
Loan Term (Years): The total length of the loan in years (e.g., 30).
Extra Payment: The additional amount paid each period (e.g., $100). This can be zero if you don’t plan to make extra payments initially.

In the cells adjacent to these labels, enter the corresponding values for your loan. For example:

Loan Amount:	$200,000
Annual Interest Rate:	0.05
Loan Term (Years):	30
Extra Payment:	$100

Consider naming these cells using Excel’s name manager for easier referencing in formulas. For example, name the cell containing the loan amount “LoanAmount,” the interest rate cell “InterestRate,” and so on. This makes the formulas more readable and less prone to errors.

Calculating Key Loan Metrics

Before building the amortization table, calculate a few essential values based on the initial loan information:

Monthly Interest Rate: Divide the annual interest rate by 12 (e.g., =InterestRate/12).
Number of Payments: Multiply the loan term in years by 12 (e.g., =LoanTerm*12).
Regular Monthly Payment: Use the PMT function to calculate the standard monthly payment without extra payments. The formula is: =PMT(InterestRate/12,LoanTerm*12,-LoanAmount). The negative sign before LoanAmount ensures the payment is displayed as a positive number.

Store these calculated values in separate cells as well, labeling them appropriately (e.g., “Monthly Interest Rate,” “Number of Payments,” “Regular Monthly Payment”).

Building the Amortization Table

Now, set up the amortization table itself. Create column headers for the following:

Payment Number: The sequential number of each payment (1, 2, 3, …).
Beginning Balance: The outstanding loan balance at the beginning of the payment period.
Scheduled Payment: The regular monthly payment calculated earlier.
Extra Payment: The additional payment made during that period.
Total Payment: The sum of the scheduled payment and the extra payment.
Interest Paid: The portion of the total payment that covers interest.
Principal Paid: The portion of the total payment that reduces the principal balance.
Ending Balance: The outstanding loan balance after the payment is applied.

Here’s how to populate the table with formulas:

Payment Number: In the first row (row 2 if your headers are in row 1), enter 1. In the cell below, enter the formula `=A2+1` (assuming “Payment Number” starts in column A). Drag this formula down to populate the payment numbers for the entire loan term. You can estimate the number of rows needed based on the “Number of Payments” calculated earlier, but we’ll adjust this dynamically later.
Beginning Balance: In the first row (row 2), enter a reference to the “Loan Amount” cell (e.g., `=LoanAmount`). In the cell below, we’ll use the “Ending Balance” from the previous row (see step 8).
Scheduled Payment: In the first row (row 2), enter a reference to the “Regular Monthly Payment” cell and lock it using absolute references (e.g., `=$D$1`, assuming “Regular Monthly Payment” is in cell D1). This ensures that the formula always refers to the same payment amount as you drag it down.
Extra Payment: In the first row (row 2), enter a reference to the “Extra Payment” cell and lock it using absolute references (e.g., `=$E$1`, assuming “Extra Payment” is in cell E1).
Total Payment: In the first row (row 2), enter the formula `=C2+D2` (assuming “Scheduled Payment” is in column C and “Extra Payment” is in column D).
Interest Paid: In the first row (row 2), enter the formula `=B2*MonthlyInterestRate` (assuming “Beginning Balance” is in column B and “Monthly Interest Rate” is named “MonthlyInterestRate”).
Principal Paid: In the first row (row 2), enter the formula `=E2-F2` (assuming “Total Payment” is in column E and “Interest Paid” is in column F). This is the portion of the total payment that goes toward reducing the principal.
Ending Balance: In the first row (row 2), enter the formula `=B2-G2` (assuming “Beginning Balance” is in column B and “Principal Paid” is in column G). This is the remaining loan balance after the payment is applied. In the second row (row 3), refer to this “Ending Balance” for the “Beginning Balance” (e.g., `=H2`).

Copying the Formulas Down and Handling the Final Payment

After entering the formulas in the first row, select all the cells in that row (except for “Payment Number,” which already has a formula in the second row) and drag the fill handle (the small square at the bottom right corner of the selection) down to copy the formulas to the remaining rows. As mentioned earlier, you can initially fill enough rows based on the initial “Number of Payments” calculation.

However, with extra payments, the loan will likely be paid off much sooner. To handle this gracefully, we need to add a condition to our formulas to stop calculating payments once the loan balance reaches zero. We’ll use the `IF` function for this. Modify the following formulas:

Scheduled Payment: `=IF(B2>0,MIN(C$1,B2+B2*MonthlyInterestRate),0)`. This formula checks if the beginning balance is greater than zero. If it is, it calculates the scheduled payment. The `MIN` function ensures that the payment doesn’t exceed the remaining balance plus interest. If the beginning balance is zero or less, the formula returns 0.
Extra Payment: `=IF(B2>0,D$1,0)`. This formula checks if the beginning balance is greater than zero. If it is, it makes the extra payment. If the beginning balance is zero or less, the formula returns 0.
Total Payment: `=IF(B2>0,E2,0)`. This formula checks if the beginning balance is greater than zero. If it is, it calculates the total payment. If the beginning balance is zero or less, the formula returns 0.
Interest Paid: `=IF(B2>0,B2*MonthlyInterestRate,0)`. This formula checks if the beginning balance is greater than zero. If it is, it calculates the interest payment. If the beginning balance is zero or less, the formula returns 0.
Principal Paid: `=IF(B2>0,MIN(G2,B2),0)`. This formula checks if the beginning balance is greater than zero. If it is, it calculates the principal payment. The `MIN` function is crucial here. If the total payment exceeds the remaining balance, it limits the principal payment to the remaining balance. If the beginning balance is zero or less, the formula returns 0.
Ending Balance: `=IF(B2>0,MAX(B2-G2,0),0)`. This formula checks if the beginning balance is greater than zero. If it is, it calculates the ending balance. The `MAX` function ensures the ending balance never goes negative, setting it to 0 if the principal paid is greater than the remaining balance. If the beginning balance is zero or less, the formula returns 0.
Beginning Balance (Row 3 onwards): `=IF(H2>0,H2,0)`. This formula checks if the previous row’s ending balance is greater than zero. If it is, it carries over the ending balance as the beginning balance. If it’s zero or less, it sets the beginning balance to zero.

After modifying these formulas in the first row (row 2) and the second row (row 3) for the Beginning Balance, copy these updated formulas down to all the remaining rows.

Analyzing the Amortization Schedule

Once the amortization schedule is complete, you can analyze it to see the impact of the extra payments. You’ll notice the loan is paid off much sooner, and the total interest paid is significantly less.

To get a summary of the total interest paid, use the `SUM` function to add up all the values in the “Interest Paid” column. Similarly, you can sum the “Principal Paid” column, which should equal the initial loan amount.

Dynamic Adjustment of the Table Length

To avoid having a very long table with many rows of zeros after the loan is paid off, you can use a more advanced technique involving dynamic named ranges or VBA scripting. However, for most users, simply hiding the rows with all zeros is sufficient. Select the rows with zero values in all columns and right-click, then choose “Hide.”

Conclusion

Creating an amortization schedule with extra payments in Excel provides a powerful tool for understanding and managing your loans. By experimenting with different extra payment amounts, you can quickly see how accelerating your payments can save you thousands of dollars in interest and shorten the loan term. This knowledge empowers you to make informed decisions about your financial future.