Payment Amortization Calculator in Excel

A payment amortization calculator in Excel estimates a fixed monthly loan payment and shows how each payment is divided between interest and principal over time. Enter the loan amount, annual APR, and term to calculate the payment and total interest; the spreadsheet can also display the balance after each month.

Formula

The standard fixed-payment formula calculates a level payment that repays a loan over a set number of monthly periods:

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

• P is the starting principal, in dollars.
• r is the monthly interest rate, calculated as APR ÷ 1,200 when APR is entered as a percentage per year.
• n is the total number of monthly payments: loan term in years × 12.

The calculator rounds the monthly payment to cents. Total interest is estimated as the rounded monthly payment multiplied by the number of payments, minus the original principal. This total therefore reflects the displayed payment; actual lender schedules can differ slightly because of payment-date conventions, rounding, or a final adjusted payment.

Worked Example

Using the calculator’s default inputs—$20,000 principal, 8% APR, and a five-year term:

1. Monthly rate: 8 ÷ 1,200 = 0.0066666667.
2. Number of payments: 5 × 12 = 60.
3. Substitute into the formula: $20,000 × 0.0066666667 ÷ [1 − (1.0066666667)⁻⁶⁰].
4. The calculated payment is about $405.53 per month when rounded to cents.
5. Estimated total interest: $405.53 × 60 − $20,000 = $4,331.80.

Assumptions

• The interest rate is fixed for the full term.
• Payments are equal, monthly, and made at the end of each payment period.
• Interest is calculated monthly using APR divided by 12.
• Fees, taxes, insurance, PMI, escrow, and other charges are not included.

Building an Amortization Schedule in Excel

The calculator above gives the payment and estimated interest. To see the balance and interest allocation month by month, make a schedule with columns for payment number, beginning balance, payment, interest, principal, and ending balance.

1. Put the loan amount, annual APR, and term in separate cells. For example, use B1 for principal, B2 for APR, and B3 for years.
2. Calculate the payment with Excel’s PMT function: =-PMT(B2/12,B3*12,B1). The negative sign displays the payment as a positive amount under Excel’s cash-flow sign convention.
3. In the first schedule row, set beginning balance to the loan amount. Calculate interest as beginning balance multiplied by monthly APR, and principal repaid as payment minus interest.
4. Calculate ending balance as beginning balance minus principal repaid. In the next row, carry that ending balance forward as the new beginning balance, then copy the formulas down for the remaining months.

If the APR is entered as 8 rather than 8%, divide by 1,200 in the rate calculation. If the cell contains the Excel percentage value 8%, use the monthly rate as that cell divided by 12 instead. Mixing these conventions is a frequent source of incorrect results.

How to Read the Results

Early in a fully amortizing loan, the balance is relatively high, so a larger part of each fixed payment generally goes to interest. As the principal declines, the interest portion typically shrinks and more of the payment reduces principal. The scheduled payment stays level under the calculator’s assumptions.

For example, the first month’s interest in the default example is approximately $20,000 × 8% ÷ 12, or $133.33. From a $405.53 payment, about $272.20 goes toward principal, leaving an estimated balance of $19,727.80. Spreadsheet rounding can produce a few cents of difference across rows; some real schedules adjust the final payment to bring the balance to zero.

Common Excel Errors and Limits

• Entering APR incorrectly: An APR entered as 8 means eight whole percentage points; an APR entered as 8% is stored as 0.08. Use the matching rate formula.
• Using years where months are required: PMT’s second argument is the number of payment periods. For monthly payments, multiply years by 12.
• Ignoring the sign convention: Excel treats money received and money paid as opposite cash-flow signs. A leading minus sign on PMT is a common way to show a positive payment.
• Rounding every intermediate value: Avoid rounding the balance and interest to cents in every formula unless you intend to model that exact rounding approach. Displaying two decimals is different from rounding stored values.
• Assuming every loan is fixed-rate: Variable rates, irregular payment dates, interest-only periods, balloon payments, fees financed into the balance, and extra payments require additional inputs or a different schedule.

A spreadsheet is a model, not a statement of the amount due. Check the loan documents for how the lender defines the rate, calculates interest, applies payments, and handles fees or a final payment. Get the actual rate and other loan figures from the lender rather than treating the example values as current market information.

Frequently Asked Questions

What Excel formula calculates a monthly loan payment?
Use =-PMT(APR/12, years*12, loan_amount) when APR is stored as a percentage such as 8%. If APR is entered as the number 8, use =-PMT(APR/1200, years*12, loan_amount).

How do I calculate the interest portion of a payment?
Multiply the beginning balance for that month by the monthly interest rate. Subtract that interest from the scheduled payment to find the principal portion, assuming no additional charges or adjustments.

Does Excel’s PMT function include fees or insurance?
No. PMT calculates a payment from the amount financed, periodic rate, and number of payments. Add relevant costs separately if you are modelling them, and do not assume they are part of the quoted loan payment.

Why does my amortization schedule end with a small balance?
Small differences can result from rounding each month, using a rounded payment, or calculating interest with a different date convention. Review cell precision and the lender’s method; an actual final payment may be adjusted.

Can I use this calculator for extra payments?
The calculator estimates a level scheduled payment and does not model extra principal payments. An Excel schedule can include an extra-payment column, but it must apply those amounts to principal and recalculate the remaining balance and payoff timing.

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

Related

• Amortization Table Extra Principal Payment Calculator
• Amortization Schedule With Extra Principal Payments
• Amortization Schedule 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 | $ | round(p * (apr / 1200) / (1 - (1 + apr / 1200) ^ -(yrs * 12)), 2) | 2
out int | Total interest | $ | pmt * yrs * 12 - p | 2