Excel Amortization Payment Formula and Example

Excel’s amortization payment formula is =-PMT(annual_rate/12, years*12, loan_amount) for a fixed-rate loan with monthly payments; the minus sign makes the payment display as a positive amount. Enter the loan amount, annual interest rate, and term to calculate the level payment, then use Excel’s IPMT and PPMT functions to split each payment into interest and principal.

Formula

The standard fixed-payment formula calculates the amount paid each period so that the loan balance reaches zero after the last scheduled payment:

Payment = P × r / (1 − (1 + r)−n)

• P is the starting principal, in dollars.
• r is the periodic interest rate as a decimal. For a stated annual percentage rate (APR) and monthly payments, this calculator uses APR ÷ 1200.
• n is the total number of monthly payments, equal to years × 12.

The calculator returns the monthly payment rounded to cents and the estimated total interest. Total interest is calculated as rounded monthly payment × number of payments − principal. Excel’s equivalent payment function is PMT(rate, nper, pv), where rate is the monthly rate, nper is the payment count, and pv is the present value, or starting loan amount. Excel normally returns a negative payment when the loan amount is entered as a positive cash inflow; use a leading minus sign, or enter the loan amount as a negative value, to control the sign convention.

Worked Example

Using the calculator’s defaults—$20,000 principal, 8% annual APR, and a five-year term—the calculation is:

1. Convert the APR to a monthly decimal rate: 8 ÷ 1200 = 0.0066666667.
2. Find the number of payments: 5 × 12 = 60.
3. Substitute into the payment formula: 20,000 × 0.0066666667 ÷ (1 − (1 + 0.0066666667)−60).
4. Round the result to cents: the monthly payment is $405.53.
5. Calculate total interest using the rounded payment: $405.53 × 60 − $20,000 = $4,331.80.

Excel can calculate the payment directly with =-PMT(8%/12,5*12,20000). Format the result as currency and display two decimal places. Because each individual payment is rounded for display, the final payment on an actual loan may differ slightly from the scheduled amount.

Assumptions

• The interest rate is fixed throughout the modeled term.
• Payments are equal, monthly, and made at the end of each period, as assumed by Excel’s ordinary PMT calculation.
• Interest is modeled monthly at APR divided by 12.
• Fees, taxes, insurance, PMI, and escrow are not included.

Build an Amortization Schedule in Excel

A payment formula gives the scheduled amount, but an amortization schedule shows how each payment changes the balance. Put the loan amount, annual APR, and term in separate cells, for example B1, B2, and B3. In B4, calculate the monthly payment with =-PMT(B2/12,B3*12,B1). Then set up columns for payment number, beginning balance, payment, interest, principal, and ending balance.

• Payment number: enter 1 in the first row and increment it by 1 in each following row.
• Beginning balance: use the original loan amount in the first row; in later rows, link to the previous row’s ending balance.
• Payment: link each row to the payment cell, such as =$B$4.
• Interest: multiply beginning balance by monthly APR, such as =beginning_balance*$B$2/12.
• Principal: subtract that period’s interest from its payment.
• Ending balance: subtract principal paid from the beginning balance.

For a more direct function-based schedule, Excel’s IPMT returns the interest portion for a specified period, and PPMT returns the principal portion. For example, =-IPMT($B$2/12,A7,$B$3*12,$B$1) and =-PPMT($B$2/12,A7,$B$3*12,$B$1) return positive amounts when the inputs follow the example’s sign convention. The period argument in A7 should run from 1 through the total payment count. Check that principal portions sum to the original principal, allowing for cent rounding.

How the Payment Changes Over Time

With a fixed rate and level monthly payments, the payment amount stays broadly constant, but its composition changes. Interest for a period is calculated on the outstanding balance, so the interest share is usually larger early in the schedule. As principal is repaid, the balance falls and subsequent interest charges decline; more of each later payment then reduces principal.

This is why the payment formula alone does not show the remaining debt at an arbitrary date. The schedule does. A simple balance recurrence is ending balance = beginning balance + interest − payment, with interest calculated for that period. In a real statement, rounding conventions and the date payments post can affect the precise balance.

Excel Formula Choices and Common Errors

PMT is convenient for the regular payment; IPMT and PPMT break an installment into interest and principal. For a cumulative total across a range of periods, Excel also provides CUMIPMT and CUMPRINC. These functions model scheduled cash flows, not every detail of a lender’s account.

• Using 8 instead of 8%: If the cell contains the number 8, divide by 1200 to get a monthly decimal rate. If it contains a percentage value entered as 8%, divide by 12 instead. Do not mix these conventions.
• Using years as the payment count: For monthly payments, multiply years by 12. A five-year loan has 60 monthly periods, not 5.
• Ignoring signs: Excel treats money received and money paid as opposite cash-flow directions. A negative result from PMT is not an incorrect calculation; it reflects the function’s sign convention.
• Rounding every intermediate value: Keep full precision in the rate and formulas, and format cells to show two decimal places. Rounding interest or balances manually each row can accumulate discrepancies.
• Assuming APR always describes every charge: The formula uses the entered rate as a periodic rate divided by 12. It does not add fees or model a different compounding convention.

Limits of a Standard Amortization Formula

This calculation fits a fully amortizing loan with a constant rate, equal monthly payments, and no extra payments. It does not represent a variable-rate loan, interest-only period, balloon payment, irregular payment dates, late payment, or fees added to the balance. It also does not calculate a lender’s APR from a fee schedule. If a contract has those features, the standard schedule may not match its payment breakdown or payoff balance.

For an actual estimate, use the principal, rate, term, and payment frequency supplied in the lender’s loan disclosure. If the lender quotes a payment that differs, check whether the quoted amount includes charges or uses different timing, rounding, or rate assumptions before comparing it with the spreadsheet.

Frequently Asked Questions

What is the Excel formula for a monthly loan payment?
Use =-PMT(annual_rate/12, years*12, loan_amount) when the annual rate is entered as a percentage and the term is in years. The minus sign displays the payment as positive under the usual positive-loan-amount convention.

How do I calculate interest for each month in Excel?
Multiply the period’s beginning balance by the monthly rate, or use IPMT(monthly_rate, period, total_periods, loan_amount). The function’s sign depends on the signs of the cash-flow inputs.

How do I calculate principal paid in each payment?
Subtract that period’s interest from the payment, or use PPMT(monthly_rate, period, total_periods, loan_amount). Principal paid generally rises over time in a fixed-rate, level-payment schedule.

Why is my Excel PMT result negative?
Excel uses cash-flow signs: money received and money paid are opposite directions. If the loan amount is positive, the payment commonly appears negative; put a minus sign before PMT to show a positive payment.

Does the PMT formula include taxes or fees?
No. It calculates a scheduled payment from the principal, periodic rate, and number of periods. Add separate inputs or calculations for any charges that apply, and verify them against the lender’s disclosed figures.

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

Related

• Payments First Payments University: What It Means
• PF Late Payment Interest Calculator and Rules
• Payment of Principal and Interest 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