You don’t need to manually use the EMI formula in Excel. Excel already has a built-in function that calculates EMI instantly.
Excel Function for EMI
The function used is:
PMT(rate, nper, pv)
What Each Parameter Means
| Parameter | Meaning |
|---|---|
| rate | Monthly interest rate |
| nper | Total number of months |
| pv | Loan amount (present value) |
Step-by-Step: EMI Calculation in Excel
Step 1: Convert Interest Rate to Monthly
If your annual interest rate is 12%, then:
Monthly rate = 12 ÷ 12 ÷ 100 = 0.01
Step 2: Convert Tenure into Months
If loan is 5 years:
nper = 5 × 12 = 60 months
Step 3: Use Excel Formula
Type this in any Excel cell:
=PMT(0.01, 60, -500000)
Why is Loan Amount Negative?
In Excel:
- Loan is treated as money received (cash inflow)
- EMI is money paid back (cash outflow)
So we use negative sign (-) for loan amount.
Example Output
For:
- Loan = 500,000
- Interest = 12%
- Tenure = 5 years
Excel returns:
EMI ≈ 11,122 per month
Advanced Excel EMI Formula (Using Cell Reference)
Instead of typing numbers directly, use cells:
| Cell | Value |
|---|---|
| A1 | Loan amount (500000) |
| A2 | Annual interest (12%) |
| A3 | Tenure in years (5) |
Formula:
=PMT(A2/12, A3*12, -A1)
Optional: Calculate Total Payment in Excel
To find total repayment:
=PMT(A2/12, A3*12, -A1) * A3 * 12
Optional: Calculate Total Interest
=(PMT(A2/12, A3*12, -A1) * A3 * 12) - A1
Quick Summary
To calculate EMI in Excel:
- Convert annual interest to monthly
- Convert years to months
- Use PMT formula
- Add negative sign for loan amount
Final Tip
Excel is more powerful than manual calculation because you can:
- Compare multiple loan scenarios
- Change interest rates instantly
- Build your own EMI calculator sheet
If you want, I can also create:
- A ready-made Excel EMI calculator file
- Or a Google Sheets version for your website users
- Or SEO article version for your loan calculator page