Loan Amortization Formula in Excel: Payments and Schedule

The loan amortization formula in Excel calculates a level monthly payment from the loan amount, annual interest rate, and term; Excel’s PMT function is the direct option. This calculator estimates the monthly payment and total interest from those inputs, while the steps below show how to build a payment-by-payment amortization schedule in Excel.

Formula

For a fixed-rate loan with monthly payments, the level payment is:

PMT = P × r / [1 − (1 + r)⁻ⁿ]

• P is the amount borrowed, in dollars.
• r is the monthly interest rate as a decimal: annual APR ÷ 12 ÷ 100.
• n is the number of monthly payments: loan term in years × 12.
• PMT is the monthly payment in dollars, rounded to cents.

The calculator’s first result is the monthly payment. Its second is estimated total interest, calculated as the rounded payment multiplied by the number of payments, minus the original principal. This assumes every scheduled payment is made as calculated.

Worked Example

Using the calculator’s defaults—$20,000 borrowed, 8% APR, and a 5-year term:

1. Convert APR to a monthly decimal rate: 8 ÷ 12 ÷ 100 = 0.0066667.
2. Calculate the number of payments: 5 × 12 = 60.
3. Substitute into the payment formula: 20,000 × 0.0066667 ÷ [1 − (1 + 0.0066667)⁻⁶⁰].
4. The monthly payment is approximately $405.53 after rounding to cents.
5. Total interest using the rounded payment is $405.53 × 60 − $20,000 = $4,331.80.

These figures are estimates under the stated assumptions. Spreadsheet results may differ by a cent if a formula retains extra precision instead of rounding each payment to cents.

Build an Amortization Schedule in Excel

A payment formula returns the regular installment, but an amortization schedule shows how each installment is split between interest and principal. Set up inputs in cells, for example: B1 for principal, B2 for annual APR entered as a percentage, and B3 for the term in years. Excel’s PMT function can calculate the payment:

=-PMT(B2/12,B3*12,B1)

Excel treats cash paid out as negative in many financial functions, so the minus sign makes the displayed payment positive. With the example inputs, the result is about $405.53. An equivalent direct formula is =B1*(B2/12)/(1-(1+B2/12)^(-B3*12)), provided B2 is stored as 8% rather than the number 8. If APR is entered as the whole number 8, use B2/1200 for the monthly decimal rate.

Create columns for payment number, opening balance, payment, interest, principal repaid, and closing balance. In the first payment row, use:

• Opening balance: the original principal.
• Payment: the fixed PMT result.
• Interest: opening balance × APR ÷ 12.
• Principal repaid: payment − interest.
• Closing balance: opening balance − principal repaid.

For each following row, link the opening balance to the prior row’s closing balance and repeat the calculations. Fill down for the number of payments. For example, if the first row is row 6, the interest formula could be =B6*$B$2/12 when B6 contains the opening balance and B2 contains APR as a percentage. Use absolute references for input cells when copying formulas.

How to Read the Schedule

Interest is calculated on the outstanding balance, so the interest portion is usually larger early in a fully amortizing loan and becomes smaller as principal is repaid. The payment stays level under the fixed-rate assumptions, but its two components change. Near the end, the remaining balance should reach zero, subject to rounding and the loan’s actual payment rules.

Summing the schedule’s interest column gives the schedule’s total interest. That can differ slightly from multiplying the displayed rounded monthly payment by the number of months because per-row interest and principal calculations may carry more precision. If you need a schedule that reconciles to a lender’s records, follow the lender’s payment dates, rounding conventions, and final-payment adjustment.

Common Excel Errors and Limits

• APR entered in the wrong form: Excel’s percentage format stores 8% as 0.08. Dividing that by 12 is correct; dividing it by 1,200 again makes the rate too small. If entering 8 as a plain number, divide by 1,200.
• Term units do not match: monthly payments require months for n. Multiply years by 12, or enter the term in months directly.
• Sign conventions cause a negative result: PMT may return a negative payment when the loan principal is positive. Reverse the sign for display, or enter the present value as negative, but do not reverse both.
• Rounding is inconsistent: displaying two decimal places is not the same as rounding the underlying value. Decide whether your schedule uses full precision or explicitly rounds each payment and component.
• Extra payments or changing rates are omitted: a simple PMT schedule assumes fixed, level payments. Extra principal, fees, variable rates, skipped payments, and irregular payment dates require additional rows or a different model.

The formula is not a complete loan quote. It models principal and scheduled interest only, not charges or other items that may affect an actual amount due. Get the applicable figures and payment rules from the lender.

Assumptions

• The interest rate remains fixed throughout the term.
• Payments are equal monthly payments made at the end of each monthly period.
• Interest compounds monthly at APR ÷ 12, using APR as a nominal annual rate.
• Fees, taxes, insurance, PMI, escrow, and other charges are not included.

Frequently Asked Questions

What is the Excel formula for a monthly loan payment?
Use =-PMT(APR/12, years*12, loan_amount) when APR is stored as a percentage and the principal is positive. The minus sign displays the payment as a positive amount.

How do I calculate loan interest for each month in Excel?
Multiply that month’s opening balance by the monthly rate: opening balance × APR ÷ 12, with APR stored as a percentage. Subtract the interest from the payment to find principal repaid.

Why is my Excel PMT result negative?
Financial functions use cash-flow signs. A positive loan amount represents money received, while the repayment is money paid out, so PMT commonly returns a negative value. Negate the result to show a positive payment.

Does an amortization schedule include extra payments?
Not unless you add them to the schedule. Extra principal changes later balances and interest, so update the principal-repayment and closing-balance calculations for each affected period.

Why does the final Excel balance not equal zero?
Small differences can come from rounding, payment timing, or formulas that do not match the lender’s conventions. A real loan may adjust its final payment; check the lender’s disclosure for exact figures.

This result is an estimate for educational use; the lender’s disclosure gives the exact figures.

Related

• UW Short-Term Loan Payment Calculator
• Term Loan vs Line of Credit: How They Differ
• Term Loan vs Revolving Loan: Key Differences

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 | $ | round(p * (apr / 1200) / (1 - (1 + apr / 1200) ^ -(yrs * 12)), 2) | 2
out int | Total interest | $ | pmt * yrs * 12 - p | 2