EMI Calculation Formula in Excel Sheet

To calculate an EMI (equated monthly instalment) in Excel, use =-PMT(annual_rate/12, years*12, loan_amount). Enter the annual interest rate as a percentage, the loan term in years, and the principal in the same currency; Excel returns the fixed monthly payment, excluding fees and other charges.

Formula

The standard fixed-payment formula is:

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

It can also be written as EMI = P × r / (1 − (1 + r)^−n). The calculator rounds the monthly payment to two decimal places and then calculates total interest using that rounded payment.

• P is the amount borrowed, entered in your currency.
• r is the monthly interest rate as a decimal: annual APR (%) divided by 1,200.
• n is the total number of monthly payments: loan term in years multiplied by 12.
• EMI is the level payment due each month, in your currency.

For example, an 8% annual rate becomes a monthly rate of 8 / 1200, or approximately 0.006667. The division by 12 converts an annual nominal rate to a monthly rate; dividing by 100 converts the percentage to a decimal.

Worked Example

Using the calculator’s default values—principal 20,000, APR 8%, and term 5 years—the number of payments is n = 5 × 12 = 60, and the monthly rate is r = 8 / 1200 = 0.0066666667.

Substitute these values into the formula:

EMI = 20,000 × 0.0066666667 / (1 − (1 + 0.0066666667)^−60)

The unrounded result is approximately 405.5277, so the monthly payment rounded to two decimal places is 405.53 in your currency. Total of the rounded payments is 405.53 × 60 = 24,331.80. Estimated total interest is 24,331.80 − 20,000 = 4,331.80.

How to Set Up the Formula in Excel

Excel’s PMT function calculates a level payment for a loan with a constant interest rate and regular payment periods. If the annual rate is in cell B2, the term in years is in B3, and the principal is in B4, enter:

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

Format B2 as a percentage, or enter a decimal such as 8%. The negative sign before PMT displays the payment as a positive amount. Excel treats money paid out and money received as opposite cash-flow directions, so without the sign change the payment may appear negative.

To show a payment rounded to two decimal places, use =ROUND(-PMT(B2/12,B3*12,B4),2). If B2 contains a whole-number percentage such as 8 rather than Excel’s percentage value 8%, use =-PMT(B2/100/12,B3*12,B4). Do not apply both conventions at once.

An alternative is to calculate the EMI directly from the formula. With the same cells and B2 entered as 8%, use:

=ROUND(B4*(B2/12)/(1-(1+B2/12)^(-B3*12)),2)

For a rate entered as the number 8, replace each monthly-rate expression B2/12 with B2/100/12. The direct formula and PMT should agree when their rate, period, and payment-timing assumptions match.

Assumptions

• The interest rate stays fixed for the full term.
• Payments are equal, monthly, and made at the end of each period, as assumed by Excel’s PMT function unless its optional timing argument is changed.
• The APR is converted to a monthly rate by dividing by 12; the calculation uses monthly periods.
• The estimate excludes fees, taxes, insurance, PMI, and escrow because the calculator has no inputs for them.

What the EMI Result Includes

For a standard fully amortizing loan, each payment covers the interest accrued for that month and reduces the outstanding principal. Early in the schedule, the interest portion is generally larger because the balance is larger. As principal is repaid, the interest portion tends to decline, while the principal portion tends to rise. The total payment remains level under the stated assumptions.

The total-interest figure here is calculated as the rounded monthly payment multiplied by the number of payments, minus the original principal. It is an estimate based on the same fixed-rate model—not a complete payoff quote. A lender’s schedule can differ if payment dates, daily interest calculations, rate changes, fees, or rounding rules differ.

Common Excel Mistakes and Limits

• Mixing years and months: If the term is entered in years, multiply it by 12 for the number of monthly periods. If you already entered months, do not multiply by 12 again.
• Entering the rate in the wrong format: Excel’s percentage value for 8% is stored as 0.08. A cell containing the number 8 needs an additional division by 100.
• Using an annual rate as a monthly rate: The PMT rate argument must match the payment period. For monthly payments, convert the annual rate to a monthly rate.
• Forgetting the cash-flow sign: Excel may show a negative payment because the loan amount and repayments have opposite signs. Use a leading minus sign or enter the principal with the opposite sign.
• Rounding too early: Round the displayed monthly payment to cents, but avoid rounding the monthly rate or intermediate formula values. Rounding at every step can cause small differences in totals.

The formula is not suitable without adjustment for variable-rate loans, irregular payment dates, interest-only periods, balloon payments, or fees included in the financed balance. A zero-interest loan is a special case: its payment is simply principal divided by the number of payments. The calculator requires a positive APR, so it does not model that case.

Frequently Asked Questions

What is the Excel formula for EMI?
Use =-PMT(annual_rate/12, years*12, loan_amount) for monthly payments, with the annual rate entered as a percentage. For a payment rounded to cents, wrap the formula in ROUND(...,2).

Why does Excel show a negative EMI?
PMT uses cash-flow signs: receiving the loan and making repayments have opposite signs. Put a minus sign before PMT to display the payment as a positive number.

Should I enter the annual interest rate as 8 or 8%?
Either can work if the formula matches. Entering 8% means Excel stores 0.08, so divide by 12 for a monthly rate. If entering the number 8, divide by 100 and then by 12.

Does EMI include fees and insurance?
No. The standard formula covers principal and interest under a fixed-rate assumption. Fees, insurance, taxes, PMI, and escrow are not included unless separately added to the calculation.

Why might my Excel result differ from a lender’s payment?
Differences can come from rounding, payment dates, the way interest is accrued, rate changes, fees, or a different loan balance. Use the lender’s disclosed payment schedule for the exact figures.

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

Related

• Current PF Interest Rate: EPF Rate and How It Works
• Periodic Interest Rate: Examples and How to Calculate It
• Current G Fund Interest Rate: How It’s Set

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