EMI Calculation Excel Sheet: Formula and Example

An EMI calculation Excel sheet estimates a fixed monthly loan payment from the amount borrowed, annual interest rate, and repayment term. Enter those three figures to see the monthly EMI and total interest; this calculator uses the same standard reducing-balance formula commonly entered in a spreadsheet.

Formula

The fixed monthly payment, or equated monthly installment (EMI), is calculated using the standard amortizing-loan formula:

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

This is algebraically equivalent to EMI = round(P × r / (1 − (1 + r)^−n), 2). The spreadsheet-style inputs and variables are:

• P is the starting principal, or loan amount, in the currency you enter.
• APR is the annual interest rate entered as a percentage, not a decimal. For example, enter 8 for 8%.
• r is the monthly rate as a decimal: APR / 1200. At 8% APR, r = 8 / 1200.
• n is the number of monthly payments: loan term in years multiplied by 12.
• EMI is the level monthly payment, rounded to two decimal places in the entered currency.

Total interest here is calculated as the rounded EMI multiplied by the number of payments, minus the original principal. The formula assumes a fixed rate and equal monthly payments. It models a loan that is fully repaid by the end of the term, rather than a loan with a final balloon payment.

Worked Example

Using the calculator’s default inputs—principal 20,000 in your currency, APR 8%, and a five-year term—the number of payments is 5 × 12 = 60. The monthly rate is 8 / 1200 = 0.0066666667.

1. Substitute the values: EMI = 20,000 × 0.0066666667 / (1 − (1 + 0.0066666667)^−60).
2. Evaluate the denominator: 1 − (1.0066666667)^−60 ≈ 0.32854.
3. Divide the numerator by the denominator: 133.3333 / 0.32854 ≈ 406.64.
4. Rounded to two decimal places, the monthly EMI is 406.64 in your currency.

Total interest using the rounded payment is 406.64 × 60 − 20,000 = 4,398.40 in your currency. The combined scheduled payments are 24,398.40. These figures are estimates from the stated inputs and formula; a lender’s schedule may differ because of payment dates, rounding conventions, fees, or other loan terms.

Assumptions

• The interest rate is fixed for the entire term.
• Payments are made monthly at the end of each period, with the first payment one month after the loan begins.
• Interest compounds monthly at APR divided by 12.
• Fees, taxes, insurance, PMI, and escrow are not included. The calculator does not model them or add them to the EMI.

Using an EMI Formula in Excel

In a spreadsheet, put the loan amount, annual percentage rate, and term in years in separate cells. If the principal is in A1, APR as a percentage in B1, and years in C1, a direct formula is:

=ROUND(A1*(B1/1200)/(1-(1+B1/1200)^-(C1*12)),2)

The division by 1200 converts an APR entered as a percentage per year into a monthly decimal rate. The multiplication by 12 converts years into months. Both conversions matter: entering 8% as 8 in a formula that expects 0.08, or treating a five-year term as five monthly payments, produces a materially incorrect result.

Excel also has a payment function, often written as PMT(rate, nper, pv). For the same inputs, the equivalent expression is =ROUND(-PMT(B1/1200,C1*12,A1),2). The minus sign changes Excel’s cash-flow sign convention so the payment displays as a positive amount. The direct formula above makes the rate and payment calculation more visible.

How EMI Changes Over Time

A level EMI does not mean that the same amount of principal is repaid every month. Each payment is divided between interest and principal. Interest for a period is based on the outstanding balance; the rest of that month’s payment reduces principal. As the balance falls, the interest portion generally becomes smaller and the principal portion larger.

For example, on the default principal, the first month’s interest under the monthly-rate assumption is approximately 20,000 × 0.0066666667 = 133.33. From a 406.64 payment, about 273.31 therefore goes toward principal, subject to rounding. Later payments use the reduced balance. This is why the formula differs from simply dividing the loan amount by the number of months: that division ignores interest.

When the Estimate May Differ

Use this calculation to understand the payment implied by a specified principal, rate, and term, or to check a spreadsheet formula. It is not a loan offer, an approval decision, or a complete cost comparison. Get the actual rate, fees, payment dates, and repayment terms from the lender’s disclosure before relying on a figure.

• Variable rates: If the rate changes, the payment or repayment schedule may change; this fixed-rate calculation cannot forecast those changes.
• Irregular timing: A first payment that is not due one month after disbursement, or a schedule using non-monthly periods, can produce different interest.
• Fees and add-ons: Upfront fees, financed charges, insurance, and other costs are excluded unless they are included in the principal or modeled separately.
• Rounding: This calculator rounds the monthly payment to cents and uses that rounded payment for total interest. A lender may round each period differently, so the final balance can vary slightly.

A zero-interest loan is not accepted by this calculator because its formula divides by the monthly rate. For a truly interest-free loan with equal payments, the basic payment is principal divided by the number of months, before considering any other charges or terms.

Frequently Asked Questions

What does EMI mean?
EMI means equated monthly installment: a scheduled monthly payment intended to cover both interest and principal for an amortizing loan. The formula assumes the payment amount and interest rate stay fixed.

How do I calculate EMI in Excel?
Use =ROUND(P*(APR/1200)/(1-(1+APR/1200)^-(Years*12)),2), replacing the names with cell references. Enter APR as a percentage number, such as 8 for 8%, and the term in years.

Does EMI include total interest?
Each EMI includes that period’s interest and a principal repayment. The total interest over the term is separate: under this estimate, it is the rounded monthly payment multiplied by the number of payments, minus the original principal.

Why is my Excel EMI different from a lender’s figure?
Check the APR units, term conversion, principal, payment timing, and rounding first. A lender’s amount can also reflect fees, a different interest convention, changing rates, or other terms not represented by this calculation.

Can I use this for a changing interest rate?
Not as a complete projection. The result assumes one fixed APR throughout the term; a changing rate requires recalculating the payment or modeling each rate period under the contract’s rules.

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

Related

• Periodic Interest Rate: Examples and How to Calculate It
• Current G Fund Interest Rate: How It’s Set
• Current Interest Rate Index Calculator and Guide

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