Are you planning a loan or teaching finance? Understanding how to calculate EMI (Equated Monthly Installment) using Microsoft Excel is a must-have skill. Whether it’s a home loan, personal loan, or education loan, Excel makes it simple and quick.
In this blog post, we’ll walk you through how to calculate EMI using Excel’s PMT function — with examples and formulas.
💡 What is EMI?
EMI stands for Equated Monthly Installment. It’s a fixed monthly payment made by a borrower to a lender. It consists of two parts:
-
Principal – the original loan amount
-
Interest – the cost of borrowing the money
📘 EMI Formula in Excel
To calculate EMI in Excel, we use the PMT function:
Where:
-
rate= interest rate per period (monthly interest) -
nper= total number of payments (loan tenure in months) -
pv= present value or loan amount
🧠 Example:
Let’s say:
-
Loan Amount (Principal) = ₹5,00,000
-
Annual Interest Rate = 10%
-
Tenure = 5 years (60 months)
In Excel:
Result: ₹10,624.67
(This is your monthly EMI)
👉 Note: We use a negative sign before the principal (-500000) to get a positive EMI output.
📊 Bonus: Create a Mini EMI Calculator in Excel
| A | B |
|---|---|
| Loan Amount | 500000 |
| Annual Interest Rate | 10% |
| Loan Tenure (in years) | 5 |
| Monthly EMI | =PMT(B3/12, B4*12, -B2) |
You can now change the values in column B and see the EMI update instantly.
🎯 Why Learn This?
-
✅ Helps in financial planning
-
✅ Essential for finance & accounting professionals
-
✅ Useful for personal budgeting and client advisory
-
✅ Frequently asked in interviews & assessments
📌 Final Tip
You can also use Excel to break down the EMI into Principal & Interest components, create amortization schedules, and even visualize loan repayments using charts.
📚 Learn More with VertiSkills
Want to master Excel for accounting and finance?
Join our Advanced Excel Course – packed with real-life projects, functions, and job-oriented training.
📅 New Batch Starting Soon!
📞 Contact us at [Your Phone Number] | 🌐 Visit: www.vertiskills.com
Let me know if you’d like a downloadable Excel template or images for this blog!