calculate-loan

01 Loan payments

Loan EMI Formula in Excel: Calculate Monthly Payments

Calculator No. 01-15062 · 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.

To calculate a fixed-rate loan EMI in Excel, use =-PMT(annual_rate/12, years*12, loan_amount). Enter the annual interest rate as a decimal or percentage, the loan term in years, and the amount borrowed; the result is the level monthly payment, excluding fees and other costs.

Formula

The standard fixed-payment formula is EMI = P × r × (1 + r)n / ((1 + r)n − 1). An equivalent form is P × r / (1 − (1 + r)−n), which is used in the calculator above.

  • P is the principal, or amount borrowed, in the same currency as the payment.
  • r is the monthly interest rate as a decimal: annual APR in percent divided by 1,200.
  • n is the total number of monthly payments: loan term in years multiplied by 12.
  • EMI is the fixed monthly principal-and-interest payment, rounded to two decimal places.

APR is entered as a percentage, so 8 means 8%, not 0.08. Dividing by 1,200 both converts percent to a decimal and changes an annual rate to a monthly rate. This formula requires a positive interest rate; the calculator limits APR to 0.01% or more.

Worked Example

Using the calculator’s default inputs—loan amount 20,000, annual APR 8%, and a five-year term—the number of payments is n = 5 × 12 = 60. The monthly rate is r = 8 / 1,200 = 0.0066666667.

Substitute these into the formula: EMI = 20,000 × 0.0066666667 / (1 − (1 + 0.0066666667)−60). The unrounded payment is about 405.5277, so the monthly payment rounded to cents is 405.53.

Total of the rounded payments is 405.53 × 60 = 24,331.80. Subtract the original principal: 24,331.80 − 20,000 = 4,331.80 in total interest. This total uses the rounded monthly payment, so it can differ slightly from a calculation based on the unrounded formula result.

Excel PMT Function and Cell Formula

Excel’s PMT function calculates the same level payment. For example, if cell A2 contains the amount 20000, B2 contains the annual rate 8%, and C2 contains the term 5 years, enter:

=-PMT(B2/12,C2*12,A2)

The negative sign is there because Excel treats money received and money paid as opposite cash-flow directions. By default, PMT returns the payment with a negative sign when the loan amount is entered as a positive value. The leading minus displays the payment as a positive number.

Format the answer cell as currency with two decimal places. If the annual rate is stored as the number 8 rather than Excel’s percentage value 8%, use =-PMT(B2/1200,C2*12,A2). Do not divide by 1,200 if the cell already contains 0.08 as a decimal; in that case, divide by 12.

How to Use and Read the Result

  1. Enter the loan principal, excluding any amount that is not actually financed.
  2. Enter the annual rate as a percent and the term in years. Convert the term to months if your spreadsheet uses a months input.
  3. Calculate the payment with the formula or PMT, then format it to two decimal places.
  4. Multiply the displayed payment by the number of payments to estimate the total paid, and subtract principal to estimate total interest.

The EMI is a regular principal-and-interest installment under the formula’s assumptions. Early payments include a larger interest portion; as the balance declines, more of each fixed payment goes toward principal. The payment alone is not necessarily the full amount due: fees, insurance, taxes, or other charges may be billed separately or included in a lender’s payment figure.

Common Excel Errors and Formula Limits

  • Wrong rate scale: A rate entered as 8 must be divided by 1,200; a cell holding 8% is already 0.08 and should be divided by 12.
  • Wrong term unit: For a monthly rate, the number of periods must be months. Convert years using years × 12.
  • Sign confusion: Excel’s cash-flow convention may show a negative payment. Use a leading minus for a positive display, or enter the loan amount with the opposite sign.
  • Rounding differences: Round the displayed payment to cents for a practical installment estimate. A lender may adjust the final payment or use its own calculation and posting conventions.
  • Zero interest: The displayed formula divides by the monthly rate and is not designed for 0%. At exactly 0%, the payment is principal divided by the number of monthly payments.

This formula models a fixed rate and equal monthly payments. It does not directly model variable rates, irregular payment dates, skipped installments, extra principal payments, origination charges, or changing loan balances. If a quoted APR includes costs that are paid separately rather than financed, the EMI formula does not automatically add them to the principal.

Assumptions

  • The interest rate stays fixed throughout the term.
  • Payments are equal monthly installments, paid at the end of each monthly period.
  • Interest accrues monthly using the annual APR divided by 12.
  • Fees, taxes, insurance, PMI, and escrow are not included unless separately added to the amount financed.

Frequently Asked Questions

What is the EMI formula in Excel?
Use =-PMT(annual_rate/12, term_in_years*12, loan_amount) when the annual rate is stored as an Excel percentage. If the rate is entered as a whole number such as 8, divide it by 1,200 instead.

Why does Excel show a negative EMI?
Excel represents cash received and cash paid with opposite signs. A positive loan amount represents money received, so the payment may display as negative. Put a minus sign before PMT to show the payment as positive.

How do I calculate total interest in Excel?
Multiply the monthly payment by the number of monthly payments, then subtract the loan principal. For a cent-rounded payment, use that displayed payment in the total so the estimate reflects the regular installment amount.

Does EMI include processing fees or insurance?
Not by itself. The formula calculates scheduled principal and interest based on the principal, rate, and term entered. Include a cost only if it is part of the amount financed, or calculate it separately.

Can I use the formula for a zero-interest loan?
The usual formula divides by the monthly interest rate, so it cannot be used directly at zero. Divide the principal by the number of monthly payments instead.

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