Term Loan EMI Calculator in Excel: Formula and Example

A term loan EMI calculator in Excel estimates the fixed monthly payment on a loan from its principal, annual interest rate and repayment term. Enter those figures to calculate the monthly installment and total interest; the result is an estimate based on equal monthly payments and a fixed rate.

Formula

The monthly EMI (equated monthly installment) for a fixed-rate loan with equal monthly payments is:

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

Here, P is the amount borrowed, in your currency; r is the monthly interest rate as a decimal, calculated as annual APR ÷ 12 ÷ 100; and n is the number of monthly payments, calculated as the term in years × 12. The calculator rounds the payment to two decimal places. Total interest is calculated as the rounded EMI × number of payments − principal.

The formula assumes a positive interest rate. The input minimum is 0.01% APR, so it does not model a zero-interest loan. The term input is in years, although actual loan schedules may use a particular number of months or a different first-payment date. Check the lender’s schedule if those details matter.

Worked Example

Using the calculator’s defaults—principal of 20,000 in your currency, APR of 8%, and a five-year term—first find the monthly rate and number of payments:

• Monthly rate: 8 ÷ 1,200 = 0.0066666667.
• Number of payments: 5 × 12 = 60.
• Substitute into the formula: 20,000 × 0.0066666667 ÷ [1 − (1 + 0.0066666667)⁻⁶⁰].
• Rounded monthly EMI: 405.53 in your currency.
• Total of rounded payments: 405.53 × 60 = 24,331.80.
• Estimated total interest: 24,331.80 − 20,000 = 4,331.80.

These are mathematical estimates, not a lender’s quote. Small differences can arise if a lender calculates interest daily, uses a different payment schedule, or rounds each period differently.

Assumptions

• The interest rate stays fixed throughout the modeled term.
• Payments are equal, made monthly, and treated as occurring at the end of each monthly period.
• Interest compounds monthly at the stated APR divided by 12.
• Fees, taxes, insurance, and other charges are not included.

Building the Calculation in Excel

Excel’s PMT function can calculate a periodic payment. If the principal is in cell A1, annual APR in A2, and term in years in A3, use:

=-PMT(A2/12/100,A3*12,A1)

The minus sign makes the displayed payment positive because Excel’s cash-flow functions treat the amount borrowed and repayments as opposite-direction cash flows. To round the installment to cents, use =ROUND(-PMT(A2/12/100,A3*12,A1),2). With the example inputs, the result is 405.53.

To calculate total interest using the rounded payment, multiply that payment by the number of months and subtract the principal. For example, if the rounded EMI is in B1, use =B1*A3*12-A1. Keep input cells clearly labelled and format money cells as currency. A formula is only as reliable as the values entered: use the annual rate as a percentage, not as a decimal, when applying the conversion shown above.

How to Use and Read the Results

Enter the amount actually borrowed, the APR supplied for the calculation, and the repayment term in years. The EMI is the estimated fixed monthly principal-and-interest payment under the stated assumptions. Total interest is the sum of those rounded payments minus the original principal; it is not the total cost if separate charges apply.

For a real estimate, obtain the rate, amount, term, and payment schedule from the lender’s written information. If the lender gives a term in months, convert it to years only if that produces an exact and appropriate input; for an unusual schedule, use a spreadsheet with the exact number of periods instead. A variable rate cannot be represented accurately by one fixed-rate EMI over the whole term.

Common Excel Errors and Limits

• Entering APR incorrectly: For an APR entered as 8, the monthly decimal rate is 8/100/12, not 8/12.
• Mixing years and months: PMT’s number-of-periods argument must be 60 for a five-year monthly loan, not 5.
• Sign confusion: Excel may show a negative payment depending on the signs of the cash flows. Negate PMT for a positive display.
• Rounding at different stages: The example rounds the EMI to cents first, then totals those rounded installments. A lender’s schedule may round interest and principal separately each month, causing a small difference.
• Leaving out charges or rate changes: The formula does not include fees, irregular payments, changing rates, or payment holidays.

The standard formula models a fully amortizing loan: each payment covers that period’s interest and reduces principal, with the balance scheduled to reach zero after the final payment. It is not a payoff statement, and extra payments or missed installments change the balance and interest calculation.

Frequently Asked Questions

What is the EMI formula in Excel?
Use =-PMT(APR/12/100,years*12,principal) when APR is entered as a whole percentage, such as 8 for 8%. Use ROUND around the formula if you want the displayed payment rounded to two decimal places.

Does EMI include the whole cost of a term loan?
Not necessarily. This calculation covers principal and modeled interest only. Charges outside the loan payment are omitted unless included in the amount borrowed or separately represented.

Why does my Excel result differ from the lender’s EMI?
Possible causes include a different rate convention, payment date, compounding method, rounding approach, loan amount, or term. Compare the inputs and ask the lender how its repayment schedule is calculated.

Can I use this for a variable-rate loan?
Only as a fixed-rate snapshot using the rate entered. If the rate changes, the payment or the amount of principal repaid may change; this calculation does not project those future changes.

Can the EMI be zero?
No, not with the calculator’s positive principal and APR limits. A zero-interest loan requires a different calculation: principal divided by the number of monthly payments.

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

Related

• Loan Term vs Amortization Term: Key Differences
• Loan Termination Program: Meaning and Payoff Calculator
• Loan Term Sheet: Meaning, Terms, and How to Review

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