Term Loan Calculator Excel: Build a Payment Schedule

A term loan calculator in Excel can estimate a fixed monthly payment, total interest, and the balance over a stated loan term. Enter the loan amount, annual APR, and term below to see the payment and total interest; the estimate assumes equal monthly payments and a fixed rate.

Formula

The payment calculation is the standard amortizing-loan formula, with the monthly payment rounded to the nearest cent before total interest is calculated:

PMT = round(P × r / (1 − (1 + r)⁻ⁿ), 2)

Total interest = PMT × n − P

P is the amount borrowed, in dollars.
APR is the annual percentage rate entered as a percentage, such as 8 for 8%.
r is the monthly rate, calculated as APR ÷ 1,200.
n is the number of monthly payments, calculated as loan term in years × 12.
PMT is the rounded monthly payment, in dollars.

In Excel, the equivalent payment expression is =ROUND(P*(APR/1200)/(1-(1+APR/1200)^-(Years*12)),2), where the names stand for cells or named ranges containing the inputs. If using Excel’s PMT function instead, its rate and number-of-periods arguments must be monthly: =-PMT(APR/12,Years*12,P) when APR is entered as a decimal such as 0.08. Excel ordinarily returns a payment with full precision; round it to cents if the schedule and totals are meant to follow this calculator’s convention.

Worked Example

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

Convert the annual rate to a monthly decimal: 0.08 ÷ 12 = 0.0066667.
Find the number of payments: 5 × 12 = 60.
Apply the payment formula: 20,000 × 0.0066667 ÷ (1 − (1 + 0.0066667)⁻⁶⁰), then round to cents.
The monthly payment is $405.53.
Total of rounded payments: $405.53 × 60 = $24,331.80.
Subtract the amount borrowed: $24,331.80 − $20,000 = $4,331.80 total interest.

The totals use the rounded monthly payment, as an actual payment schedule commonly expresses payments in cents. A final payment can differ slightly if a lender adjusts it to clear a remaining balance.

Assumptions

The interest rate is fixed for the full term.
Payments are equal monthly payments made at the end of each monthly period.
Interest is calculated monthly at APR divided by 12; this is a standard simplifying assumption, not a lender-specific daily-interest method.
The calculation excludes fees, taxes, insurance, PMI, escrow, and any separate charges or financed costs.

What a Term Loan Calculator Shows

A term loan is borrowed as a lump sum and repaid through scheduled payments over an agreed period. For a fully amortizing loan with a fixed rate, each payment covers interest due for that period and reduces principal. Early payments generally include a larger interest share; as the balance declines, the interest portion falls and more of each level payment goes toward principal.

The calculator answers two basic questions: the estimated payment required to repay the stated principal within the term, and the interest implied by making that payment for every month. It does not determine whether a lender will offer those inputs or what a particular contract will charge. Use the APR, amount, and term from the lender’s disclosure when available; do not treat a sample rate or output as a quote or approval decision.

Building the Schedule in Excel

A spreadsheet can show the path from opening balance to payoff. Put principal, APR, and years in separate input cells. Calculate monthly payment with the formula above, then create a row for each payment period. Use columns for payment number, starting balance, interest, principal repaid, payment, and ending balance.

Monthly interest = starting balance × APR ÷ 12.
Principal repaid = payment − monthly interest.
Ending balance = starting balance − principal repaid.

For the first row, the starting balance is the loan amount. In later rows, link the starting balance to the prior row’s ending balance. Copy the formulas down for the number of months in the term. Use currency formatting for dollar amounts and keep full precision in intermediate balance calculations; rounding every intermediate cell can cause the schedule’s final balance to drift. Display payments to cents, and inspect the last row for a small residual caused by rounding.

When the Estimate May Differ

Actual loan terms can use calculation conventions that the simple model does not represent. A lender may accrue interest daily, use an irregular first payment period, collect a fee, or set a different final payment. A variable rate can also change the future payment or payoff path. If fees are added to the amount financed, the principal used in the formula should reflect that only when the contract treats them as borrowed principal.

Do not mix percentage formats: enter 8 for 8% in this calculator. In an Excel formula that divides APR by 12, enter 8% as either the decimal 0.08 or a percentage-formatted cell—not the number 8—otherwise the monthly rate is overstated by a factor of 100. Also check that the term is in years for this calculator; a spreadsheet formula using months needs the number 60, not 5, for a five-year term.

Frequently Asked Questions

How do I calculate a term loan payment in Excel?
Use the amortizing-payment formula with a monthly rate and monthly payment count, or Excel’s PMT function. For an APR stored as a decimal, use =-PMT(APR/12,Years*12,Principal); round the result to cents if that is the payment convention used for your totals.

Does Excel have a loan calculator?
Excel includes financial functions such as PMT, which calculates a level periodic payment. A worksheet can combine it with rows for interest, principal, and remaining balance to create an amortization schedule.

Why does my Excel payment differ by a few cents?
Common causes include rounding the rate or intermediate calculations, entering APR in the wrong format, or using a different payment timing convention. The payment shown here is rounded to cents, and total interest uses that rounded payment.

Does total interest include loan fees?
No. The result is interest implied by the principal, rate, term, and payment formula. Fees and other charges are not included unless they are reflected in the loan amount and modeled separately.

Can this calculator handle a variable-rate or interest-only loan?
No. It assumes a fixed rate and equal payments that repay principal and interest over the stated term. A changing rate or interest-only period needs a schedule that models those contract terms explicitly.

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

Formula used

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

Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work

Method: the standard loan formula for this calculation, fixed rate and monthly compounding unless stated, rounded as each result states. Not financial advice; no lender, rate or product is recommended. See formulas and assumptions, the disclaimer or report an error.