EMI Calculation in Excel: Formula and Example

An EMI calculation in Excel estimates the fixed monthly payment on a fully amortizing loan from its principal, annual interest rate, and term. Enter those three values to calculate the payment and total interest; the example below uses a $20,000 loan at 8% APR for five years, with all amounts understood to be in your currency.

Formula

The standard fixed-payment formula for a loan with monthly payments is:

EMI = P × r ÷ [1 − (1 + r)⁻ⁿ]

• EMI is the monthly installment, in your currency.
• P is the amount borrowed, in your currency.
• r is the monthly interest rate as a decimal: annual APR ÷ 12 ÷ 100.
• n is the number of monthly payments: loan term in years × 12.

The calculator rounds the EMI to two decimal places, then calculates total interest as the rounded monthly payment multiplied by the number of payments, minus the original principal. This makes the displayed total consistent with the displayed installment. It assumes a fixed rate and regular monthly payments.

For the default inputs, r = 8 ÷ 1200 = 0.0066667 per month and n = 5 × 12 = 60 payments. The formula uses the unrounded monthly rate in its calculation and rounds the resulting payment to two decimal places.

Worked Example

Using the defaults in the calculator—principal 20,000 in your currency, APR 8%, and term five years—the calculation is:

1. Convert APR to a monthly decimal rate: 8 ÷ 1200 = 0.0066667.
2. Convert the term to monthly payments: 5 × 12 = 60.
3. Substitute in the formula: 20,000 × 0.0066667 ÷ [1 − (1 + 0.0066667)⁻⁶⁰].
4. Round the monthly EMI to cents: 405.53 in your currency.
5. Calculate total paid using the rounded EMI: 405.53 × 60 = 24,331.80. Subtract the principal: 24,331.80 − 20,000 = 4,331.80 in your currency of total interest.

Assumptions

• The interest rate stays fixed for the full repayment term.
• Payments are equal, made monthly, and occur at the end of each payment period.
• Interest is calculated monthly using APR divided by 12; this is a standard estimate convention, not a statement about every loan’s exact calculation method.
• Fees, taxes, insurance, PMI, escrow, late charges, and payment-date adjustments are not included.

How to Calculate EMI in Excel

Excel’s PMT function can calculate a fixed periodic payment. If the annual rate is in cell B2, the number of years in B3, and the principal in B4, enter:

=-PMT(B2/12, B3*12, B4)

The minus sign makes the result display as a positive payment when the principal is entered as a positive number. For the example, use 8% in B2, 5 in B3, and 20000 in B4. Excel returns approximately 405.53. Format the result to two decimal places for currency-style display. Excel stores percentages as decimals, so 8% is stored as 0.08; do not divide a percentage-formatted cell by 100 again.

To calculate the total interest using the rounded EMI, use a separate formula such as =ROUND(-PMT(B2/12,B3*12,B4),2)*B3*12-B4. This follows the calculator’s approach of using the payment rounded to cents. Depending on the loan’s actual final-payment adjustment, its exact total may differ slightly.

Reading the Results and Limits

The monthly EMI is the scheduled principal-and-interest amount under the formula’s assumptions. It is not necessarily the full amount due each month: a lender may include other charges or use a different schedule. Total interest is the estimated interest over the stated term, not the total amount borrowed or the monthly installment.

In a conventional amortizing loan, each payment covers interest accrued for that period and reduces principal with the remainder. Early payments generally contain a larger interest portion because the outstanding balance is higher; later payments generally reduce principal more quickly. The EMI formula returns the same scheduled payment throughout, but the split between interest and principal changes.

This is not the same as simply dividing the amount borrowed by the number of months. That division leaves out interest. For example, 20,000 divided by 60 is 333.33, but at a positive rate the payment must also cover interest, so the calculated EMI is higher. A rate of zero also needs a separate calculation—principal divided by the number of payments—because the standard expression divides by the monthly rate.

Common Excel Mistakes

• Using the annual rate as a monthly rate: PMT needs the periodic rate, so divide an annual percentage by 12 when payments are monthly.
• Entering the term in years as the number of periods: five years of monthly payments means 60 periods, not five.
• Mixing payment timing: PMT’s default is end-of-period payments. Excel’s optional type argument can change timing; the ordinary EMI estimate uses end-of-month periods.
• Forgetting cash-flow signs: Excel treats money borrowed and money paid back as opposite cash-flow directions. A leading minus sign can present the payment as positive.
• Rounding too early: Keep the rate and formula calculations at full precision, then round the displayed EMI to cents. Repeatedly rounding intermediate values can shift the result.

Frequently Asked Questions

What is the Excel formula for EMI?
Use =-PMT(annual_rate/12, years*12, loan_amount) for a positive monthly payment, assuming monthly payments and a fixed annual rate. Format the result to two decimal places if you want a currency-style display.

Why does Excel show a negative EMI?
Excel uses cash-flow signs: the loan amount received and the payments made are opposite directions. Add a minus sign before PMT, or enter the principal with the opposite sign, to display the EMI as positive.

Should I enter APR as 8 or 8%?
If the cell contains the number 8, divide by 100 as well as by 12, using 8/1200. If it contains a percentage value such as 8%, Excel stores it as 0.08, so use B2/12.

Does EMI include fees and insurance?
Not in this calculation. It estimates the fixed principal-and-interest payment only. Check the loan’s own payment schedule or disclosure for charges or other amounts that may be due.

Can I use this formula for a variable-rate loan?
It can estimate a payment for a chosen rate and term, but it does not model future rate changes. If the rate changes, the payment or repayment schedule may change too.

This result is an estimate; the lender’s disclosure gives the exact figures.

Related

• NPS Interest Rate: How Returns Are Calculated
• New Car Zero-Interest Financing: How It Works
• National Saving Certificate Interest Rate in India

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