EMI Calculation Excel Formula and Worked Example

The standard Excel formula for a fixed-rate monthly EMI is =-PMT(annual_rate/12, years*12, loan_amount). Enter the annual interest rate as a decimal or percentage, the number of monthly payments, and the loan principal; Excel returns the monthly payment, in the same currency as the principal.

Formula

For a fixed-rate loan repaid through equal monthly payments, the payment is calculated as:

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

This is equivalent to P × r / (1 − (1 + r)^−n). The calculator rounds the payment to two decimal places before calculating total interest, as the block specifies.

• P is the principal, or amount borrowed, in your currency.
• r is the monthly interest rate as a decimal. With an annual rate expressed as a percent, r = APR / 1200.
• n is the total number of monthly payments, calculated as years × 12.
• EMI is the fixed monthly payment in your currency.

Excel’s PMT function uses the same level-payment mathematics. For example, with principal in A1, annual rate as a percentage in A2, and years in A3, use =-PMT(A2/12%,A3*12,A1) if A2 contains a value such as 8, or =-PMT(A2/12,A3*12,A1) if A2 contains the percentage value 8% (stored as 0.08). The minus sign makes the payment display as a positive amount because Excel treats the borrowed amount and payment as cash flows in opposite directions.

Worked Example

Using the calculator’s defaults: principal 20,000 in your currency, annual rate 8%, and term 5 years.

1. Convert the annual percentage rate to a monthly decimal rate: r = 8 / 1200 = 0.0066666667.
2. Convert the term to monthly payments: n = 5 × 12 = 60.
3. Substitute into the formula: 20,000 × 0.0066666667 / (1 − (1 + 0.0066666667)^−60).
4. The unrounded monthly payment is approximately 405.5277; rounded to cents, the EMI is 405.53 in your currency per month.
5. Total of the rounded payments: 405.53 × 60 = 24,331.80. Estimated total interest: 24,331.80 − 20,000 = 4,331.80 in your currency.

Assumptions

• The rate remains fixed for the full term.
• Payments are made monthly at the end of each period, as in Excel’s default PMT timing.
• Interest compounds monthly at APR / 12.
• Fees, taxes, insurance, PMI, and escrow are not included. The calculator has no inputs for these costs.

What EMI Means and How the Calculation Works

EMI means equated monthly installment: a scheduled payment that stays level when the interest rate and repayment schedule stay unchanged. Each payment covers interest charged for that period and repays some principal. Early in a standard amortizing loan, the outstanding balance is larger, so more of the payment goes toward interest; as the balance falls, the interest portion usually falls and the principal portion rises.

The formula finds the payment amount that brings the balance to zero after the specified number of payments, assuming the stated rate and timing. It is not simply the principal divided by the number of months, because interest accrues while the balance is outstanding. The total-interest output is the sum of the rounded monthly payments over the term minus the original principal. It is an estimate under these assumptions, not a complete loan cost figure.

How to Use the Excel Formula

In Excel, the direct formula is usually the quickest way to calculate EMI. Keep the rate and term in matching time units: this example converts an annual rate to a monthly rate and years to months.

• For a rate typed as 8%, use =-PMT(8%/12,5*12,20000).
• For a rate typed as 8 to mean 8 percent, use =-PMT(8%/1200,5*12,20000).
• If the term is already entered as months, use that cell directly for the second argument rather than multiplying by 12.
• To show payments to cents, format the result as currency with two decimal places. Formatting changes the display; to make a separate total match the rounded payment, use ROUND in the payment calculation.

Excel’s PMT function accepts optional arguments for payment timing and a remaining future value. For a typical fully amortizing calculation, leave future value at zero and omit the timing argument, which makes payments due at the end of each period. A payment at the beginning of each period produces a different result.

Common Excel Errors and Limitations

• Percent entered at the wrong scale: Excel stores 8% as 0.08. Dividing that by 12 is correct; dividing it by 1200 again would make the rate far too small. If the cell contains the number 8, divide by 1200.
• Annual rate used as a monthly rate: The payment periods are months, so a nominal annual rate must be converted to a monthly rate for this formula.
• Negative payment display: This is Excel’s cash-flow sign convention, not an error. Put a minus sign before PMT to display a positive installment.
• Rounding differences: Excel may retain more decimal places than are shown. A displayed payment rounded to cents can produce a slightly different total than a calculation using the unrounded value. The calculator above uses the rounded payment for total interest.
• Zero interest: The displayed formula divides by the monthly rate and is not defined at zero. The calculator requires a positive rate; for a truly interest-free loan, the payment is principal divided by the number of months.

The calculation does not model variable rates, missed or irregular payments, extra principal payments, fees added to the balance, or lender-specific rounding and payment rules. If a loan has a changing rate or a different payment schedule, a single fixed-rate EMI formula cannot reproduce the full repayment path.

Frequently Asked Questions

What is the EMI formula in Excel?
Use =-PMT(annual_rate/12, years*12, principal) when the annual rate is stored as a percentage such as 8%. If the rate cell contains 8 rather than 8%, divide by 1200 instead of 12.

Why does Excel show a negative EMI?
Excel treats the loan amount received and payments made as opposite cash flows. Add a minus sign before PMT to display the payment as a positive number.

Can I calculate EMI without the PMT function?
Yes. Use =P*r*(1+r)^n/((1+r)^n-1), where r is the monthly decimal rate and n is the number of monthly payments. This is the same fixed-payment formula expressed directly.

Does the EMI include fees and insurance?
No. The standard formula uses principal, interest rate, and term. Add any separate charges only if you are calculating them independently or have reliable loan-specific figures.

Why might my Excel result differ from a lender’s payment?
Possible causes include a different rate convention, payment date or timing, fees included in the financed amount, rounding practices, or a rate that changes during the term. Compare the formula’s assumptions with the lender’s disclosed figures.

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

Related

• Simple Interest: Principal × Rate × Time Calculator
• Current PF Interest Rate: EPF Rate and How It Works
• Periodic Interest Rate: Examples and How to Calculate It

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