Loan Amortization Schedule in Excel: Formula and Steps

Create a loan amortization schedule in Excel by listing each payment period, calculating interest on the opening balance, subtracting that interest from the payment, and reducing the balance by the principal repaid. This calculator estimates the fixed monthly payment and total interest from a loan amount, APR, and term; an Excel schedule then shows how each payment is divided between interest and principal.

Formula

The fixed monthly payment is calculated using the standard amortizing-loan formula:

M = P × r ÷ [1 − (1 + r)−n]

• M is the monthly payment in dollars.
• P is the amount borrowed in dollars.
• r is the monthly interest rate as a decimal: APR ÷ 1200.
• n is the number of monthly payments: loan term in years × 12.

Total interest in this calculator is the rounded monthly payment multiplied by the number of payments, minus the original loan amount. The formula assumes equal monthly payments and a fixed rate. Its payment is rounded to cents; actual lender schedules can differ because of payment dates, rounding rules, fees, or other contract details.

Worked Example

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

1. Convert the APR to a monthly decimal rate: r = 8 ÷ 1200 = 0.0066667.
2. Find the number of payments: n = 5 × 12 = 60.
3. Substitute in the payment formula: M = 20000 × 0.0066667 ÷ [1 − (1.0066667)−60].
4. Round the result to cents: M = $405.53.
5. Calculate total interest using the rounded payment: $405.53 × 60 − $20,000 = $4,331.80.

The results are an estimated monthly payment of $405.53 and total interest of $4,331.80.

Assumptions

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

Build the Schedule in Excel

First put the loan inputs in separate cells. For example, use B1 for principal (20000), B2 for APR (8%), and B3 for term in years (5). Enter the APR as a percentage, not the number 8; Excel stores 8% as 0.08. You can put the payment formula in B4 as =-PMT(B2/12,B3*12,B1). The minus sign makes the payment display as a positive amount when the loan principal is entered as positive. Format the payment cell as currency with two decimal places.

Set up a schedule with columns for period, opening balance, payment, interest, principal, and closing balance. Put headings in row 7, for example, and use row 8 for the first payment. The following formulas assume the loan inputs and payment are in the cells above:

• Period: enter 1 in A8 and =A8+1 in A9, then fill down for the number of payments.
• Opening balance: enter =$B$1 in B8. In B9, enter =F8, then fill down.
• Payment: enter =$B$4 in C8 and fill down.
• Interest: enter =B8*$B$2/12 in D8 and fill down.
• Principal: enter =C8-D8 in E8 and fill down.
• Closing balance: enter =B8-E8 in F8 and fill down.

Use dollar signs in references such as $B$2 to keep the input cell fixed when copying formulas. Fill the schedule through period 60 for the example. The final balance should be close to zero, although displayed rounding may leave a few cents. For a real loan, the last payment may need adjustment to clear the remaining balance; check the lender’s figures rather than treating a spreadsheet rounding difference as a contractual amount.

How to Read the Schedule

Each payment has an interest portion and a principal portion. Interest for a period is the opening balance multiplied by the periodic rate. The remainder of the payment reduces principal. Early in a standard fixed-payment loan, the balance is larger, so more of the payment goes to interest. As the balance falls, the interest charge generally declines and more of each level payment goes to principal.

For the example, first-month interest is approximately $20,000 × 0.08 ÷ 12 = $133.33. With a $405.53 payment, about $272.20 goes to principal, leaving an estimated balance of $19,727.80. Calculations retained at full precision can differ slightly from a schedule that rounds every row to cents.

Common Excel Errors and Limits

• Entering APR incorrectly: if B2 contains 8%, divide by 12; if it contains the number 8, use B2/1200 instead. Do not mix the two conventions.
• Using the wrong payment count: a five-year monthly loan has 60 periods, not 5. The rate and number of periods must use the same payment frequency.
• Reversing signs in PMT: Excel treats cash received and cash paid as opposite signs. A negative principal in the formula or a minus sign before PMT can make the displayed payment positive.
• Expecting a perfect zero balance: displayed cents, formula precision, irregular dates, and a final-payment adjustment can leave a small remainder.
• Assuming every loan follows this pattern: variable rates, interest-only periods, skipped or irregular payments, fees financed into the balance, and daily interest calculations require different schedule logic.

This is a planning calculation, not a substitute for the lender’s payment schedule. Use the actual principal, APR, term, and payment timing from the loan documents, and account for any charges separately if they are not part of the financed balance.

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%. The minus sign displays a positive payment for a positive loan amount.

How do I calculate interest for each month?
Multiply that month’s opening balance by the monthly rate. With APR entered as a percentage, use opening_balance*APR/12; with APR entered as the number 8, use opening_balance*APR/1200.

Why does my last balance not equal zero?
Rounding each row, using a payment rounded to cents, or entering a different number of periods can leave a small balance. Some loans also use a final payment that differs from earlier payments.

Does an amortization schedule include taxes and insurance?
Not unless you add them as separate columns or inputs. The standard loan schedule tracks principal and interest; taxes, insurance, PMI, escrow, and fees are outside this calculation.

Can I use this schedule for extra payments?
Yes, but add an extra-principal column and subtract that amount from the balance in the relevant period. Extra payments change the payoff path, and the loan agreement determines how payments are applied.

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

Related

• Agri Term Loan Calculator: Payments and Interest
• 7-Year Loan Term: DepEd Guidelines and Payment Calculator
• 7-Year Loan Term DepEd 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 | $ | p * (apr / 1200) / (1 - (1 + apr / 1200) ^ -(yrs * 12)) | 2
out int | Total interest | $ | pmt * yrs * 12 - p | 2