Loan in
60 Minutes
When it comes to managing your finances, calculating your loan EMI (Equated Monthly Instalment) is an essential step in understanding your repayment structure. An accurate EMI calculation helps borrowers plan their budgets effectively and ensures timely repayments.
While there are several online EMI calculators available, building your own EMI calculator in Excel provides the flexibility to customize it according to your specific needs.
Learn about the process of creating a simple yet effective loan EMI calculator in Excel and empower yourself to make informed decisions about your financial commitments.
Understanding EMI First – What is EMI?
Before touching Excel, quick clarity on what we're calculating.
EMI stands for Equated Monthly Instalment. The fixed amount you pay every month to repay a loan.
Each EMI contains two parts: principal repayment and interest payment. Early EMIs have more interest, less principal. Later EMIs reverse this ratio. But the total EMI stays constant throughout.
Understanding these components helps you see exactly where your money goes each month. If you're curious about how lenders calculate these amounts, you might want to check out what is EMI and how it's calculated.
EMI Mathematical Formula
EMI = P × r × (1 + r)^n / [(1 + r)^n – 1]
Where:
P = Principal loan amount
r = Monthly interest rate (annual rate divided by 12)
n = Total number of months (tenure in years × 12)
Looks intimidating? Don't worry. Excel handles this automatically when you calculate EMI in Excel using the PMT function.
Why Excel Makes Sense
Manual calculation works, but it can quickly get tedious when you're trying to compare different options. Calculating once with the formula? Sure, that's fine. But calculating twenty variations? That’s where Excel really shines, saving you hours of work. Plus, creating amortization schedules to show the principal and interest split each month becomes a breeze when you use Excel.
The PMT Function in Excel
Excel's PMT function does exactly what you need to calculate loan EMI in Excel.
Function Syntax
=PMT(rate, nper, pv, [fv], [type])
rate: Monthly interest rate (annual rate / 12)
nper: Total number of payments (tenure in months)
pv: Present value (loan amount as negative number)
fv: Future value (usually 0 for loans, optional)
type: When payment is due (0 for end of period, optional)
Simple Example
Loan amount: ₹10,00,000
Annual interest: 10%
Tenure: 5 years
=PMT(10%/12, 5*12, -1000000)
Result: ₹21,247
This is the simplest way to calculate EMI in Excel using the PMT formula.
Why Negative Number for Loan Amount?
Excel uses cash flow logic. Loan amount is money you receive (inflow to you but outflow from bank). EMI is money you pay (outflow from you).
Using negative principal gives positive EMI result. Using positive principal gives negative EMI result. Both are mathematically correct. Negative principal is just cleaner.
Pro Tip: Always use negative number for loan amount in PMT function. The result will be positive, making your calculations intuitive.
Step-By-Step Excel Setup - How to Calculate EMI in Excel
Let’s build a proper loan EMI calculation in Excel from scratch.
Step 1: Create Input Section
In a new Excel sheet, set up cells for inputs:
|
Cell |
Label |
Example Value |
|
A1 |
Loan Amount |
|
|
B1 |
(value) |
1000000 |
|
A2 |
Annual Interest Rate |
|
|
B2 |
(value) |
10% |
|
A3 |
Tenure (Years) |
|
|
B3 |
(value) |
5 |
Keep labels and values in separate columns for clean layout.
Step 2: Add Calculation Cells
Below your inputs, add calculated values:
|
Cell |
Label |
Formula |
|
A5 |
Monthly Interest Rate |
|
|
B5 |
(formula) |
=B2/12 |
|
A6 |
Number of Months |
|
|
B6 |
(formula) |
=B3*12 |
|
A7 |
Monthly EMI |
|
|
B7 |
(formula) |
=PMT(B5, B6, -B1) |
Step 3: Add Summary Calculations
Useful additional information:
|
Cell |
Label |
Formula |
|
A9 |
Total Payment |
|
|
B9 |
(formula) |
=B7*B6 |
|
A10 |
Total Interest |
|
|
B10 |
(formula) |
=B9-B1 |
Now you have a complete EMI calculator.
Want to understand how interest rates affect your total payment? Our guide on how interest rates are calculated on personal loans breaks it down nicely.
Step 4: Format for Readability
Format currency cells with Indian number format (₹ symbol, comma separators).
Format percentage cells to show percentage sign.
Bold the EMI result to highlight the key output.
Building an Amortisation Schedule in Excel
EMI calculation is step one. Seeing the month-by-month breakdown adds real value.
Setting Up the Schedule
Create column headers starting from row 13 or so:
|
A |
B |
C |
D |
E |
|
Month |
Opening Balance |
EMI |
Interest |
Principal |
First Row Formulas
Assuming your inputs are in cells as described earlier:
Month (A14): 1
Opening Balance (B14): =B1 (your loan amount)
EMI (C14): =B7 (your calculated EMI)
Interest (D14): =B14*B5 (opening balance × monthly rate)
Principal (E14): =C14-D14 (EMI minus interest)
Closing Balance (F14): =B14-E14 (opening minus principal)
Subsequent Row Formulas
For month 2 and beyond:
Month (A15): =A14+1
Opening Balance (B15): =F14 (previous closing balance)
EMI (C15): =C14 (same EMI)
Interest (D15): =B15*B5
Principal (E15): =C15-D15
Closing Balance (F15): =B15-E15
Extending the Schedule
Copy row 15 formulas down for all months of your tenure.
For 5-year loan, you need 60 rows.
For 20-year loan, you need 240 rows.
The closing balance in the final row should be zero (or very close to zero due to rounding).
Tip: Use the ROUND function on interest and principal calculations to avoid small rounding discrepancies in your final balance.
Practical Scenarios and Comparisons
Your Excel calculator becomes powerful for analysis.
Comparing Different Tenures
Create multiple calculation sections side by side:
|
3 Years |
5 Years |
7 Years |
|
EMI: ₹32,267 |
EMI: ₹21,247 |
EMI: ₹16,601 |
|
Total Interest: ₹1,61,619 |
Total Interest: ₹2,74,820 |
Total Interest: ₹3,94,484 |
Same loan amount and rate. Longer tenure means lower EMI but much higher total interest.
Choosing the right loan tenure isn't just about affordability, it's about balancing monthly comfort with long-term cost.
Comparing Different Rates
|
9% Rate |
10% Rate |
11% Rate |
|
EMI: ₹20,758 |
EMI: ₹21,247 |
EMI: ₹21,742 |
|
Total Interest: ₹2,45,480 |
Total Interest: ₹2,74,820 |
Total Interest: ₹3,04,520 |
One percent rate difference adds roughly ₹30,000 to total interest over 5 years.
Prepayment Impact Analysis
Modify your amortisation schedule to show prepayment effects.
Add a column for additional payment. Reduce closing balance by prepayment amount. Remaining tenure shortens automatically.
Visualising prepayment savings motivates early repayment behaviour.
Visualising prepayment savings motivates early repayment behaviour. Understanding the pros and cons of personal loan prepayment helps you decide if it's right for your situation.
Common Mistakes to Avoid
A few pitfalls catch people regularly.
Rate Conversion Errors
Annual rate must be divided by 12 for monthly calculations. Forget this step and your EMI will be wildly wrong.
10% annual = 0.833% monthly = 0.008333 as decimal
Not 10% monthly. That would be a terrible loan.
Tenure Conversion Errors
Years must be multiplied by 12 for monthly calculations.
5-year tenure = 60 months. Not 5 in the nper argument.
Sign Confusion
Positive loan amount gives negative EMI. Confusing but mathematically correct.
Use negative loan amount for intuitive positive EMI result.
Rounding Issues
Interest calculations create tiny decimal differences. Over 240 months, these accumulate.
Round individual calculations to two decimal places for clean schedules.
Percentage Format Mistakes
Excel interprets 10 as 10, not 10%. Enter either 10% or 0.10 for ten percent.
Entering just 10 when meaning 10% creates thousand percent interest calculations.
Beyond Basic EMI: Advanced Calculations
Excel handles more complex scenarios too.
Changing Interest Rates
Floating rate loans change periodically. Build separate calculation blocks for each rate period and link them.
First 36 months at 9%. Next 24 months at 10%. Different EMIs for each period.
Part-Prepayment Modelling
Add prepayment column to amortisation. Closing balance reduces by prepayment amount plus principal.
New EMI calculation from reduced balance for remaining tenure.
If you're considering making part payments on your personal loan, your Excel model can show exactly how much you'll save.
EMI vs Lump Sum Comparison
What saves more: increasing EMI by ₹5,000 monthly or making annual ₹60,000 prepayments?
Build both scenarios and compare total interest paid.
Loan Comparison Dashboard
Multiple loans on one sheet. Car loan, home loan, personal loan. Total monthly outflow. Payment completion dates.
Complete financial picture in one view.
Tip: Create a master template once. Copy and modify for each new loan scenario you want to analyse.
Quick Reference: EMI Calculation Formulas
Copy-paste ready formulas for common needs.
Basic Monthly EMI
=PMT(annual_rate/12, years*12, -loan_amount)
Total Interest Payable
=PMT(rate/12, years*12, -amount)*years*12 - amount
Principal Component of Specific EMI
=PPMT(rate/12, payment_number, years*12, -amount)
Interest Component of Specific EMI
=IPMT(rate/12, payment_number, years*12, -amount)
Cumulative Principal Paid (First N Payments)
=CUMPRINC(rate/12, years*12, amount, 1, n, 0)
Cumulative Interest Paid (First N Payments)
=CUMIPMT(rate/12, years*12, amount, 1, n, 0)
When You Need Funds Quickly
Building Excel calculators works great for planning. But when you need a loan, quick processing matters more than spreadsheets.
Finnable, an RBI-licensed NBFC, offers personal loans from 25,000 to 10,000,00. Interest rates range from 15% to 30.99% annually based on credit profile.
Disbursement happens in as fast as sixty minutes for eligible applicants. Processing fees up to five percent apply. The entire process is 100% digital and paperless, making it convenient for busy professionals.
Check your eligibility with Finnable
Use your Excel skills to compare options. Then apply where the numbers work best for your situation.
Use the PMT function: =PMT (rate/12, tenure*12, -loan_amount). Rate is annual interest, tenure is years, loan amount entered as negative gives positive EMI result.
PMT calculates periodic payment for a loan based on constant interest rate and regular payments. It handles the complex EMI formula automatically.
Check rate conversion (annual to monthly) and tenure conversion (years to months). Also verify whether you're using the same rounding approach.
Yes. Build columns for month, opening balance, EMI, interest, principal, and closing balance. Use formulas linking each row to calculate the complete schedule.
Add a prepayment column. Reduce closing balance by prepayment amount. Recalculate remaining tenure or EMI from the new reduced balance.
Loan in
60 Minutes
Understanding EMI First – What is EMI?
Why Excel Makes Sense
The PMT Function in Excel
Step-By-Step Excel Setup - How to Calculate EMI in Excel
Building an Amortisation Schedule in Excel
Practical Scenarios and Comparisons
Common Mistakes to Avoid
Beyond Basic EMI: Advanced Calculations
Quick Reference: EMI Calculation Formulas
When You Need Funds Quickly
