Loan Repayment Schedule Excel Formula and Example

A loan repayment schedule in Excel tracks each payment’s interest, principal, and remaining balance; the standard fixed-payment formula is =-PMT(annual_rate/12, years*12, loan_amount). Enter the loan amount, annual interest rate, and term to calculate the monthly payment, then use a row-by-row balance formula to build the schedule.

Formula

The calculator uses the fixed-payment amortization formula:

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

• P is the starting loan principal, in dollars.
• r is the monthly interest rate as a decimal: APR ÷ 12 ÷ 100. In the calculator expression, this is apr / 1200.
• n is the total number of monthly payments: loan term in years × 12.
• Payment is the level monthly amount, in dollars, before any charges not included in the inputs.

Total interest is estimated as monthly payment × number of payments − principal. The calculator rounds the payment to cents before multiplying it to obtain total interest. The Excel PMT function uses a cash-flow sign convention: with a positive loan amount, its result is normally negative because it represents money paid out. Put a minus sign before PMT to display the payment as a positive amount.

Worked Example

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

1. Convert the annual percentage rate to a monthly decimal: 8 / 1200 = 0.0066667.
2. Calculate the number of payments: 5 × 12 = 60.
3. Apply the payment formula: 20,000 × 0.0066667 / (1 − (1 + 0.0066667)−60), which is about $405.53 per month when rounded to cents.
4. Calculate total interest using the rounded payment: $405.53 × 60 − $20,000 = $4,331.80.

The schedule’s final row may need a small adjustment if the lender rounds interest and payments at each period; a formula that repeats rounded amounts can leave a few cents outstanding or overpaid.

Assumptions

• The interest rate stays fixed throughout the term.
• Payments are monthly and made at the end of each period, as in ordinary loan amortization.
• Interest is modelled monthly at APR divided by 12.
• Fees, taxes, insurance, PMI, and escrow are not included.

Build the Repayment Schedule in Excel

To create the full schedule, put the loan amount, APR, and term in separate cells. For example, use B1 for principal, B2 for annual APR entered as a percentage such as 8%, and B3 for the term in years. In B4, calculate the monthly payment with =-PMT(B2/12,B3*12,B1). Excel interprets 8% as 0.08, so divide by 12—not by 1200—in this version.

Add column headings for payment number, opening balance, payment, interest, principal repaid, and closing balance. Put 1 in the first payment row. If the first schedule row is row 8, the formulas can be:

• Opening balance, B8: =$B$1
• Payment, C8: =$B$4
• Interest, D8: =B8*$B$2/12
• Principal repaid, E8: =C8-D8
• Closing balance, F8: =B8-E8
• Next opening balance, B9: =F8

Copy the next-row formulas down for the full number of payments. For a more robust schedule, cap the final payment at the amount due: calculate interest from the opening balance, then use a payment no greater than opening balance plus that period’s interest. This prevents the last row from showing a negative balance when rounding or an early payoff changes the exact amount due.

Excel PMT, IPMT, and PPMT

PMT returns the regular payment for a loan with a constant interest rate and payment amount. Its arguments are rate per period, number of periods, and present value. If payments are monthly, use a monthly rate and the total number of months. For instance, for an APR stored as a decimal, use =-PMT(B2/12,B3*12,B1). If the rate is stored as the whole number 8 instead of 8%, use B2/1200 instead.

Excel also provides IPMT for the interest part of a particular payment and PPMT for its principal part. For payment number 1, a monthly loan can use =-IPMT(B2/12,1,B3*12,B1) and =-PPMT(B2/12,1,B3*12,B1). Increase the period argument for each schedule row. These functions use the same sign convention, which is why the example negates their results.

Reading the Schedule and Avoiding Errors

At the start of a conventional amortizing loan, the outstanding balance is largest, so a fixed monthly rate produces more interest on each payment. As the balance declines, the interest portion generally falls and the principal portion rises. The payment can remain level even though its internal split changes. The total interest is the sum of the interest column, subject to the schedule’s rounding method.

• Match rate units to the cell: 8% entered as a percentage is 0.08 in Excel. Divide it by 12 for monthly periods. If you enter 8 as a whole number, convert it with 1200.
• Match periods: A monthly rate requires a number of monthly payments. Do not use five periods for a five-year monthly loan; use 60.
• Do not mix annual and monthly payments: If payments occur on another schedule, both the rate conversion and period count must reflect that schedule.
• Check signs: Excel financial functions treat received funds and outgoing payments as opposite cash flows. A negative display is often a sign convention, not a calculation error.
• Account for rounding: Excel may retain fractions of a cent internally, while a lender may round each billed payment or interest amount. A small final-period difference can result.

This method is for a fully amortizing loan with a fixed rate and equal periodic payments. It does not automatically model variable rates, irregular payment dates, payment holidays, fees added to the balance, extra principal payments, or lender-specific day-count conventions. For those cases, calculate each period using the actual terms and payment dates, or obtain the lender’s amortization schedule.

Frequently Asked Questions

What is the Excel formula for a loan payment?
For monthly payments and an annual rate stored as a percentage, use =-PMT(APR/12, years*12, principal). Ensure the APR cell is a percentage, such as 8%, rather than the whole number 8.

How do I calculate loan interest for each month in Excel?
Multiply that month’s opening balance by the monthly rate. If the opening balance is in B8 and the APR is in B2 as a percentage, use =B8*$B$2/12.

How do I calculate the principal part of a payment?
Subtract that period’s interest from the payment: principal repaid = payment − interest. The next opening balance is the previous opening balance minus principal repaid.

Why does my Excel payment show as a negative number?
PMT follows a cash-flow convention: the loan received and the repayments have opposite signs. Prefix the function with a minus sign when you want to show the repayment as a positive amount.

Why does my final loan balance not equal zero?
Rounding, using a mismatched rate or payment count, or making a payment at a different time can cause a residual. Check the inputs and rounding, and calculate the final payment from the amount actually due rather than blindly repeating the regular payment.

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

Related

• Vehicle Loan Payoff Calculator: Estimate Your Balance
• VA Personal Loan Programs: What They Cover
• UWM Loan APR: How to Calculate Your 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 | $ | p * (apr / 1200) / (1 - (1 + apr / 1200) ^ -(yrs * 12)) | 2
out int | Total interest | $ | pmt * yrs * 12 - p | 2