Excel Amortization Formula With Extra Payments

An Excel amortization formula with extra payments calculates each period’s interest, principal reduction, and remaining balance after adding an extra amount to the scheduled payment. Enter the loan amount, APR, term, and extra payment to see the adjusted monthly payment and total interest; the schedule below uses a fixed-rate monthly-payment model.

Formula

This calculator gives the scheduled level monthly payment and the interest implied by keeping that payment for the full term. Let P be the starting principal in dollars, a the annual percentage rate (APR) in percent, and y the term in years. The number of monthly payments is n = 12y; the monthly rate as a decimal is r = a / 1200. The payment formula is:

Payment = P × r ÷ [1 − (1 + r)⁻ⁿ]

Total scheduled interest, assuming the payment is made for all n months, is Payment × n − P. The calculator rounds the displayed payment to cents. An extra payment is not included in these outputs; in Excel, add the extra amount to each scheduled payment in the amortization table to calculate the changed payoff path and interest.

Worked Example

Using the calculator’s defaults—$20,000 principal, 8% APR, and five years—the term is 5 × 12 = 60 monthly payments. The monthly rate is 8 ÷ 1200 = 0.0066666667.

1. Substitute into the level-payment formula: $20,000 × 0.0066666667 ÷ [1 − (1.0066666667)⁻⁶⁰].
2. The monthly payment is approximately $405.53 after rounding to cents.
3. Using the rounded payment for the full-term estimate: $405.53 × 60 − $20,000 = $4,331.80 total interest.

This is the scheduled-payment baseline. An actual amortization schedule can have a slightly different final payment because payments and interest are rounded by period.

Assumptions

• The interest rate is fixed for the entire modeled term.
• There are equal monthly payments, made at the end of each monthly period.
• Interest accrues monthly at APR divided by 12; the APR input is expressed as a percentage.
• Fees, taxes, insurance, PMI, escrow, and lender-specific payment rules are not included.

Build an Excel Schedule With Extra Payments

Use one row per payment period. A basic worksheet can place the starting balance in column B, interest in C, scheduled payment in D, extra payment in E, principal paid in F, and ending balance in G. Put the loan inputs in fixed cells—for example, principal in B1, APR in B2, term in years in B3, and recurring extra payment in B4. Use cell references appropriate to your sheet.

1. Calculate the scheduled payment with Excel’s PMT function: =-PMT($B$2/12,$B$3*12,$B$1). The minus sign makes the result display as a positive payment when the loan amount is positive.
2. For the first row, set beginning balance to the original principal. Calculate monthly interest as =BeginningBalance*$B$2/12 when APR is stored as a percentage such as 8% (not the number 8). If APR is typed as 8, divide by 1200 instead.
3. Set the scheduled payment to the PMT result and the extra payment to the planned additional amount. Principal paid is scheduled payment plus extra payment minus that period’s interest.
4. Ending balance is beginning balance minus principal paid. In the next row, carry the prior ending balance forward as the new beginning balance, then repeat the calculations.

For a more robust table, cap the total payment at the amount needed to clear the balance: payment should not exceed beginning balance plus that month’s interest. Otherwise the final row can show a negative balance, which is an artifact of continuing the same payment after payoff. A common formula is =MIN(ScheduledPayment+ExtraPayment,BeginningBalance+Interest), with principal paid calculated from that capped payment less interest. Stop the schedule when the balance reaches zero.

What Extra Payments Change

Interest for a period is calculated on the balance at the start of that period. An extra principal payment reduces the balance more quickly, so later interest amounts generally decline in a fixed-rate, standard amortization schedule. If the borrower keeps making the same extra amount, the loan will typically be paid off sooner, and the final payment will be smaller than a full regular payment. Exact savings depend on when payments post, how interest is calculated, and whether the lender applies extra amounts to principal.

For illustration, adding $100 to a $405.53 scheduled payment means the table initially records $505.53 paid, subject to the payoff cap in the final period. That example describes how to model the cash flow; it is not included in the calculator’s displayed total, which represents the no-extra-payment schedule. To calculate extra-payment interest accurately, sum the interest column in the actual schedule rather than using scheduled payment × original term − principal.

Common Excel Errors and Limits

• Mixing percentage formats: If a cell contains 8%, Excel stores it as 0.08, so divide by 12. If it contains 8 as a plain number, divide by 1200.
• Using the wrong period count: For monthly payments, multiply years by 12 and use APR divided by 12. For other payment frequencies, both the period count and rate conversion must change.
• Misreading PMT’s sign: Excel treats money paid out and money received as opposite cash-flow signs. Use a negative loan amount or add a leading minus sign to display a positive payment.
• Rounding every intermediate value: Displaying currency to two decimals does not require rounding every formula cell. Rounding interest or balances each period can produce small differences from a lender’s ledger.
• Assuming extra payments are applied to principal: The model assumes they reduce principal immediately. Check the loan statement or lender disclosure for payment handling, posting date, and any applicable charges or restrictions; do not assume the spreadsheet captures them.

This formula is a useful estimate for a conventional fixed-rate loan with equal monthly payments. It does not model variable rates, irregular payment dates, daily simple interest, skipped payments, fees, or a lender’s specific rounding and allocation methods.

Frequently Asked Questions

What is the Excel formula for a monthly loan payment?
Use =-PMT(APR/12,Years*12,LoanAmount) when APR is stored as a decimal percentage such as 8%. For an APR entered as the number 8, use =-PMT(8%/12,Years*12,LoanAmount) or convert the input to a percentage format first.

How do I include an extra payment in an Excel amortization schedule?
Add the extra amount to the scheduled payment for each period, subtract that period’s interest to find principal paid, and subtract principal paid from the beginning balance. Cap the final payment at the balance plus accrued interest.

Does an extra payment reduce interest or the monthly payment?
In a schedule that keeps the scheduled payment unchanged, an extra principal amount reduces the balance and therefore future interest; it generally shortens the payoff time. This model does not recalculate a lower required payment after each extra amount.

Why does my final Excel balance go below zero?
The schedule may be applying a full payment after the loan is already nearly paid off. Cap that period’s payment at beginning balance plus interest, then set subsequent rows to zero or stop the schedule.

Why does my Excel result differ from my lender’s figures?
Differences can come from payment posting dates, daily versus monthly interest accrual, rounding, fees, payment allocation, or an APR that differs from the interest rate used for amortization. Compare the spreadsheet assumptions with the lender’s disclosure and account details.

The result is an estimate; the lender’s disclosure gives the exact figures.

Related

• Biweekly Amortization Calculator: Payment and Interest
• Amortization Based on Payment Amount?
• 30-Year Amortization Table: Monthly Payment Calculator

in p | Loan amount | $ | 20000 | 1 | 100000000
in apr | Interest rate (APR) | % | 8 | 0.01 | 100
in yrs | Loan term | years | 5 | 0.0833 | 50
out pmt | Monthly payment | $ | p * (apr / 1200) / (1 - (1 + apr / 1200) ^ -(yrs * 12)) | 2
out int | Total interest | $ | pmt * yrs * 12 - p | 2