calculate-loan

01 Loan payments

Loan Repayment Spreadsheet: Payments and Interest

Calculator No. 01-15149 · Updated · Formula and assumptions shown below

Monthly payment $405.53
Total interest
$4,331.67
= Monthly payment × loan term × 12 − loan amount
Loan amount
$20,000
Input
Interest rate (APR)
8 %
Input
You enter
Loan amount ($), Interest rate (APR) (%), Loan term (years)
You get
Monthly payment ($), Total interest ($)
Rounding
2 dp

Estimate for education, not financial advice. The result follows from the numbers you enter and the formula shown. It is not a quote or an offer, and it does not include fees, taxes or insurance unless the calculator asks for them. Ask your lender for the exact figures.

A loan repayment spreadsheet lays out each scheduled payment, showing how much goes to interest, how much reduces the balance, and what remains owed. Use the calculator below to estimate the fixed monthly payment and total interest from a loan amount, annual APR, and term; it assumes equal monthly payments and a fixed rate.

Formula

The calculator uses the standard fixed-payment amortization formula. Let P be the amount borrowed in dollars, a the annual APR in percent, r = a / 1200 the monthly interest rate as a decimal, and n = 12 × y the number of monthly payments, where y is the term in years. The monthly payment is M = P × r / (1 − (1 + r)−n). Total interest is calculated from the rounded monthly payment: I = M × n − P.

The APR is converted to a monthly rate by dividing by 12 and then by 100. The spreadsheet’s figures are estimates because real loan schedules may use different payment dates, rounding rules, fees, or rate structures. The input limits allow positive principal, an APR from 0.01% through 100%, and terms from 0.0833 through 50 years.

Worked Example

Using the calculator’s defaults, the principal is $20,000, the APR is 8%, and the term is 5 years. The number of payments is 5 × 12 = 60. The monthly rate is 8 / 1200 = 0.0066667.

  1. Insert the values in the payment formula: $20,000 × 0.0066667 / [1 − (1 + 0.0066667)−60].
  2. The monthly payment rounds to $405.53.
  3. Total interest uses that rounded payment: $405.53 × 60 − $20,000 = $4,331.80.

The displayed total follows the required convention of multiplying the payment rounded to cents by the number of payments, then subtracting principal. A lender’s final schedule may differ by a small amount because its actual payment amount and rounding can differ.

Assumptions

  • The interest rate stays fixed for the full term.
  • There are 12 equal payments per year, with payments made monthly.
  • Interest is modelled monthly at APR divided by 12; the calculation treats each payment as part of a standard amortizing schedule.
  • Fees, taxes, insurance, private mortgage insurance, escrow, and any other charges are not included.

What a Loan Repayment Spreadsheet Shows

A repayment spreadsheet, often called an amortization schedule, expands the overall payment estimate into one row per payment period. A useful schedule commonly includes the payment number or date, opening balance, scheduled payment, interest portion, principal portion, and closing balance. It lets a borrower see how the debt changes over time, rather than viewing only the monthly amount.

For a standard fixed-rate loan, each period’s interest is calculated on the remaining balance. The scheduled payment is then split between interest and principal. Early in the schedule, the balance is larger, so interest takes a larger share of the payment. As principal falls, the interest portion usually falls and more of the same payment reduces principal. This is why a level monthly payment does not mean equal principal reductions each month.

The calculator above returns the payment and overall interest, not a downloadable or month-by-month spreadsheet. Those two figures give a quick cross-check for a spreadsheet: over the full schedule, total payments should equal principal plus interest, subject to rounding and any charges not modelled.

How to Build or Check a Schedule

A spreadsheet can be created with one row for each payment. Begin with the actual principal, APR, payment frequency, and term shown in the loan documents. For a monthly schedule, calculate interest for a period as opening balance multiplied by the monthly rate. Subtract that interest from the scheduled payment to find principal repaid, then subtract principal repaid from the opening balance to get the closing balance.

  1. Record the initial balance as the loan principal.
  2. Calculate monthly interest as opening balance × APR / 1200.
  3. Calculate principal paid as scheduled payment − period interest.
  4. Calculate closing balance as opening balance − principal paid; use that as the next row’s opening balance.
  5. Repeat for each payment, then compare the final balance and total paid with the lender’s schedule.

For example, with a $20,000 balance at 8% APR, the first month’s simplified interest estimate is $20,000 × 0.08 / 12, or about $133.33. If the payment is $405.53, about $272.20 goes toward principal and the remaining balance is about $19,727.80. Actual lender calculations can depend on dates and rounding, so this example illustrates the method rather than reproducing a specific account statement.

Extra Payments, Variable Rates, and Common Errors

A basic fixed-payment calculator does not model additional principal payments. In a spreadsheet, an extra payment generally reduces the balance sooner, which changes later interest calculations. The result depends on how the loan applies extra money and whether the scheduled payment or payoff date changes. Entering an extra amount as though it were part of the regular payment can therefore produce an inaccurate payoff estimate.

Other common sources of mismatch include using the loan’s nominal rate instead of its disclosed APR, entering a term in months where the sheet expects years, and rounding monthly interest too early. Keep full precision in intermediate spreadsheet cells and round displayed currency to cents. Also check whether a stated payment includes charges outside principal and interest.

A variable-rate loan cannot be represented accurately for its entire term with one fixed APR unless the rate truly remains unchanged. A schedule can illustrate a chosen rate scenario, but it is not a forecast of future rates or payments. Likewise, a loan with irregular payment dates, interest-only periods, a balloon payment, or fees financed into the balance needs a formula and inputs that reflect those specific terms.

Using the Estimate Carefully

Use figures from the lender’s disclosure or loan documents for the amount, APR, and term rather than assuming an example rate applies to your situation. APR and the interest rate used to accrue interest can have different meanings in a loan’s documents, particularly where costs are included in the APR. This calculator uses the entered APR as the fixed annual rate for its arithmetic; it does not separately model fees or other APR components.

Check the schedule against the payment frequency and first payment date. Monthly calculations are not interchangeable with daily accrual or other payment conventions. If the lender’s quoted payment differs, compare the stated principal-and-interest amount first, then check financed costs, timing, rounding, and any payment structure not represented here. The calculator is for educational arithmetic, not financial advice. The result is an estimate, and the lender’s disclosure gives the exact figures.

Frequently Asked Questions

What is a loan repayment spreadsheet?
A loan repayment spreadsheet is a table that tracks each payment and shows its interest portion, principal portion, and remaining balance. It is also known as an amortization schedule.

How do I calculate a monthly loan payment?
For a fixed-rate loan with equal monthly payments, convert annual APR to a monthly decimal rate by dividing by 1200, then use the amortization payment formula shown above with the principal and number of monthly payments.

Why does the interest portion change if the payment stays the same?
Interest is calculated on the outstanding balance. As principal is repaid, the balance shrinks, so less interest accrues and a larger share of a level payment can reduce principal.

Does this calculator include extra payments or fees?
No. It models regular payments on a fixed-rate loan and excludes extra payments, fees, taxes, insurance, and escrow. Those items require additional inputs and may change the schedule.

Why might my spreadsheet differ from the lender’s schedule?
Differences can come from the actual rate basis, payment dates, daily rather than monthly interest accrual, rounding, fees, or loan features such as a variable rate. Compare the assumptions and figures in the lender’s disclosure.

Related

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 | $ | p * (apr / 1200) / (1 - (1 + apr / 1200) ^ -(yrs * 12)) | 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.

More in this section

The whole Loan payments section →