Monthly EMI Calculation Formula in Excel

To calculate a monthly EMI in Excel, use =-PMT(annual_rate/12, years*12, loan_amount); for example, =-PMT(8%/12, 5*12, 20000) returns a monthly payment of about $405.53. The calculator below applies the standard fixed-payment loan formula: enter the principal, annual APR, and term in years to estimate the payment and total interest.

Formula

The standard monthly EMI formula is:

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

In Excel, the equivalent calculation uses the PMT function. Because Excel normally displays a loan payment as a negative cash flow, put a minus sign before PMT to show the EMI as a positive amount:

=-PMT(apr/12, years*12, loan_amount)

• P is the amount borrowed, in your currency.
• apr is the annual interest rate entered as a percentage. For the formula, convert it to a decimal monthly rate: r = apr / 1200.
• r is the monthly interest rate as a decimal.
• n is the total number of monthly payments: years × 12.
• EMI is the level monthly payment, in your currency.

The calculator rounds the monthly payment to two decimal places, then calculates total interest as the rounded payment multiplied by the number of months, minus the principal. This matches the way the worked example is presented; an actual lender may calculate its schedule using more precision or different rounding conventions.

Worked Example

Using the calculator’s default values—$20,000 principal, 8% annual APR, and a five-year term—there are 60 monthly payments. Convert the APR to a monthly decimal rate: 0.08 / 12 = 0.0066667.

1. Calculate the number of payments: 5 × 12 = 60.
2. Substitute into the formula: 20,000 × 0.0066667 × (1.0066667)^60 / ((1.0066667)^60 − 1).
3. The monthly EMI is approximately $405.53 when rounded to cents.
4. Total of the rounded payments: $405.53 × 60 = $24,331.80.
5. Estimated total interest: $24,331.80 − $20,000 = $4,331.80.

Excel formula: =-PMT(8%/12,5*12,20000). To display cents, format the result as currency or use ROUND(-PMT(8%/12,5*12,20000),2).

Assumptions

• The interest rate is fixed throughout the loan term.
• Payments are equal and made once per month, at the end of each period, as assumed by Excel’s PMT function when its optional timing argument is omitted or set to 0.
• Interest compounds monthly, using the annual APR divided by 12.
• The estimate excludes fees, taxes, insurance, PMI, and escrow. These are not inputs to the calculator.

Using EMI in an Excel Sheet

A simple spreadsheet can keep the inputs separate from the formula. For example, put the principal in B1, annual APR in B2 as a percentage (such as 8%), and term in years in B3. Then enter =-PMT(B2/12,B3*12,B1) in B4. If the interest rate is entered as the number 8 rather than as the percentage 8%, use =-PMT(B2/100/12,B3*12,B1) instead.

For a term already entered in months, do not multiply by 12: use =-PMT(B2/12,B3,B1) if B3 contains months and B2 contains an Excel percentage. PMT’s optional fourth argument represents a future value, which is usually 0 for a fully repaid loan. Its optional fifth argument sets payment timing: 0 means the end of a period, and 1 means the beginning. Use the timing that matches the agreement being modelled.

What the EMI and Interest Results Mean

With a fixed rate and equal monthly payments, the EMI is designed to cover that month’s interest and reduce the remaining principal. Early in the schedule, the outstanding balance is larger, so more of a payment goes to interest; later, less interest accrues and more of the payment reduces principal. The EMI can stay level even though its interest and principal portions change over time.

Total interest is the estimated sum of interest over all scheduled payments under the calculator’s assumptions. It is not the monthly EMI, nor is it necessarily the total amount due under a particular contract. A lender’s repayment schedule may also reflect fees, payment dates, rate changes, extra payments, or rounding practices that are not represented here. Get the actual APR, charges, and payment details from the lender’s disclosure.

Common Excel Errors and Limits

• Entering a whole-number rate incorrectly: Excel stores 8% as 0.08. If you type 8, divide by 100 before dividing by 12.
• Mixing months and years: PMT needs the total number of payment periods. Multiply years by 12, but do not multiply a term already in months.
• Getting a negative result: PMT follows cash-flow signs. A negative loan amount and positive payment, or a positive loan amount with a minus sign before PMT, can make the displayed result positive.
• Comparing rounded figures: Excel may retain extra decimal places internally even when cells show cents. Use ROUND if you need the displayed payment itself rounded before calculating totals.
• Assuming every loan has level payments: This formula models a fixed rate and regular payments. Variable rates, irregular schedules, interest-only periods, fees added to principal, and extra payments need a different or expanded calculation.

PMT calculates payment arithmetic; it does not determine the applicable rate, fees, or contract terms. Treat any result as an estimate, and check the lender’s disclosure for the exact figures.

Frequently Asked Questions

What is the Excel formula for monthly EMI?
Use =-PMT(annual_rate/12, term_in_years*12, loan_amount) when the annual rate is stored as an Excel percentage and the term is in years.

Why does Excel PMT show a negative number?
Excel uses signs to represent money flowing in and out. A loan received and payments made have opposite signs; adding a minus sign before PMT displays the payment as a positive amount.

Should I enter the interest rate monthly or annually?
For an annual APR, enter it as a percentage and divide by 12 in the PMT formula. If the rate you have is already monthly, do not divide it by 12 again.

How do I calculate total interest in Excel?
For a rounded fixed monthly payment, use =ROUND(payment,2)*number_of_months-loan_amount. This estimates total interest under the same fixed-rate assumptions; a detailed schedule can differ because of rounding and contract terms.

Does EMI include processing fees or insurance?
Not in the formula shown. It calculates principal and interest only; fees, insurance, taxes, and other charges must be checked separately in the lender’s figures.

Related

• EMI Calculator: Estimate Your Monthly Loan Payment
• EMI Calculator: Estimate Your Monthly Installment
• Two-Wheeler EMI Calculator: Estimate Your Monthly Payment

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