calculate-loan

01 Loan payments

Payment Schedule Spreadsheet: Calculate Loan Payments

Calculator No. 01-15249 · 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 payment schedule spreadsheet lays out each loan payment by date, showing how much goes to principal, how much goes to interest, and what balance remains. Use this calculator to estimate the fixed monthly payment and total interest from a loan amount, annual percentage rate (APR), and term; the spreadsheet-style schedule itself can then be built from those figures.

Formula

The calculator uses the standard fixed-payment amortization formula. It assumes equal payments made monthly, with interest calculated at a monthly rate derived from the APR.

  • Monthly payment: M = P × r ÷ [1 − (1 + r)−n]
  • Monthly rate: r = APR ÷ 1,200
  • Number of payments: n = term in years × 12
  • Total interest: I = M × n − P

Here, P is the starting loan principal in dollars, APR is the annual percentage rate expressed as a percentage, r is the monthly rate as a decimal, n is the number of monthly payments, M is the monthly payment in dollars, and I is the estimated total interest in dollars. The displayed monthly payment is rounded to cents; total interest is calculated using that rounded payment.

Worked Example

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

  1. Convert APR to a monthly decimal rate: r = 8 ÷ 1,200 = 0.0066667.
  2. Find the number of payments: n = 5 × 12 = 60.
  3. Substitute into the payment formula: M = 20,000 × 0.0066667 ÷ [1 − (1.0066667)−60].
  4. The unrounded result is approximately $405.53, so the monthly payment displayed to the nearest cent is $405.53.
  5. Calculate total interest using the rounded payment: I = $405.53 × 60 − $20,000 = $4,331.80.

Assumptions

  • The interest rate stays fixed for the full term.
  • Payments are equal, monthly, and made at the end of each period.
  • Interest compounds monthly using APR divided by 12.
  • The estimate excludes fees, taxes, insurance, PMI, escrow, and any other charges or adjustments not entered.

What a Payment Schedule Spreadsheet Shows

A schedule, often called an amortization table, expands the monthly payment into a row for each payment period. Typical columns include the payment number or due date, opening balance, payment amount, interest portion, principal portion, and closing balance. A final total can summarize payments and interest across the full term.

For a standard fixed-rate loan, the payment amount stays level, but its components change. Interest for a period is generally calculated on the balance before that payment. Early in the schedule, the balance is larger, so more of the payment goes to interest. As principal is repaid, the interest portion tends to fall and the principal portion rises. The remaining balance should approach zero after the final scheduled payment, subject to rounding and the lender’s payment method.

How to Build and Read the Schedule

Start with the lender’s actual principal, APR, term, payment frequency, and first payment date. A basic monthly schedule can then be organized in chronological rows:

  1. Enter the opening balance. For the first row, this is the starting principal.
  2. Calculate that period’s interest as opening balance × monthly rate.
  3. Subtract the interest from the scheduled payment to get principal repaid.
  4. Subtract principal repaid from the opening balance to get the closing balance.
  5. Use that closing balance as the next row’s opening balance, and repeat.

For example, at a 0.6667% monthly rate, the first month’s interest on $20,000 is about $133.33. With a $405.53 payment, about $272.20 goes to principal, leaving roughly $19,727.80. A full spreadsheet may differ by a few cents because lenders can use their own day-count conventions, rounding rules, dates, or payment allocation methods.

Inputs, Uses, and Important Limits

This estimate is useful for understanding the arithmetic of a fixed-rate loan, comparing a stated payment with a calculation, or preparing a draft schedule. It is not a lender-issued payoff statement. For a real loan, use the figures and timing described in the lender’s disclosure or account information; APR may reflect costs in ways that do not match the simple monthly-interest assumption used here.

  • Use the correct amount: distinguish the amount borrowed from a purchase price or a balance that includes other charges.
  • Enter APR as a percentage: enter 8 for 8%, not 0.08. The formula converts it to a monthly decimal.
  • Match the term units: this calculator takes years, while a schedule typically counts months. Multiply years by 12 to get payment count.
  • Check payment frequency: biweekly, weekly, irregular, or interest-only payments do not follow this monthly formula as written.
  • Expect rounding differences: rounding each row’s interest to cents can shift the final balance slightly. A real final payment may be adjusted.

Extra payments, missed payments, variable rates, deferred payments, fees added to principal, and changes in payment dates can all alter the balance path. The formula does not model those events. A spreadsheet can include additional columns or revised rules, but the underlying loan terms must be known to calculate them accurately.

Spreadsheet Versus Calculator

A calculator gives a quick payment and interest estimate from a few inputs. A spreadsheet makes the sequence visible and can be customized to show dates, balances, or extra payments. Neither replaces the lender’s official figures. A spreadsheet also needs careful formulas and consistent references: an incorrect opening balance, APR conversion, or payment count can create a schedule that looks plausible but is wrong.

One common misconception is that each payment reduces principal by the same amount. With a level-payment, fixed-rate loan, the total payment is generally level, but the principal share changes as the balance declines. Another is that total interest is simply the APR multiplied by the original principal and years; amortization means interest is calculated on a declining balance, so that shortcut is not the schedule formula.

Frequently Asked Questions

What is a payment schedule spreadsheet?
A table that lists each planned payment and separates it into principal and interest, while tracking the remaining balance over time.

How do I calculate a monthly loan payment?
Convert APR to a monthly rate by dividing the percentage by 1,200, convert the term to months, then apply the fixed-payment formula shown above.

Why does the interest portion change each month?
For a standard amortizing loan, period interest is based on the outstanding balance. As payments reduce that balance, the interest calculated for later periods generally decreases.

Can I use this schedule for extra payments?
Not without changing the calculation. Extra principal payments affect later balances and interest, so each subsequent row must use the updated balance and the loan’s applicable payment rules.

Why might my lender’s payment differ?
The lender may use different timing, rounding, fees, payment dates, or rate and APR conventions. Check the lender’s disclosed payment and terms for the figures that apply to the account.

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

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 →