How to Calculate Your Personal Loan EMI Using Excel: Step-by-Step Guide
Arjun had just received his salary slip and was staring at his phone, trying to figure out whether he could afford a Rs 3 lakh personal loan without stretching his budget too thin. He knew his EMI would come out of his account every month for the next three years but what exactly would that number be? Sound familiar?
Most borrowers face this exact moment of uncertainty before applying for a loan. The good news: you do not need a financial advisor to get this answer. A simple EMI calculator in Excel can give you your monthly payment figure in under two minutes for free, offline, and with complete transparency.
This guide walks you through the EMI calculation formula in Excel, step by step. Whether you are a first-time borrower or comparing multiple loan offers, this is the tool that puts you in control before you sign anything.
What Is a Personal Loan EMI?
An Equated Monthly Instalment (EMI) is the fixed amount you repay to your lender every month throughout your loan tenure. It is not just the principal amount divided by the number of months it bundles both the principal repayment and interest into one consistent figure, making budgeting predictable.
Understanding your EMI before applying helps you:
- Assess whether the monthly outgo fits within your income
- Choose the right tenure to balance affordability and total interest cost
- Compare loan offers across lenders with confidence
- Avoid loan rejection due to an over-stretched debt-to-income ratio
The EMI Calculation Formula in Excel
There are two ways to calculate your EMI in Excel: the manual mathematical formula and the built-in PMT function. Both arrive at the same result.
Method 1: The Mathematical Formula
EMI = [P × R × (1 + R)^N] / [(1 + R)^N – 1]
Where:
- P = Principal loan amount (the amount you borrow)
- R = Monthly interest rate = Annual interest rate ÷ 12 ÷ 100
- N = Loan tenure in months
Method 2: Excel's Built-In PMT Function (Recommended)
=PMT(RATE, NPER, PV, FV, TYPE)
| Parameter | What to Enter |
| RATE | Monthly interest rate = Annual rate ÷ 12 ÷ 100 |
| NPER | Total number of monthly instalments (tenure in months) |
| PV | Loan amount (enter as negative, e.g., -300000) |
| FV | Future value - always enter 0 for loans |
| TYPE | 0 if EMI is paid at month-end; 1 if paid at month-start |
How to Calculate Personal Loan EMI in Excel: Step-by-Step
Here is a practical walkthrough using a real example. Suppose you are borrowing Rs 3,00,000 for 3 years (36 months) at 18% per annum. Hero FinCorp's starting interest rate for eligible borrowers with a CIBIL score of 725 and above.
Step 1: Open a New Excel Sheet
Create a fresh spreadsheet. Label three rows: Loan Amount, Annual Interest Rate, and Tenure (Years).
Step 2: Enter Your Loan Details
| Parameter | Value |
| Loan Amount (P) | Rs 3,00,000 |
| Annual Interest Rate | 18% |
| Monthly Interest Rate (R) | 18 ÷ 12 ÷ 100 = 0.015 |
| Tenure (Years) | 3 Years |
| NPER (Months) | 36 |
Step 3: Convert the Annual Rate to Monthly
In a cell, type: = 18/12/100 — This gives you 0.015, your monthly interest rate. Do not skip this step; entering the annual rate directly into the PMT formula is the most common error borrowers make.
Step 4: Convert Tenure to Months
In another cell, type: = 3 * 12 This gives you 36, the total number of installments.
Step 5: Enter the PMT Formula
In the EMI cell, enter: =PMT(0.015, 36, -300000, 0, 1)
Press Enter. Excel will return your EMI amount instantly.
EMI Calculation Result
| Loan Amount | Tenure | Monthly EMI |
| Rs 3,00,000 | 36 Months @ 18% p.a. | Rs 10,847 (approx.) |
This means Arjun or you would pay approximately Rs 10,847 per month over 3 years. Total repayment: ~Rs 3,90,492, of which ~Rs 90,492 is interest.
Quick EMI Reference Table (Rs 3,00,000 at 18% p.a.)
Use this to instantly compare how tenure affects your monthly outgo:
| Tenure | Monthly EMI (Approx.) | Total Interest Paid |
| 12 Months | Rs 27,533 | Rs 30,396 |
| 24 Months | Rs 14,953 | Rs 58,872 |
| 36 Months | Rs 10,847 | Rs 90,492 |
Key insight: A longer tenure reduces your monthly EMI but significantly increases the total interest you pay. Choose the shortest tenure your budget can comfortably support.
EMI Calculator Excel Sheet vs. Online EMI Calculator: Which to Use?
| Feature | Excel EMI Calculator | Online EMI Calculator |
| Internet Required | No | Yes |
| Scenario Comparison | Highly Flexible | Limited |
| Amortisation Schedule | Can be built manually | Often provided instantly |
| Error Risk | Moderate (manual input) | Low (automated) |
| Visual Charts | Customisable | Built-in |
Use Excel when you want to model multiple scenarios with different loan amounts, rates, and tenures all in one sheet. Use an online EMI calculator for a quick, single-scenario check before applying.
Critical Points to Remember When Using the EMI Formula in Excel
- Always convert the annual interest rate to monthly by dividing by 12 before entering it into the PMT formula.
- Enter the loan amount as a negative value in PV to get a positive EMI output. Entering it as positive gives a negative result, which is mathematically correct but visually confusing.
- The formula does not account for processing fees, GST, or prepayment charges. Add these separately for a complete cost picture.
- If your lender charges a floating interest rate, update the rate cell periodically to re-calculate your revised EMI.
- Always keep an emergency fund of at least 2–3 months' EMI before you commit to a loan.
How to Apply for a Personal Loan After Calculating Your EMI
Once your EMI fits comfortably within your monthly budget (ideally not more than 40 - 50% of your take-home pay), you are ready to apply. Here is how to apply with Hero FinCorp:
- Visit the Hero FinCorp website or install the Personal Loan app
- Click 'Apply Now' on the Personal Loan page
- Enter your mobile number and verify via OTP
- Select the loan amount you need (up to Rs 5 Lakh)
- Verify your KYC details to check income eligibility
- Digitally sign and submit no physical documents required
Eligibility at a glance: Age 21–58 years | Minimum monthly income Rs 15,000 | CIBIL score 725+ | Interest rate starting at 18% p.a. | Tenure 12–36 months
Conclusion
The EMI calculation formula in Excel is one of the most practical tools a borrower can use before committing to a loan. It takes under two minutes to set up and gives you complete clarity on your monthly obligation, total interest cost, and how different tenures compare. Use the PMT function as your first step, not an afterthought in the loan planning process.
Once you have your number and you know it fits your budget, applying for a personal loan is straightforward. With digital KYC, instant approval, and quick disbursal, getting the funds you need does not have to be a lengthy process.
Frequently Asked Questions
How can I create an EMI calculator in Excel?
To create an EMI calculator in Excel, you need to use the PMT function ( the financial functions) and input the loan details. You can then customize the spreadsheet to suit your needs and preferences.
How to calculate EMI in Excel without the PMT function?
Use the formula: EMI = [P × R × (1+R)^N] / [(1+R)^N – 1]. Substitute your values for P (principal), R (monthly rate), and N (months). Both methods give the same result.
Can I create an EMI calculator Excel sheet for multiple scenarios?
Yes. Create a table with different loan amounts, interest rates, and tenures. Reference each set of values in separate PMT formulas. This is one of Excel's biggest advantages over online calculators unlimited scenario comparison in a single view.
Why does my Excel EMI come out as a negative number?
Because you entered the loan amount (PV) as a positive value. In the PMT formula, the loan amount should be entered as a negative number (e.g., -300000) to get a positive EMI result. Alternatively, add a minus sign before the PMT formula: =-PMT(...).
Does a CIBIL score affect EMI?
Your CIBIL score affects the interest rate you are offered, which directly impacts your EMI. Borrowers with a score of 725 and above typically qualify for more competitive rates. A lower interest rate means a lower EMI for the same loan amount and tenure.
How do I verify my Excel EMI calculation?
Cross-check your Excel result with the online Personal Loan EMI Calculator on the Hero FinCorp website. If both numbers match, your formula is correctly set up. You can also manually verify using the EMI formula to triple-check.
What is the difference between EMI paid at month-start vs. month-end?
In the PMT formula, TYPE = 1 means EMI is paid at the start of the month (slightly lower EMI due to earlier principal reduction). TYPE = 0 means payment at month-end, which is the more common arrangement. Always confirm your repayment schedule with your lender.
Disclaimer: The information provided in this blog post is intended for informational purposes only. The content is based on research and opinions available at the time of writing. While we strive to ensure accuracy, we do not claim to be exhaustive or definitive. Readers are advised to independently verify any details mentioned here, such as specifications, features, and availability, before making any decisions. Hero FinCorp does not take responsibility for any discrepancies, inaccuracies, or changes that may occur after the publication of this blog. The choice to rely on the information presented herein is at the reader's discretion, and we recommend consulting official sources and experts for the most up-to-date and accurate information about the featured products.
