calculate-loan

01 Loan payments

Debt Repayment Calculator Excel: How to Build One

Calculator No. 01-25277 · 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 debt repayment calculator in Excel is a spreadsheet that works out how much you need to pay each month to clear one or more debts by a target date, and how much interest you will pay along the way. You enter the amount owed, the annual interest rate, and the monthly payment (or the number of months), and the sheet calculates the payoff time, total interest, and a full amortization schedule. Below is a working version of the core calculation, followed by a guide to building your own in Excel.

Formula

The calculator above uses the standard level-payment formula for a fixed-rate loan:

M = P × (r × (1 + r)^n) / ((1 + r)^n − 1)

Where:

  • M = monthly payment (in dollars)
  • P = principal, the amount you owe (in dollars)
  • r = monthly interest rate = annual percentage rate (APR) divided by 12, then divided by 100 (e.g., 8% APR → 0.08/12 = 0.006666…)
  • n = total number of monthly payments = loan term in years × 12

The same formula works for any debt with a fixed rate and fixed payment, not just formal loans. For credit cards and other revolving debt, the rate and minimum payment can change, so the result is only an estimate.

Worked Example

Take the default values from the calculator: a $20,000 loan at 8% APR for 5 years.

  1. Convert APR to a monthly rate: r = 8 / 1200 = 0.0066667
  2. Total number of payments: n = 5 × 12 = 60
  3. Compute (1 + r)^n: (1.0066667)^60 ≈ 1.48985
  4. Plug into the formula: M = 20000 × (0.0066667 × 1.48985) / (1.48985 − 1) = 20000 × (0.0099323) / 0.48985 = 20000 × 0.020276 = 405.53
  5. Total paid over 5 years: 405.53 × 60 = 24,331.80
  6. Total interest: 24,331.80 − 20,000 = 4,331.80

The calculator shows a monthly payment of $405.53 and total interest of $4,331.80, matching the hand calculation.

Assumptions

  • The interest rate is fixed for the entire term.
  • Payments are made at the end of each month, starting one month after the loan begins.
  • Interest compounds monthly at APR / 12.
  • No fees, late charges, taxes, insurance, or other costs are included.
  • The payment is rounded to the cent in the schedule; the total uses that rounded payment.

How to Build the Calculator in Excel

You can recreate the same logic in a spreadsheet in a few steps:

  1. Set up input cells – Put the loan amount, APR (as a percentage, e.g., 8), and term in years in separate cells. Label them clearly.
  2. Calculate the monthly rate – In a cell, enter =APR/1200 (assuming the APR cell is formatted as a number like 8, not 0.08).
  3. Calculate the number of payments – =term_years*12.
  4. Compute the monthly payment – Use Excel’s built-in PMT function: =PMT(monthly_rate, num_payments, -principal). The negative sign makes the result positive.
  5. Total interest – =payment * num_payments - principal.
  6. Build an amortization schedule – List payment numbers 1 to n. For each row, compute interest = previous balance × monthly rate, principal paid = payment − interest, new balance = previous balance − principal paid. Use formulas that reference the previous row.

Once the schedule is built, you can add a chart showing the balance declining over time.

Common Mistakes When Using a Debt Calculator

  • Mixing up APR and monthly rate – If you enter 8% as 8 instead of 0.08, the formula breaks. Always divide by 1200 for monthly.
  • Ignoring payment timing – The formula assumes payments at the end of each month. If you pay at the beginning, interest accrues slightly less.
  • Rounding too early – Round only the final payment, not intermediate interest calculations, or the schedule will drift.
  • Forgetting that credit card rates change – A fixed-rate formula won’t match a card with a promotional rate or variable APR.
  • Using the minimum payment as a fixed payment – Minimums often decrease as the balance drops, so a fixed-payment calculator overestimates the speed of payoff.

How to Use the Result

The monthly payment tells you what a fixed-payment plan would cost. The total interest figure helps you compare different payoff speeds. For example, paying $500 per month on a $20,000 debt at 8% APR would clear it in about 44 months and cost roughly $2,000 in interest, versus 60 months and $4,332 at the minimum $405.53 payment. You can run the same calculator with different payment amounts to see the trade-off between monthly cash flow and total interest.

If you are juggling multiple debts, you can list each one in a spreadsheet and use the same formula for each. The total monthly payment is the sum of the individual payments. Some people choose to target the highest-rate debt first (the avalanche method) or the smallest balance first (the snowball method); the calculator can show the total interest for each strategy, but it does not tell you which is better for your situation.

Limitations of the Formula

This calculation assumes a constant interest rate and a constant payment. Real debts often have:

  • Variable rates that change with an index or the prime rate
  • Fees for late payments, balance transfers, or cash advances
  • Minimum payments that are a percentage of the balance, not a fixed dollar amount
  • Interest that compounds daily (common with credit cards) rather than monthly

For those cases, the result is an approximation. To get an exact figure, you would need to model each month’s rate and payment individually. The formula also does not account for extra one-off payments you might make, though you can add those to the schedule manually.

Frequently Asked Questions

Can I use this calculator for a credit card?

Yes, but only as an estimate. Credit cards typically compound daily and have variable rates, so the monthly-compounding formula will be slightly off. Also, minimum payments usually change as the balance drops, so a fixed-payment calculation overestimates how fast you can pay off the card if you only make the minimum.

What is the difference between APR and interest rate?

APR (annual percentage rate) includes the interest rate plus certain fees and costs spread over the loan term. For a loan with no fees, APR equals the interest rate. The calculator uses APR as the annual rate, which is the standard way to compare loans.

How do I add extra payments to the Excel calculator?

In the amortization schedule, create a column for extra payments. Each month, subtract the extra amount from the balance before computing the next month’s interest. You can also use a one-time lump-sum payment by adding it to a specific row. The schedule will then show a shorter payoff time and less total interest.

Why does the calculator show a different total than my lender’s statement?

Lenders may round differently, charge fees, or use a different compounding method. Also, if your payment date falls on a weekend or holiday, the interest accrual can shift slightly. The result here is an estimate; the lender’s disclosure gives the exact figures.

Is it better to pay off a debt faster or invest the money?

That depends on the interest rate on your debt versus the return you expect from investing. Mathematically, if the debt’s APR is higher than your investment return, paying off the debt saves more money. But this is a personal decision that also involves risk tolerance and cash flow needs. A calculator can show the numbers, but it cannot make the choice for you.

This calculator provides an estimate based on the inputs you enter. Your lender’s disclosure will show the exact payment and total cost for your specific loan.

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 →