EMI Calculator: EMI Calculation in Excel

January 14, 2025 By: Gopal Agarwal0 comments

What is EMI?

EMI stands for Equated Monthly Installment. It is the fixed amount paid by a borrower to a lender at a specified date each calendar month. EMIs are used to pay off both the interest and principal each month so that over a specified number of years, the loan is fully paid off.

EMI Formula

The formula to calculate EMI is:

Where:

• P is the loan amount (principal)
• r is the monthly interest rate (annual interest rate divided by 12)
• n is the number of monthly installments (loan tenure in months)

Steps to Calculate EMI in Excel– Download EMI Calculator Excel Sheet

1. Open Excel: Start by opening a new Excel worksheet.
2. Input Variables: Create cells to input the loan amount (P), annual interest rate, and loan tenure (in years). For example:
   • Cell A1: “Loan Amount”
   • Cell B1: Input loan amount (e.g., 500000)
   • Cell A2: “Annual Interest Rate”
   • Cell B2: Input annual interest rate (e.g., 8%)
   • Cell A3: “Loan Tenure (Years)”
   • Cell B3: Input loan tenure (e.g., 5)
3. Convert Annual Rate to Monthly Rate: In a new cell, calculate the monthly interest rate by dividing the annual interest rate by 12. For example, in cell B4:
   • Formula: =B2/12
   • Label cell A4 as “Monthly Interest Rate”
4. Calculate Number of Payments: In another cell, calculate the total number of payments (n) by multiplying the loan tenure in years by 12. For example, in cell B5:
   • Formula: =B3*12
   • Label cell A5 as “Number of Payments”
5. EMI Calculation: Use the PMT function in Excel to calculate the EMI. In a new cell, enter the following formula:
   • Formula: =PMT(B4/100, B5, -B1)
   • This formula uses the monthly interest rate (converted to a decimal), the number of payments, and the loan amount to calculate the EMI.
   • Label cell A6 as “EMI”

Example Calculation

Let’s say you have taken a loan of ₹500,000 at an annual interest rate of 8% for 5 years. Here is how you can calculate the EMI in Excel:

• Loan Amount: ₹500,000
• Annual Interest Rate: 8%
• Loan Tenure: 5 years

Steps in Excel:

• Monthly Interest Rate: =8/12 = 0.6667%
• Number of Payments: =5*12 = 60
• EMI: =PMT(0.6667/100, 60, -500000) = ₹10,138.07

So, the EMI for this loan would be approximately ₹10,138.07 per month.

Advantages of Using Excel for EMI Calculation

1. Customization: You can customize the sheet according to your needs, adding fields for prepayments, varying interest rates, or different loan scenarios.
2. Visualization: Excel allows you to create graphs and charts to visualize the repayment schedule and interest versus principal components.
3. Offline Access: An Excel-based EMI calculator can be accessed anytime without the need for an internet connection.
4. Understanding: Building the calculator yourself helps you understand the EMI calculation process better.