calculate-loan

01 Loan payments

Loan EMI Calculator With Prepayment in Excel

Calculator No. 01-15053 · 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 EMI calculator with prepayment in Excel estimates your regular monthly installment, then shows how extra payments may change the payoff time and interest total. Enter the loan amount, annual rate, term, and planned extra payment; the calculator below gives the standard EMI, while the Excel model and formulas explain how to track prepayments month by month.

Formula

The block calculates a fixed monthly payment and the interest implied by that payment over the full term, without prepayments. The payment formula is:

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

  • P is the starting loan principal, in dollars.
  • r is the monthly interest rate, calculated as APR ÷ 1,200. APR is entered as a percentage per year.
  • n is the number of monthly payments, calculated as years × 12.
  • EMI is the level monthly payment, in dollars.

Total interest for the scheduled term is EMI × n − P. The calculator rounds displayed outputs to cents. This simple calculator is the baseline; it does not include a prepayment input, because extra payments need to be applied to a changing balance each month. The Excel method below provides that schedule.

Worked Example

Using the calculator defaults: principal P = $20,000, APR = 8%, and term = 5 years.

  1. Monthly rate: r = 8 ÷ 1,200 = 0.0066666667.
  2. Number of payments: n = 5 × 12 = 60.
  3. Substitute: EMI = 20,000 × 0.0066666667 ÷ (1 − (1 + 0.0066666667)^−60).
  4. The monthly payment rounds to $405.53.
  5. Scheduled total paid is $405.53 × 60 = $24,331.80; estimated total interest is $24,331.80 − $20,000 = $4,331.80.

Assumptions

  • The interest rate is fixed throughout the term.
  • Payments are made monthly, at the end of each period.
  • Interest compounds monthly using APR divided by 12.
  • Fees, taxes, insurance, PMI, escrow, and lender-specific rounding or payment-allocation rules are not included.

Build a Prepayment Schedule in Excel

Excel can model the extra payment by recalculating interest on the opening balance for each period, then applying the regular payment and extra amount. Set up cells for the principal, APR, term in months, and monthly prepayment. For example, put those inputs in B1:B4 respectively. Use the calculator’s EMI formula in B5:

=-PMT(B2/12,B3,B1)

Excel’s PMT function generally returns a negative cash flow when the principal is entered as positive, so the leading minus sign displays the payment as a positive amount. Format the result to two decimal places. Create a schedule with columns for month, opening balance, interest, regular payment, extra payment, principal reduction, and closing balance. In the first row, use these formulas, assuming the first schedule row is row 9:

  • Month: 1; in the next row use =A9+1.
  • Opening balance: =$B$1; subsequent rows use the prior closing balance.
  • Interest: =B9*$B$2/12.
  • Regular payment: =MIN($B$5,B9+C9), so the last payment cannot exceed the amount due.
  • Extra payment: =MIN($B$4,MAX(0,B9+C9-D9)), which avoids an extra payment larger than the remaining amount owed.
  • Principal reduction: =D9+C9-B9 is not correct; use =D9+E9-C9. This equals total payment minus interest.
  • Closing balance: =MAX(0,B9+C9-D9-E9).

Copy the formulas down for enough months to reach a zero balance. Sum the interest column for the schedule’s estimated interest total, and note the last month with a balance before payoff. The regular EMI stays fixed in this model; a prepayment shortens the payoff period unless you separately change the regular installment. Compare against a second schedule with the extra-payment cell set to zero to see the modeled difference.

How to Read the Results

The EMI is the scheduled amount before any optional extra payment. In an amortizing loan, each payment covers interest accrued since the previous payment, and the rest reduces principal. Early in the schedule, the balance is larger, so the interest portion is usually larger; as principal falls, the interest portion generally falls too.

An extra payment reduces the balance only when it is actually credited to principal under the loan’s payment rules. In the spreadsheet, the interest for a month is calculated first, then both regular and extra payments reduce the amount due. A lower balance can reduce future interest, but the exact result depends on payment dates, rounding, and the lender’s allocation of payments.

Common Excel Errors and Limits

  • Entering 8 instead of 8%: Keep APR in percentage points in the input cell, and divide by 12 for a monthly rate if using Excel’s percent format, or by 1,200 if entering 8 as a plain number.
  • Mixing years and months: PMT needs a number of periods. For a five-year monthly loan, enter 60 periods, not 5.
  • Rounding every intermediate value: Display cents, but retain the full precision of formula cells unless the contract specifies a rounding convention. Actual final payments can differ by cents.
  • Assuming extra payment always shortens the term: The schedule assumes the regular payment remains unchanged and extras reduce principal. A lender may instead adjust future payments, apply funds differently, or require instructions.
  • Using the formula for a changing rate: The standard EMI formula assumes one fixed rate and equal monthly periods. Variable rates, irregular payment dates, fees financed into the loan, and payment holidays need a more detailed model.

Use the lender’s statement or payment instructions to confirm how an additional amount must be submitted and how it will be credited. A spreadsheet is a planning estimate, not a record of the lender’s actual balance.

Frequently Asked Questions

Can I calculate EMI with prepayment in Excel?
Yes. Calculate the regular EMI with PMT, then build a monthly amortization schedule that adds the extra payment after that month’s interest is calculated. Continue until the balance reaches zero.

Does a prepayment reduce EMI or loan tenure?
It depends on the loan terms and how the lender handles the payment. A schedule that holds EMI constant models a shorter payoff time; one that recalculates the installment models a different result.

How do I calculate interest for each month?
Multiply the opening balance for that month by the monthly rate. With an APR entered as a plain number such as 8, the monthly rate is 8 divided by 1,200.

Why does Excel PMT show a negative payment?
Excel uses cash-flow signs: money received and money paid have opposite signs. Use =-PMT(rate,periods,principal) to display the installment as a positive amount when principal is entered as positive.

Does this estimate include prepayment fees?
No. The calculator and sample schedule do not model fees or lender-specific payment rules. Check the loan documents or ask the lender for the applicable figures and instructions.

This result is an estimate, and 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 →