Amortization Schedule in Excel: Formula and Example

An amortization schedule in Excel shows how each loan payment is divided between interest and principal, and how the balance changes over time. Enter the loan amount, annual interest rate, and term in the calculator below to estimate the monthly payment and total interest; you can then build the payment-by-payment schedule in Excel.

Formula

The calculator estimates a fixed monthly payment using the standard level-payment loan formula. It assumes monthly payments and a monthly rate equal to the annual percentage rate divided by 12:

• Monthly rate: r = APR / 1200, where APR is entered as a percentage per year and r is a decimal per month.
• Number of payments: n = years × 12.
• Monthly payment: M = P × r / [1 − (1 + r)⁻ⁿ].
• Total interest: I = M × n − P.

P is the starting loan principal in dollars, M is the calculated monthly payment in dollars, and I is the estimated total interest in dollars. The output rounds the monthly payment to cents. The total-interest output uses that displayed rounded payment multiplied by the full number of monthly payments, then subtracts the principal; actual lender schedules may handle the final payment differently.

Worked Example

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

1. Convert the annual rate to a monthly decimal: r = 8 / 1200 = 0.0066667.
2. Find the number of payments: n = 5 × 12 = 60.
3. Apply the payment formula: M = 20,000 × 0.0066667 / [1 − (1.0066667)⁻⁶⁰] ≈ $405.53 per month.
4. Use the rounded payment to estimate interest: I = $405.53 × 60 − $20,000 = $4,331.80.

These figures are estimates based on the stated assumptions. Small differences can arise when a lender uses different payment dates, rounding rules, or a final adjusted payment.

Build the Schedule in Excel

To create a schedule, put the loan inputs in clearly labeled cells. For example, enter the principal in B1, APR as a percentage in B2, term in years in B3, and monthly payment in B4. In B4, use:

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

Excel’s PMT function returns a negative cash flow when the principal is entered as a positive amount, so the leading minus sign displays the payment as positive. If you enter APR as the number 8 rather than a percentage-formatted 8%, use =-PMT(B2/1200,B3*12,B1) instead. Do not divide a percentage-formatted 8% by 100 again.

Create columns for payment number, beginning balance, payment, interest, principal, and ending balance. The first schedule row can use these formulas, assuming row 8 is the first payment:

• Payment number: enter 1, then increase by 1 on each row.
• Beginning balance: =$B$1 for the first row; subsequent rows link to the prior ending balance.
• Payment: =$B$4.
• Interest: =BeginningBalance*$B$2/12, using the appropriate cell references.
• Principal: =Payment-Interest.
• Ending balance: =BeginningBalance-Principal.

For example, if the first row’s beginning balance is in B8, payment in C8, interest in D8, principal in E8, and ending balance in F8, the formulas could be =B8*$B$2/12 in D8, =C8-D8 in E8, and =B8-E8 in F8. On the next row, set the beginning balance to the previous ending balance, such as =F8, and fill the formulas down for the scheduled number of payments.

How to Read Each Row

At the start of a standard fully amortizing loan, the balance is relatively high, so interest takes a larger share of the payment. As principal is paid down, the interest calculated on the remaining balance generally falls, and more of each level payment goes toward principal. The payment can stay the same while its components change.

In a simple monthly schedule, interest for a row is the beginning balance multiplied by the monthly rate. Principal is the payment less that interest. The ending balance is the beginning balance less principal. When the schedule is complete, the balance should be close to zero, allowing for rounding. A slight residual is not automatically an error: spreadsheet precision, rounding to cents each row, and lender-specific final-payment adjustments can all affect the last line.

Assumptions

• The rate is fixed for the entire term.
• Payments are made monthly at the end of each modeled period.
• Interest compounds monthly at APR / 12; the calculator treats the entered APR as a nominal annual rate for this calculation.
• The loan is repaid with equal scheduled payments, with no extra payments or skipped payments.
• Fees, taxes, insurance, PMI, escrow, and other charges are not included.

Common Excel Problems and Limits

Rate entered in the wrong format: If a cell contains 8%, divide it by 12 to get the monthly rate. If it contains the number 8, divide by 1200. Mixing these conventions produces a dramatically incorrect payment.

Sign errors in PMT: Spreadsheet financial functions track money flowing in and out. A positive loan amount can produce a negative payment, which is why the example negates PMT. Alternatively, enter the principal as negative and leave the function result’s sign unchanged.

Rounding every row: Displaying two decimal places is different from rounding the underlying calculation to two decimals. Excel normally retains extra precision unless a formula uses ROUND. A lender may round periodic interest to cents and adjust the final payment, so the spreadsheet may not match the lender’s statement exactly.

Other loan structures: This formula is for a fixed-rate, level-payment loan. It does not model variable rates, interest-only periods, balloon payments, irregular payment dates, daily interest calculations, fees financed into the balance, or changing payment amounts. Check the lender’s payment schedule or disclosure for exact terms and figures.

Frequently Asked Questions

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

How do I calculate the interest in each payment?
For a monthly schedule using a nominal annual rate, multiply the row’s beginning balance by the annual rate divided by 12. Subtract that interest from the payment to get the principal portion.

Why does the Excel balance not reach exactly zero?
Displayed rounding, rounding inside formulas, and the lender’s payment timing can leave a small remaining balance. A real lender may adjust the last payment; do not assume a spreadsheet residual is the amount due.

Can I add extra payments to an Excel amortization schedule?
Yes. Add an extra-principal column and subtract that amount from the ending balance along with scheduled principal. Recalculate interest on the new balance each period, and ensure the extra payment cannot make the balance negative; the calculator above does not model extra payments.

Does an amortization schedule include taxes and insurance?
Not in the standard principal-and-interest calculation shown here. Some loan bills include separate charges, such as taxes or insurance, so the total amount paid may differ from the schedule’s payment.

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

Related

• Low Interest Rate? Calculate a Loan Payment
• Line of Credit Interest Rate: Calculate the Cost
• Loan Against Property Interest Rate 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