Excel does not have an EMI function. For a standard loan with a fixed interest rate and equal payments, use Excel’s PMT function. The key is to express the interest rate and number of payments in the same units: a monthly rate with the total number of monthly payments, for example.
The formulas below build an EMI calculator, show total interest, and create a payment-by-payment amortization schedule.
Set up the EMI calculator inputs
Start with a small input section. In this example, the loan amount is in B2, the annual interest rate is in B3, the term is in B4, and the number of payments per year is in B5.
| Cell | Input | Example |
|---|---|---|
B2 |
Loan amount | 250000 |
B3 |
Annual interest rate | 8% |
B4 |
Loan term in years | 20 |
B5 |
Payments per year | 12 |
Format B3 as a percentage and B2 as currency. Enter 8% or 0.08 for an 8% rate. Entering 8 in a percentage-formatted cell represents an 800% rate, not 8%.
Crashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minutePC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11#1 Best Overall
- Profitability calculations; cash flow function Calculates NPV and IRR for uneven cash flows
- Time-value-of-money and Amortization keys solve problems including: pension calculations, loans, mortgages, etc.
- Ideal calculator for students, managers and statisticians
- Built-in functionality : List-based one- and two-variable statistics with four regression options: linear, logarithmic, exponential and power
- The BA II Plus calculator is approved for use on the following professional exams: Chartered Financial Analyst exam. GARP Financial Risk Manager (FRM) exam. Certified Management Accountants exam
Calculate EMI with Excel’s PMT function
Put this formula in B6:
=-PMT(B3/B5,B4*B5,B2,0,0)This returns the periodic payment as a positive amount. The shorter monthly version is:
=-PMT(B3/12,B4*12,B2)The PMT syntax is:
PMT(rate, nper, pv, [fv], [type])| Argument | Meaning |
|---|---|
rate |
Interest rate for one payment period |
nper |
Total number of payment periods |
pv |
Present value, usually the loan principal |
fv |
Balance remaining after the final payment; normally 0 |
type |
0 or omitted for payments at the end of a period; 1 for payments at the beginning |
Excel uses cash-flow signs. If the principal is positive, PMT normally returns a negative payment because the payment is money leaving you. The minus sign before PMT displays EMI as a positive figure.
Why the rate must be divided by 12
For monthly payments, the annual rate and payment count need to be converted to monthly units:
- Monthly rate:
B3/12 - Total payments:
B4*12
For another frequency, use the payments-per-year input. For example, with quarterly payments, use B5=4, producing a rate of B3/4 and a payment count of B4*4.
This division assumes that the quoted annual rate is a nominal annual rate compounded monthly or at the relevant payment frequency. An effective annual rate requires a different conversion.
Rank #2
- PROFESSIONAL FINANCIAL CALCULATOR : Built-in TVM, IRR, NPV. Engineered for business analysts, real estate investors, accountants, and finance students.
- ADVANCED CASH FLOW & AMORTIZATION : Execute time value of money, break-even analysis, depreciation schedules, and bond pricing. Trusted for professional exam prep", MBA coursework, and banking certifications.
- CATIGA CF-300 : Flip-open hard case with a snap-close design for a secure fit. Compact and portable: designed for daily professional use in office, classroom, or on-site.
- ALL-IN-ONE FOR PROFESSIONALS : From NPV/IRR for real estate analysis to statistical calculations for business analysts. Handles probability, linear regression, and complex financial formulas.
- MORTGAGE, LOAN & INVESTMENT CALCULATOR : Covers bond pricing, loan amortization, investment analysis, and exam-level computations. Your go-to accounting calculator, business calculator, and real estate calculator in one device.
Effective annual rate versus nominal annual rate
If B3 is an effective annual rate, calculate the equivalent monthly rate as:
=(1+B3)^(1/12)-1You can place that monthly rate in another cell, such as B7, and use:
=-PMT(B7,B4*12,B2)Or use the conversion directly:
=-PMT((1+B3)^(1/12)-1,B4*12,B2)Do not divide an effective annual rate by 12 and assume the result is equivalent. Excel’s EFFECT function converts a nominal annual rate to an effective annual rate, while NOMINAL performs the reverse conversion.
Do these 3 things before closing this tab:
1Clear out junk files and repair common Windows errors2Fix the driver behind crashes, sound loss and screen glitches3Repair Windows errors before they cause bigger problemsCalculate total payments and total interest
If the positive EMI is in B6, calculate the total amount paid over the loan with:
=B6*B4*B5Calculate total interest with:
=B6*B4*B5-B2For a monthly loan, these can also be written as:
=B6*B4*12=B6*B4*12-B2These totals cover principal and scheduled interest only. They do not include loan fees, taxes, insurance, reserve payments, balloon amounts, or the effect of future prepayments.
Rank #3
- HP 10BII+ FOR STUDENTS & PROFESSIONALS – This HP calculator is built for business, finance, accounting, and statistics courses. Perfect for learners and professionals who need to solve common financial problems quickly without memorizing formulas or relying on spreadsheets.
- 100+ FUNCTIONS FOR REAL WORLD MATH – Quickly solve time value of money, interest rates, loan payments, NPV, IRR, cash flows, and more. The 10bII+ also includes probability distributions for statistics courses—a feature not often found in financial calculators.
- ALGORITHMIC INPUT WITH DEDICATED KEYS – This high-school/college calculator uses algebraic and chain logic with minimal keystrokes. Layout appears the same as standard calculators for easy learning. Dedicated keys give quick access to commonly used financial and statistical functions
- APPROVED FOR MAJOR EXAMS – The HP 10bII+ algebra calculator is permitted for use on SAT, PSAT/NMSQT, and AP tests. An ideal statistics calculator and business calculator for school finance and accounting students preparing for class, coursework, or standardized exams.
- INCLUDES TRAVEL CASE, CLEANING CLOTH & BATTERIES– Slim, durable, and easy to keep on hand or store in a backpack or locker. Includes a protective case, cleaning cloth, and batteries so it’s ready out of the box. Large screen with clear contrast (non-backlit) is easy to read during exams or lectures.
Build an Excel amortization schedule
An EMI remains level for a standard amortizing loan, but the amount allocated to interest changes every period. Early payments contain more interest; later payments contain more principal.
Create these headings in row 12:
| Column | Heading |
|---|---|
| A | Period |
| B | Opening balance |
| C | Payment |
| D | Interest |
| E | Principal |
| F | Closing balance |
Assume the first payment is on row 13. Enter these formulas:
A13: 1
B13: =$B$2
C13: =-$B$6
D13: =-IPMT($B$3/$B$5,A13,$B$4*$B$5,$B$2,0,0)
E13: =-PPMT($B$3/$B$5,A13,$B$4*$B$5,$B$2,0,0)
F13: =B13-E13In row 14, enter:
A14: =A13+1
B14: =F13
C14: =-$B$6
D14: =-IPMT($B$3/$B$5,A14,$B$4*$B$5,$B$2,0,0)
E14: =-PPMT($B$3/$B$5,A14,$B$4*$B$5,$B$2,0,0)
F14: =B14-E14Copy row 14 down until the period reaches B4*B5. For a 20-year monthly loan, that means 240 payment rows.
IPMT calculates the interest portion for a specified period. PPMT calculates the principal portion. The period argument must be between 1 and the total number of payments.
Make the schedule calculate in cents
Changing the displayed decimal places only changes how a value looks. It does not round the value used in later calculations. If your schedule must calculate each payment to cents, use ROUND.
Rank #4
- Solves time-value-of-money calculations such as annuities, mortgages, leases, savings, and more
- Performs cash-flow analysis for up to 32 uneven cash flows with up to 4-digit frequencies
- Calculates various financial functions: Net Future Value Net present Value Modified Internal Rate of Return Internal Rate of Return Modified Duration Payback Discounted Payback
- The Texas Instruments BAII Plus Professional features an Automatic Power Down (APD) function for extended battery life
- Prompted display guides you through financial calculations showing current variable and label. Ten-digit display
For example, use these formulas in the first schedule row:
D13: =ROUND(-IPMT($B$3/$B$5,A13,$B$4*$B$5,$B$2,0,0),2)
E13: =ROUND(C13-D13,2)
F13: =ROUND(B13-E13,2)Then use the equivalent rounded formulas in subsequent rows. Because interest and principal are rounded at every payment, the final balance can differ from zero by a few cents. A practical solution is to adjust the final payment:
C13: =IF(A13=$B$4*$B$5,B13+D13,$B$6)Copy that formula down the payment column. It replaces the regular EMI on the last period with the remaining balance plus that period’s interest. This version assumes end-of-period payments and that the balance has not already fallen below zero.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Add input controls with Data Validation
Data Validation can prevent an invalid loan term, rate, or payment frequency from breaking the calculator.
- Select the input cell, such as
B5. - Go to Data > Data Validation.
- On the Settings tab, choose an allowed type such as Whole Number, Decimal, or List.
- Set the limits or list values.
- Optionally configure the Input Message and Error Alert tabs.
- Select OK.
For a payment-frequency selector, choose Allow: List and enter:
Best Value
- Brand New in box; The product ships with all relevant accessories
- Dedicated keys allow easy access to common financial and statistics functions
- Easy-to-use design provides business, finance and statistical calculations fast
- Specially designed to meet the mathematical needs
12,4,2,1That gives users monthly, quarterly, twice-yearly, and annual choices. Data Validation may be unavailable when the worksheet is protected or shared.
Common EMI formula mistakes
| Mistake | What happens | Fix |
|---|---|---|
| Using annual rate without conversion | The payment is seriously overstated or understated. | Use annual rate/payments per year. |
Using years as nper |
A 20-year monthly loan is treated as only 20 payments. | Use years*payments per year. |
| Leaving off the minus sign | PMT displays a negative payment. |
Use =-PMT(...) when you want a positive EMI. |
Entering 8 instead of 8% |
Excel interprets the rate as 800%. | Enter 8% or 0.08. |
| Calling principal divided by months plus interest “EMI” | The result does not follow standard amortization. | Use PMT; each equal payment has changing interest and principal portions. |
| Assuming EMI includes every loan cost | Total borrowing cost is understated. | Add applicable fees, taxes, insurance, and other charges separately. |
Using an invalid period in IPMT or PPMT |
Excel returns an error. | Keep the period between 1 and nper. |
Excel compatibility
PMT, IPMT, and PPMT are available in current Excel versions, including Excel for Microsoft 365, Excel for Mac, Excel 2024, Excel 2021, Excel 2019, Excel 2016, and Excel for the web.
FAQ
What is the Excel formula for monthly EMI?
Use =-PMT(annual_rate/12,loan_term_years*12,loan_amount). With the example cells above, use =-PMT(B3/12,B4*12,B2).
Why does Excel PMT return a negative EMI?
Excel treats the loan amount and repayment as opposite cash flows. If the principal is entered as a positive value, the payment is returned as a negative value. Add a minus sign before PMT to display EMI as positive.
The Tool Desk
Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Does PMT include processing fees, taxes, or insurance?
No. PMT calculates the scheduled principal and interest payment. Fees, taxes, insurance, reserve payments, balloon payments, and prepayments must be modeled separately.
How do I show interest and principal for every EMI?
Use IPMT for the interest portion and PPMT for the principal portion. The period number must run from 1 through the total number of payments.
The Bottom Line
For a fixed-rate, fully amortizing loan, build the calculator around =-PMT(B3/B5,B4*B5,B2,0,0). Keep the rate and payment count in matching periods, use IPMT and PPMT for the amortization schedule, and use ROUND only when you want the calculation—not merely the display—to follow currency precision.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
Free tools Windows power users keep installed
One-click scans. No signup required.
