calculate-loan

01 Loan payments

Loan EMI Calculator Excel: Formula and Example

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

Monthly payment 405.53
Total interest
4,331.80
= Monthly payment × loan term × 12 − loan amount
Loan amount
20000
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 in Excel estimates the fixed monthly payment on a fully amortizing loan from its principal, annual interest rate, and term. Enter those details to calculate the payment and total interest; the worksheet below uses the standard monthly-payment formula.

Formula

The fixed monthly payment, often called an EMI (equated monthly instalment), is calculated as:

Payment = P × r ÷ [1 − (1 + r)−n]

In this calculator, the payment is rounded to cents. The total-interest estimate then uses that rounded monthly payment:

Total interest = Payment × n − P

  • P is the amount borrowed, entered in the currency you are using.
  • APR is the annual percentage rate entered as a percentage, such as 8 for 8%.
  • r is the monthly interest rate, calculated as APR ÷ 1200. For an 8% APR, r is 0.0066667 per month.
  • n is the number of monthly payments, calculated as years × 12.
  • Payment is the level monthly amount, in the same currency as P.

The formula assumes a positive rate and equal payments made monthly. The worksheet sets the minimum APR to 0.01% because this version divides by the monthly rate. Its term input is in years, from 0.0833 to 50; the lower limit represents approximately one month.

Worked Example

Using the calculator’s default values—loan amount 20,000, APR 8%, and term 5 years—first convert the rate and term:

  • Monthly rate: r = 8 ÷ 1200 = 0.0066667.
  • Number of payments: n = 5 × 12 = 60.
  • Payment before cent rounding: 20,000 × 0.0066667 ÷ [1 − (1 + 0.0066667)−60] ≈ 405.528.
  • Monthly payment rounded to cents: 405.53.
  • Total interest using the rounded payment: 405.53 × 60 − 20,000 = 4,331.80.

The estimate is therefore a monthly payment of 405.53 and total interest of 4,331.80, in the currency used for the loan amount. The exact displayed currency symbol depends on how the calculator or spreadsheet is formatted.

Assumptions

  • The interest rate stays fixed for the entire term.
  • Payments are equal, made monthly, and begin one month after the loan starts.
  • Interest compounds monthly at APR ÷ 12.
  • Fees, taxes, insurance, PMI, escrow, variable-rate changes, and payment-date differences are not included.

How to Build the EMI Formula in Excel

Excel’s PMT function can calculate a level payment. If the principal is in B1, annual APR in B2 as a percentage (for example, 8%), and term in years in B3, use:

=ROUND(-PMT(B2/12,B3*12,B1),2)

Excel treats the amount borrowed as money received and the repayments as money paid out, so PMT commonly returns a negative number when the principal is entered as positive. The minus sign makes the payment display as a positive amount. B2 must be stored as 8% or 0.08—not the number 8—because Excel percentage formatting represents 8% as 0.08. If you enter 8 as a whole number instead, use =ROUND(-PMT(B2/1200,B3*12,B1),2).

To estimate total interest consistently with the rounded payment, multiply the rounded payment by the number of monthly payments and subtract the principal. For the example, that is =payment_cell*B3*12-B1. The result can differ slightly from a lender’s final schedule because actual contracts may round each period differently or include charges the formula does not model.

When to Use the Estimate

This calculation is useful for understanding how principal, rate, and term relate in a fixed-payment loan. It can also help you check a spreadsheet or understand a quoted payment, provided you use the same loan amount, rate basis, and payment period. It is not a lender quote or an approval calculation.

Changing one input at a time illustrates the trade-off built into amortization. With the amount and rate unchanged, a longer term usually lowers the scheduled monthly payment but increases the number of payments over which interest accrues. A higher rate raises the calculated payment for a given principal and term. These are mathematical effects under the stated assumptions, not a recommendation about which terms to select.

Common Excel Mistakes and Limits

  • Mixing percentage formats: A rate entered as 8% is 0.08 in Excel. Divide that by 12 for a monthly rate. If the cell contains the number 8, divide by 1200 instead.
  • Using years as months: PMT needs the number of payment periods. For a monthly loan, multiply years by 12.
  • Forgetting the payment sign: PMT may return a negative value because of its cash-flow convention. Use a leading minus sign if you want to show the instalment as positive.
  • Rounding too early or inconsistently: The example rounds the monthly payment to cents, then uses that payment to estimate total interest. A lender’s schedule may calculate and round interest and principal separately each month, producing a small difference.
  • Assuming every borrowing cost is included: PMT models principal and interest only. It does not automatically include lender charges, insurance, taxes, or other amounts.

The formula is for a fully amortizing loan with a fixed rate and regular monthly payments. It does not model an interest-only period, a balloon payment, irregular payment dates, extra repayments, variable rates, or fees added to the balance. For a zero-interest loan, the payment is simply principal divided by the number of payments; the calculator’s minimum rate is above zero, so that special case is not represented by its formula.

Frequently Asked Questions

What is EMI?
EMI means equated monthly instalment: a scheduled monthly payment that is level in this standard fixed-rate calculation. Each payment covers interest due for the period and repays some principal.

How do I calculate EMI in Excel?
Use =ROUND(-PMT(annual_rate/12, years*12, loan_amount),2) when the annual rate is stored as a percentage such as 8%. If the rate is entered as the whole number 8, divide by 1200 instead of 12.

Does the EMI include fees and insurance?
No. The formula estimates principal and interest only. Add any relevant charges separately using figures supplied for the specific loan; this calculator does not estimate them.

Why does Excel show a negative payment?
Excel’s PMT function follows cash-flow signs: a positive loan amount represents money received, while repayments are money paid out. Putting a minus sign before PMT displays the payment as a positive number.

Why might the lender’s payment differ?
The lender may use a different rate convention, payment schedule, rounding method, or include charges not represented here. Check the lender’s disclosed loan amount, rate, payment dates, and payment breakdown when comparing figures.

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 | - | 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 →