Payment Amortization Schedule Excel: Formula and Example

A payment amortization schedule in Excel shows each loan payment split between interest and principal, plus the balance remaining afterward. This calculator estimates the monthly payment and total interest from a loan amount, APR, and term; you can use the same inputs to build a month-by-month schedule in Excel.

Formula

The standard fixed-payment formula calculates a level monthly payment for a loan with a fixed annual percentage rate and monthly payments:

• Monthly payment: M = round[P × r ÷ (1 − (1 + r)⁻ⁿ), 2].
• Monthly interest rate: r = APR ÷ 1,200. APR is entered as a percentage per year, such as 8 for 8%.
• Number of payments: n = years × 12.
• Total interest estimate: total interest = (M × n) − P, using the payment rounded to cents.

Here, P is the starting principal in dollars, M is the monthly payment in dollars, and n is the number of monthly payments. The formula assumes equal payments made monthly and a fixed rate. It calculates the scheduled payment, not necessarily the amount due on a lender statement, which can reflect fees, timing, or other loan terms.

Worked Example

Using the calculator’s defaults: loan amount P = $20,000, APR = 8%, and term = 5 years.

1. Convert the APR to a monthly rate: r = 8 ÷ 1,200 = 0.0066667.
2. Convert the term to payments: n = 5 × 12 = 60.
3. Apply the payment formula: M = 20,000 × 0.0066667 ÷ [1 − (1.0066667)⁻⁶⁰] ≈ $405.53 per month, rounded to cents.
4. Calculate total interest from the rounded payment: ($405.53 × 60) − $20,000 = $4,331.80.

The result is an estimated monthly payment of $405.53 and estimated total interest of $4,331.80. In an actual amortization schedule, a final payment may need a small adjustment because interest and payments are rounded to cents each month.

Assumptions

• The interest rate remains fixed for the full term.
• Payments are made once per month, at the end of each monthly period.
• Interest accrues monthly at APR divided by 12.
• Fees, taxes, insurance, PMI, escrow, late charges, and extra payments are not included.

Build a Monthly Schedule in Excel

For a schedule, set up columns for payment number, beginning balance, payment, interest, principal, and ending balance. Put the original loan amount in the first beginning-balance cell. Use an absolute reference for the APR and payment cells so copied formulas continue to point to the same inputs.

1. Payment number: enter 1 in the first row and increase by 1 for each following row, up to the number of payments.
2. Beginning balance: the first row starts at the loan principal; each later row uses the previous row’s ending balance.
3. Interest: multiply the beginning balance by the monthly rate, APR divided by 12. If APR is stored as a percentage such as 8%, Excel’s percentage formatting already represents it as 0.08, so divide that value by 12 rather than by 1,200.
4. Principal repaid: subtract the period’s interest from the scheduled payment.
5. Ending balance: subtract principal repaid from beginning balance.

For example, if your worksheet stores APR as 8% in cell B2, the first period’s interest can be calculated as beginning balance × $B$2 / 12. If the payment is in B3 and interest in D7, principal in E7 can be calculated as =B3-D7. The next row’s beginning balance should link to the prior ending balance. Excel’s PMT function can also calculate a level payment: =PMT(B2/12,B4*12,-B1), where the cells hold APR, years, and principal. A negative present-value argument makes the payment display as a positive amount under Excel’s cash-flow sign convention.

Reading the Schedule and Checking the Totals

At the start of a conventional fully amortizing loan, the balance is larger, so the interest portion of a level payment is larger. As principal is repaid, later interest charges generally decline and more of each payment reduces principal. The total payment stays level under the stated assumptions, even though its components change.

Check that each row’s payment equals interest plus principal, subject to rounding, and that the ending balance becomes the next beginning balance. Over the full schedule, total principal paid should reconcile to the original principal, with a possible cent-level adjustment in the last row. Summing the interest column gives schedule-based total interest; it may differ slightly from multiplying a rounded payment by the number of months if you adjust the final payment.

Common Excel Errors and Limits

• Mixing APR formats: 8% entered as a percentage is stored as 0.08. Dividing that value by 1,200 instead of 12 makes the monthly rate far too small. If you enter 8 as a plain number, divide by 1,200.
• Wrong sign in PMT: Excel treats money received and payments made as opposite cash-flow directions. A negative principal argument commonly displays the payment as positive.
• Rounding every calculation too early: show currency to two decimal places, but avoid rounding intermediate rates or balances unnecessarily. Small differences can accumulate across many periods.
• Assuming all loans use this pattern: variable rates, irregular payment dates, interest-only periods, balloon payments, and fees require different calculations or additional inputs.

The calculator’s total-interest figure uses the rounded monthly payment multiplied by the scheduled number of payments, then subtracts principal. A row-by-row Excel schedule can produce a slightly different total when monthly interest and principal are rounded separately. Compare like with like and follow the lender’s disclosed payment and rounding conventions for an account-level figure.

Frequently Asked Questions

How do I make an amortization schedule in Excel?
Enter the loan details, calculate the fixed payment, then create rows for beginning balance, interest, principal, and ending balance. Link each row’s ending balance to the next row’s beginning balance.

What Excel formula calculates a monthly loan payment?
Excel’s PMT function can calculate it. If the APR is stored as a percentage and the term is in years, use =PMT(APR/12, years*12, -principal), adapting cell references to your worksheet.

Why does Excel show my payment as negative?
Excel uses cash-flow signs: money received and money paid are opposite directions. Entering the principal as a negative value in PMT generally returns a positive payment.

Why does the final balance not equal zero?
Rounding interest or principal in each period can leave a small remainder. A different payment date, rate convention, or payment amount can also explain a discrepancy. The final scheduled payment may need adjustment under the loan’s terms.

Does an amortization schedule include taxes and insurance?
Not unless you add separate columns or inputs for them. The basic schedule models principal and interest only; escrow, insurance, fees, and other charges are outside this calculation.

Can I use this for a variable-rate loan?
Not as a complete schedule with one fixed rate. You would need to update the rate and recalculate payments according to the loan terms when the rate changes.

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

Related

• Amortization Calculator With Extra Payments
• Amortization Calculator: Find Your Monthly Payment
• Amortization Calculator: Fixed Monthly Payment

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