EMI Calculation Formula in Excel PMT

Excel’s PMT function calculates the fixed periodic payment for a loan, so it can calculate an EMI when payments are monthly and the interest rate and term are entered consistently. Use =-PMT(annual_rate/12, years*12, loan_amount) to display the monthly payment as a positive number; this calculator shows the same standard payment formula and total interest for a fixed-rate loan.

Formula

The fixed monthly payment is PMT = P × r / (1 − (1 + r)−n). Here, P is the principal, or amount borrowed, in your currency; r is the monthly interest rate as a decimal; and n is the number of monthly payments. For an APR entered as a percentage per year, the calculator uses r = APR / 1200 and n = years × 12. The payment is rounded to two decimal places.

Excel’s PMT function uses the form PMT(rate, nper, pv, [fv], [type]): rate is the rate per payment period, nper is the number of periods, and pv is the present value (the loan principal). For an ordinary loan paid monthly at month-end, enter =-PMT(APR/12, years*12, principal). The minus sign converts Excel’s usual negative cash-flow result into a positive payment display.

The calculator uses “-” for money units because the amount is in your currency. Convert the APR to a monthly decimal rate by dividing a percentage APR by 100 and then by 12; dividing by 1200 does both at once. The total-interest result is the sum of the rounded monthly payments over the full term, minus the original principal.

Worked Example

Using the calculator’s default inputs—loan amount 20,000 in your currency, 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.

1. Substitute into the payment formula: PMT = 20000 × 0.0066666667 / (1 − (1 + 0.0066666667)−60).
2. Calculate the unrounded payment: approximately 405.5279 in your currency.
3. Round to cents: the monthly payment is 405.53 in your currency.
4. Calculate total payments using the rounded payment: 405.53 × 60 = 24,331.80.
5. Subtract principal: 24,331.80 − 20,000 = 4,331.80 in total interest.

These figures assume all 60 payments are the same. A lender’s actual final payment can differ slightly if its schedule uses more precise daily interest, a different payment date convention, or a rounding adjustment.

Assumptions

• The interest rate stays fixed for the entire term.
• Payments are monthly and made at the end of each period, the usual ordinary-annuity setting in Excel’s PMT function.
• Interest is modelled monthly at APR divided by 12.
• Fees, taxes, insurance, PMI, escrow, and other charges are not included because they are not calculator inputs.

When to Use Excel PMT for EMI

Use PMT when a loan has a fixed periodic payment, a known periodic interest rate, and a known number of payments. EMI means an equated monthly instalment: principal and interest are combined into one regular payment. The payment remains level in this model, but the split between interest and principal changes over time. Early payments generally contain more interest than later payments because interest is calculated on the remaining balance.

For a spreadsheet, put the principal, APR, and term in separate cells so that changing an input updates the result. For example, if A1 contains the principal, A2 the annual APR entered as 8%, and A3 the term in years, use =-PMT(A2/12,A3*12,A1). If the APR cell contains the number 8 rather than the percentage value 8%, use =-PMT(A2/1200,A3*12,A1). Do not divide a percentage-formatted cell by 100 a second time.

How to Read the Payment and Interest

The monthly payment is not the total cost of borrowing. Multiply the rounded payment by the number of payments to estimate total scheduled payments, then subtract the principal to estimate interest under these assumptions. A longer term usually spreads repayment across more months; the payment formula must be recalculated for the new term, and the total interest can change as a result. The calculator does not show an amortization schedule, but a schedule can be built by applying each payment first to interest for that period and then to the balance.

Excel’s optional fv and type arguments matter in other situations. fv is a desired ending balance, typically zero for a fully repaid loan. type is 0 for payments at the end of a period and 1 for payments at the beginning. For a standard monthly loan, leave both optional arguments out or use zero. Choosing type 1 changes the calculation because each payment is made earlier.

Common Excel PMT Mistakes and Limits

• Using an annual rate as a monthly rate: PMT needs the rate for one payment period. Divide an annual percentage APR by 12 if Excel stores it as a percentage, or by 1200 if it is stored as the whole number 8 for 8%.
• Using years as the number of periods: For monthly payments, multiply years by 12. Entering 5 instead of 60 models five payments, not five years of monthly payments.
• Misreading a negative result: Excel treats money paid out and money received as opposite cash-flow signs. Negate PMT for a positive displayed payment, or enter the principal with the opposite sign.
• Rounding too early or inconsistently: Excel may display two decimals while retaining additional precision. If a schedule rounds every payment to cents, its total can differ slightly from a calculation that uses the unrounded PMT throughout. This calculator explicitly uses the rounded payment for total interest.

PMT is a mathematical estimate, not a lender quote. It does not model a variable rate, irregular payment dates, extra payments, missed payments, fees, or a loan with a changing balance. For a zero-interest loan, the payment is simply principal divided by the number of payments; this calculator’s APR input starts at 0.01% because the displayed formula divides by the monthly rate. The result is an estimate, and the lender’s disclosure gives the exact figures.

Frequently Asked Questions

What is the Excel formula for calculating EMI?
For a monthly loan with APR in a percentage-formatted cell, use =-PMT(APR_cell/12, years_cell*12, principal_cell). If APR is entered as a whole number such as 8, use =-PMT(APR_cell/1200, years_cell*12, principal_cell).

Why does Excel PMT return a negative number?
Excel applies cash-flow signs: the loan received and the repayments made have opposite signs. Put a minus sign before PMT to show the payment as positive, or enter the principal with the opposite sign.

Does PMT include the total interest?
No. PMT returns the regular payment. To estimate total interest for a fully repaid fixed-rate loan, multiply the payment by the number of payments and subtract the principal, using consistent rounding.

Should I use APR divided by 12 or by 1200?
If the APR cell is stored as 8%, Excel reads it as 0.08, so divide by 12. If the cell contains the number 8 to represent 8%, divide by 1200.

Can PMT calculate a loan with a changing interest rate?
One PMT calculation assumes a constant rate and level payments. A changing-rate loan needs separate calculations for the applicable rate periods and may also depend on the lender’s balance and payment rules.

Related

• National Interest Rate: Meaning and How It Works
• Simple Interest: Principal × Rate × Time Calculator
• Current PF Interest Rate: EPF Rate and How It Works

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