calculate-loan

01 Loan payments

EMI Repayment Calculator in Excel: Formula and Example

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

Monthly EMI 405.53
Total interest
4,331.80
= Monthly emi × 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 EMI, 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.

An EMI repayment calculator in Excel estimates a loan’s fixed monthly instalment from the principal, annual interest rate and loan term. Enter those three values to calculate the payment and total interest; the worksheet formula and example below use a standard monthly reducing-balance method.

Formula

The fixed monthly payment, often called an equated monthly instalment or EMI, is calculated using:

EMI = round(P × r ÷ [1 − (1 + r)−n], 2)

  • P is the amount borrowed, in your currency.
  • r is the monthly interest rate as a decimal, calculated as annual APR ÷ 1200. For an 8% APR, r = 8 ÷ 1200.
  • n is the number of monthly payments, calculated as years × 12.
  • EMI is the monthly payment, rounded to two decimal places.

Total interest in this estimate is EMI × n − P. Because it uses the rounded monthly payment, it reflects the payment schedule represented by the calculator. A lender’s final instalment can differ slightly because of its rounding and payment-date calculations.

Worked Example

Using the calculator’s default values—principal of 20,000 in your currency, APR of 8%, and a 5-year term:

  1. Convert the annual APR to a monthly decimal rate: r = 8 ÷ 1200 = 0.0066667.
  2. Convert the term to payments: n = 5 × 12 = 60.
  3. Substitute into the payment formula: 20,000 × 0.0066667 ÷ [1 − (1.0066667)−60].
  4. Round the monthly payment to two decimal places: 405.53 in your currency.
  5. Calculate estimated total interest using the rounded payment: 405.53 × 60 − 20,000 = 4,331.80.

The results are an estimated monthly EMI of 405.53 and estimated total interest of 4,331.80, both in your currency.

Assumptions

  • The interest rate stays fixed for the whole repayment term.
  • Payments are equal, made monthly, and paid at the end of each monthly period.
  • Interest accrues monthly at the annual APR divided by 12.
  • Fees, taxes, insurance, PMI and escrow are not included. The calculator has no inputs for these charges.

Build the EMI Formula in Excel

In a spreadsheet, place the loan amount in cell B1, annual APR as a percentage in B2, and the term in years in B3. One formula for the monthly payment, rounded to cents, is:

=ROUND(B1*(B2/1200)/(1-(1+B2/1200)^(-B3*12)),2)

Excel stores a percentage such as 8% as 0.08. The formula above expects B2 to contain the percentage value formatted as a percentage, so its internal value is 0.08. If you enter the number 8 instead, use B2/1200 as shown, because 8 divided by 1200 is the monthly decimal rate. Do not combine the two conventions: entering 0.08 as a plain number and dividing it by 1200 would make the rate 100 times too small.

For total interest, if the rounded EMI is in B4, use =B4*B3*12-B1. Format money cells to the relevant currency and two decimal places. Cell formatting changes how a value appears; the ROUND function is what rounds the payment used in the total calculation.

How to Read the Result

The EMI is the amount of each scheduled monthly payment under the stated assumptions, not the amount of principal repaid each month. Early in a standard amortizing loan, a larger share of each payment goes toward interest; as the balance declines, the interest portion generally falls and more of the payment reduces principal. The combined instalment stays level when the rate and payment schedule remain unchanged.

Total interest is the estimated cost of borrowing over the full term, calculated as the sum of the rounded instalments minus the original principal. It is separate from the EMI: a monthly payment alone does not show how much will be paid across the complete schedule. The result also does not represent the full cost of a loan if separate charges apply.

Common Excel Errors and Limits

  • Mixing APR formats: Use either 8 as a whole-number percentage or 8% as a spreadsheet percentage, and ensure the formula matches the entry. The formula above uses the whole-number value 8 if the cell is not stored as a percent; for a cell containing 8%, use =ROUND(B1*B2/12/(1-(1+B2/12)^(-B3*12)),2).
  • Using years as months: The formula needs the number of monthly payments. Multiply a term in years by 12.
  • Rounding too early: Keep the rate and intermediate calculations unrounded. Round the payment to cents, then use that payment when estimating total interest.
  • Assuming every loan has fixed monthly payments: Variable rates, irregular payment dates, fees added to the balance, interest-only periods and changing instalments are not represented by this formula.
  • Expecting an exact lender schedule: A lender may use different day-count conventions, payment dates, rounding or fees. The spreadsheet is an estimate, not a payoff quote or account statement.

For comparison, Excel’s PMT function can also calculate a level payment: =-PMT(B2/12,B3*12,B1) when B2 contains a true percentage such as 8%. The minus sign displays the payment as a positive amount when the borrowed principal is entered as a positive number. The explicit formula is useful when you want to see the calculation; PMT is a convenient spreadsheet function for the same basic fixed-rate arithmetic.

Frequently Asked Questions

What does EMI mean?
EMI means equated monthly instalment. It is a scheduled monthly payment intended to cover interest due and repay part of the principal under the loan’s repayment terms.

How do I calculate EMI in Excel?
Use the fixed-payment formula with the principal, monthly interest rate and total number of payments. For a whole-number APR and term in years, the formula is =ROUND(P*(APR/1200)/(1-(1+APR/1200)^(-Years*12)),2), replacing the names with cell references.

Does EMI include interest and principal?
In a standard amortizing loan, each instalment includes interest and principal repayment. The proportion of each changes over time, even though the scheduled payment is level.

Why does my Excel EMI differ from a lender’s amount?
Check whether the APR is entered as 8 or 8%, whether the term is in years or months, and whether the payment is rounded. Lender calculations may also account for fees, dates or methods this simple formula does not model.

Can this EMI calculator handle a changing interest rate?
No. It assumes one fixed annual rate for the full term. A changing rate or payment schedule requires recalculating periods under the applicable terms; the single-rate result will not describe those changes.

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