To calculate a fixed-rate loan EMI in Excel, use =-PMT(annual_rate/12, years*12, loan_amount). Enter the annual interest rate as a decimal or percentage, the loan term in years, and the amount borrowed; the result is the level monthly payment, excluding fees and other costs.
Formula
The standard fixed-payment formula is EMI = P × r × (1 + r)n / ((1 + r)n − 1). An equivalent form is P × r / (1 − (1 + r)−n), which is used in the calculator above.
- P is the principal, or amount borrowed, in the same currency as the payment.
- r is the monthly interest rate as a decimal: annual APR in percent divided by 1,200.
- n is the total number of monthly payments: loan term in years multiplied by 12.
- EMI is the fixed monthly principal-and-interest payment, rounded to two decimal places.
APR is entered as a percentage, so 8 means 8%, not 0.08. Dividing by 1,200 both converts percent to a decimal and changes an annual rate to a monthly rate. This formula requires a positive interest rate; the calculator limits APR to 0.01% or more.
Worked Example
Using the calculator’s default inputs—loan amount 20,000, annual APR 8%, and a five-year term—the number of payments is n = 5 × 12 = 60. The monthly rate is r = 8 / 1,200 = 0.0066666667.
Substitute these into the formula: EMI = 20,000 × 0.0066666667 / (1 − (1 + 0.0066666667)−60). The unrounded payment is about 405.5277, so the monthly payment rounded to cents is 405.53.
Total of the rounded payments is 405.53 × 60 = 24,331.80. Subtract the original principal: 24,331.80 − 20,000 = 4,331.80 in total interest. This total uses the rounded monthly payment, so it can differ slightly from a calculation based on the unrounded formula result.
Excel PMT Function and Cell Formula
Excel’s PMT function calculates the same level payment. For example, if cell A2 contains the amount 20000, B2 contains the annual rate 8%, and C2 contains the term 5 years, enter:
=-PMT(B2/12,C2*12,A2)
The negative sign is there because Excel treats money received and money paid as opposite cash-flow directions. By default, PMT returns the payment with a negative sign when the loan amount is entered as a positive value. The leading minus displays the payment as a positive number.
Format the answer cell as currency with two decimal places. If the annual rate is stored as the number 8 rather than Excel’s percentage value 8%, use =-PMT(B2/1200,C2*12,A2). Do not divide by 1,200 if the cell already contains 0.08 as a decimal; in that case, divide by 12.
How to Use and Read the Result
- Enter the loan principal, excluding any amount that is not actually financed.
- Enter the annual rate as a percent and the term in years. Convert the term to months if your spreadsheet uses a months input.
- Calculate the payment with the formula or
PMT, then format it to two decimal places. - Multiply the displayed payment by the number of payments to estimate the total paid, and subtract principal to estimate total interest.
The EMI is a regular principal-and-interest installment under the formula’s assumptions. Early payments include a larger interest portion; as the balance declines, more of each fixed payment goes toward principal. The payment alone is not necessarily the full amount due: fees, insurance, taxes, or other charges may be billed separately or included in a lender’s payment figure.
Common Excel Errors and Formula Limits
- Wrong rate scale: A rate entered as 8 must be divided by 1,200; a cell holding 8% is already 0.08 and should be divided by 12.
- Wrong term unit: For a monthly rate, the number of periods must be months. Convert years using years × 12.
- Sign confusion: Excel’s cash-flow convention may show a negative payment. Use a leading minus for a positive display, or enter the loan amount with the opposite sign.
- Rounding differences: Round the displayed payment to cents for a practical installment estimate. A lender may adjust the final payment or use its own calculation and posting conventions.
- Zero interest: The displayed formula divides by the monthly rate and is not designed for 0%. At exactly 0%, the payment is principal divided by the number of monthly payments.
This formula models a fixed rate and equal monthly payments. It does not directly model variable rates, irregular payment dates, skipped installments, extra principal payments, origination charges, or changing loan balances. If a quoted APR includes costs that are paid separately rather than financed, the EMI formula does not automatically add them to the principal.
Assumptions
- The interest rate stays fixed throughout the term.
- Payments are equal monthly installments, paid at the end of each monthly period.
- Interest accrues monthly using the annual APR divided by 12.
- Fees, taxes, insurance, PMI, and escrow are not included unless separately added to the amount financed.
Frequently Asked Questions
What is the EMI formula in Excel?
Use =-PMT(annual_rate/12, term_in_years*12, loan_amount) when the annual rate is stored as an Excel percentage. If the rate is entered as a whole number such as 8, divide it by 1,200 instead.
Why does Excel show a negative EMI?
Excel represents cash received and cash paid with opposite signs. A positive loan amount represents money received, so the payment may display as negative. Put a minus sign before PMT to show the payment as positive.
How do I calculate total interest in Excel?
Multiply the monthly payment by the number of monthly payments, then subtract the loan principal. For a cent-rounded payment, use that displayed payment in the total so the estimate reflects the regular installment amount.
Does EMI include processing fees or insurance?
Not by itself. The formula calculates scheduled principal and interest based on the principal, rate, and term entered. Include a cost only if it is part of the amount financed, or calculate it separately.
Can I use the formula for a zero-interest loan?
The usual formula divides by the monthly interest rate, so it cannot be used directly at zero. Divide the principal by the number of monthly payments instead.
This result is an estimate; the lender’s disclosure gives the exact figures.
Related
Formula used
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
Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work
Method: the standard loan formula for this calculation, fixed rate and monthly compounding unless stated, rounded as each result states. Not financial advice; no lender, rate or product is recommended. See formulas and assumptions, the disclaimer or report an error.
More in this section
- Closing Costs on a House Loan: What to Expect$405.53
- Secured Loan for Home Refinancing: Payment Calculator$405.53
- Closing Cost House Calculator: Estimate Buyer Costs$8,500.00
- Closing Cost House Estimate Calculator
- Loan Repayment Extension Request Letter: What to Include$405.53
- Refinance Calculator With PMI and Loan Term