calculate-loan

01 Loan payments

Loan Repayment Tracker Excel: Payments and Balance

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

Monthly payment $405.53
Total interest
$4,331.80
= 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 tracker in Excel records each payment and estimates how the balance changes over time. Enter the loan amount, annual interest rate, and term to calculate an estimated monthly payment and total interest; use the schedule to compare each month’s interest, principal, and remaining balance.

Formula

This calculator uses the standard level-payment amortization formula for a fixed-rate loan with monthly payments. It rounds the calculated payment to the nearest cent, then calculates total interest using that rounded payment.

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

P is the starting principal in dollars; APR is the annual percentage rate entered 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 total interest in dollars. For example, an APR of 8 means 8%, so the monthly rate is 8 ÷ 1,200, not 8 ÷ 12.

Worked Example

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

  1. Convert the annual rate to a monthly decimal: r = 8 ÷ 1,200 = 0.0066667.
  2. Find the payment count: n = 5 × 12 = 60.
  3. Apply the payment formula: M = round[20,000 × 0.0066667 ÷ (1 − (1.0066667)−60), 2] = $405.53 per month.
  4. Calculate interest using the rounded payment: I = $405.53 × 60 − $20,000 = $4,331.80.

The figures are estimates under the assumptions below. Actual loan statements can differ because of payment dates, rounding conventions, fees, or a changing rate.

Assumptions

  • The interest rate is fixed for the full term.
  • Payments are equal, made monthly, and treated as occurring at the end of each monthly period.
  • Interest compounds monthly at APR ÷ 12.
  • Fees, taxes, insurance, escrow, and other charges are not included.

Build a Repayment Tracker in Excel

The calculator gives the level payment and total interest. A spreadsheet schedule adds the month-by-month detail that makes a repayment tracker useful. Create columns for payment number, opening balance, scheduled payment, interest, principal repaid, extra payment, and closing balance.

  1. Place the loan amount, APR, term in years, and calculated payment in clearly labelled input cells.
  2. In the first schedule row, set the opening balance equal to the loan amount and the payment number to 1.
  3. Calculate monthly interest as opening balance × APR ÷ 12. If APR is stored as a percentage number such as 8, divide by 100 as well: opening balance × APR ÷ 1,200.
  4. Calculate principal paid as the scheduled payment minus that month’s interest. If tracking extra payments, add the extra amount to principal paid.
  5. Calculate closing balance as opening balance minus principal paid and any extra principal payment. Carry the closing balance forward as the next row’s opening balance.
  6. Fill the formulas down for the intended number of payments and check that the final balance is close to zero. A small difference can arise from cent rounding.

For the example, first-month interest is $20,000 × 8 ÷ 1,200 = $133.33. Of the $405.53 payment, $272.20 goes toward principal, leaving an estimated balance of $19,727.80. In the next row, calculate interest from that new balance—not from the original loan amount.

Reading the Schedule and Tracking Extra Payments

At the start of an amortizing loan, the balance is larger, so the interest portion of a level payment is generally larger too. As principal falls, the interest calculated for each period usually falls, and more of the scheduled payment goes toward principal. The payment amount can remain the same even while its split changes.

An extra-payment column helps show a hypothetical payoff effect, but it must be applied correctly. If the extra payment is directed to principal, subtract it from the balance after calculating the scheduled payment’s interest and principal split. Do not treat an extra principal amount as a replacement for the scheduled payment. Also verify how the lender applies extra payments; a spreadsheet cannot determine the lender’s processing or allocation rules.

For a practical tracker, record actual payment dates and amounts alongside the planned schedule. A missed, late, partial, or differently timed payment can make the estimated balance diverge from the statement. Update the opening balance from a reliable statement when reconciling the spreadsheet, and keep a note of any fees or rate changes that the basic model excludes.

Excel Tracker Versus a Loan Calculator

A calculator is useful for a quick estimate of a level payment and total interest based on a few inputs. An Excel tracker is more flexible: it can display each period, store actual payments, and model extra principal payments or changed assumptions. That flexibility also makes spreadsheet errors easier to introduce.

For a basic tracker, use formulas rather than typing each month’s amounts manually. Lock input-cell references when copying formulas down, and label whether APR is stored as 8 or as 8%. Those formats require different spreadsheet formulas: if the cell contains 8%, multiply by that rate and divide by 12; if it contains 8, divide by 1,200. Check at least the first row against a hand calculation, and compare the estimated ending balance with a lender statement when available.

Common Spreadsheet Errors and Limits

  • Wrong rate conversion: Dividing a whole-number APR by 12 without converting percent to decimal produces an incorrect interest charge.
  • Using the original balance every month: Interest should be calculated from that period’s opening balance.
  • Rounding too early: Round displayed currency to cents, but avoid rounding intermediate rate calculations more than necessary. Lender systems may use their own precision and payment-date conventions.
  • Assuming every loan is fixed-rate and fully amortizing: Variable-rate loans, interest-only periods, balloon payments, and irregular schedules need a model suited to their terms.
  • Ignoring charges or timing: This estimate does not include fees, insurance, taxes, or the effect of payments arriving on different dates.

The formula is not a statement payoff quote. For an exact payoff amount or an explanation of how a payment was applied, use the figures supplied by the lender or servicer. Get the actual rate, payment schedule, and charges from the lender rather than assuming the example inputs match a real loan. This result is an estimate, and the lender’s disclosure gives the exact figures.

Frequently Asked Questions

How do I make a loan repayment tracker in Excel?
Set up input cells for principal, APR, term, and payment, then create monthly rows for opening balance, interest, principal, and closing balance. Link each row’s opening balance to the previous row’s closing balance.

What Excel formula calculates monthly loan interest?
For APR entered as a whole percentage number, use opening balance × APR ÷ 1,200. If APR is stored as a percentage-formatted decimal such as 8%, use opening balance × APR ÷ 12.

Why does my spreadsheet show a small balance at the end?
Rounding payments and interest to cents each month can leave a small remainder. Different payment timing or an input mismatch can also explain a larger discrepancy; check the lender’s balance and schedule.

Does an extra payment reduce loan interest?
If it is applied to principal, a lower balance generally means less interest accrues in later periods under a fixed-rate amortization model. The actual effect depends on the loan terms and how the lender applies the payment.

Can this tracker calculate a variable-rate loan?
Not accurately as a single fixed-rate schedule. A variable rate requires the applicable rate and payment rules for each period, which this calculator does not model.

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 | $ | 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.

More in this section

The whole Loan payments section →