admin 
PMT, PPMT & IPMT Functions in Excel: Calculate Loan EMI Like a Pro
Have you ever wondered how banks calculate your monthly EMI for a home loan, car loan, education loan, or personal loan?
Most people use online EMI calculators without understanding the actual calculations behind them. However, if you’re a student, accountant, finance professional, business owner, or Excel learner, knowing the PMT, PPMT, and IPMT Functions in Excel can help you create your own loan calculators and financial models.
- How EMI is calculated in Excel
- How PMT function works
- How IPMT function works
- How PPMT function works
- How to create a loan repayment schedule
- Common mistakes to avoid
Why Learn EMI Calculation Functions in Excel?
Finance and accounting professionals regularly work with loans, repayments, investments, and financial reports.
Learning these Excel functions helps you:
- Calculate loan EMIs instantly
- Separate principal and interest amounts
- Create repayment schedules
- Build financial dashboards
- Improve accounting skills
- Prepare for Excel interviews
What is EMI?
EMI (Equated Monthly Installment) is the fixed amount paid every month to repay a loan.
An EMI consists of two parts:
- Principal Amount
- Interest Amount
- Interest amount decreases
- Principal repayment increases
Understanding the PMT Function in Excel
What is PMT?
The PMT function calculates the fixed EMI required to repay a loan.
Syntax
=PMT(rate,nper,pv)
| Argument | Description |
|---|---|
| Rate | Interest rate per period |
| Nper | Total number of payment periods |
| PV | Loan Amount (Present Value) |
Practical Example: Home Loan EMI Calculation
| Particular | Value |
|---|---|
| Loan Amount | ₹10,00,000 |
| Interest Rate | 10% |
| Loan Tenure | 5 Years |
Formula:
=PMT(10%/12,5*12,-1000000)
Result: Monthly EMI = ₹21,247.04
The negative sign is used because Excel treats the loan amount as money received.
Understanding the IPMT Function
What is IPMT?
The IPMT function calculates the interest portion of a specific EMI.
Syntax
=IPMT(rate,per,nper,pv)
Example
Interest paid in the first month:
=IPMT(10%/12,1,60,-1000000)
Result: ₹8,333.33 (Approx.)
This tells you how much interest is included in the first EMI.
Understanding the PPMT Function
What is PPMT?
The PPMT function calculates the principal portion of a specific EMI.
Syntax
=PPMT(rate,per,nper,pv)
Example
Principal paid in first EMI:
=PPMT(10%/12,1,60,-1000000)
Result: ₹12,913.71 (Approx.)
Relationship Between PMT, PPMT & IPMT
The relationship is very simple:
| Component | Amount |
|---|---|
| EMI (PMT) | ₹21,247.04 |
| Principal (PPMT) | ₹12,913.71 |
| Interest (IPMT) | ₹8,333.33 |
Real-Life Applications of PMT, PPMT & IPMT
Banking Sector
- Loan processing
- EMI calculations
- Customer loan reports
Accounting Jobs
- Financial analysis
- Interest calculations
- Repayment schedules
Business Owners
- Equipment financing
- Investment planning
- Cash flow forecasting
Students
- Finance projects
- Excel assignments
- Placement preparation
Step-by-Step Loan Schedule in Excel
Create the following columns:
| Month | EMI | Principal | Interest | Balance |
|---|
Use:
- PMT() for EMI
- PPMT() for Principal
- IPMT() for Interest
This creates a professional loan amortization schedule similar to what banks use.
Common Mistakes While Using EMI Functions
1. Incorrect Interest Rate
Wrong:
=PMT(10%,60,-1000000)
Correct:
=PMT(10%/12,60,-1000000)
Because EMI is calculated monthly.
2. Forgetting Negative Loan Amount
Wrong:
=PMT(10%/12,60,1000000)
Correct:
=PMT(10%/12,60,-1000000)
3. Incorrect Number of Periods
Always convert years into months.
Example:
5 Years = 5*12 = 60 Months
Important Excel Financial Functions to Learn Next
- FV (Future Value)
- PV (Present Value)
- NPV (Net Present Value)
- IRR (Internal Rate of Return)
- XIRR
- RATE
- NPER
These functions are widely used in finance, accounting, and corporate reporting.
Frequently Asked Questions (FAQs)
Is PMT used only for loans?
No. PMT can also calculate recurring payments for investments, savings plans, and financial projections.
What is the difference between PPMT and IPMT?
PPMT calculates the principal portion of EMI, while IPMT calculates the interest portion.
Which industries use these functions?
- Banking
- Finance
- Accounting
- Investment Analysis
- Corporate Reporting
Are PMT, PPMT and IPMT asked in Excel interviews?
Yes. These functions are frequently asked in Advanced Excel interviews, especially for finance and accounting roles.
Can beginners learn these functions?
Absolutely. Once you understand the basic loan structure, these functions become very easy to use.
Key Takeaways
- PMT calculates the total EMI.
- IPMT calculates the interest portion of EMI.
- PPMT calculates the principal portion of EMI.
- These functions are essential for finance professionals.
- They help create loan calculators and repayment schedules.
- Learning them improves Excel skills and job opportunities.
Watch Complete Excel Learning Playlist
Watch BPA Educators Excel Playlist
Join Advanced Excel Training at BPA Educators
Master Excel formulas, financial functions, dashboards, Pivot Tables, MIS reporting, and data analysis with practical training.
