You can calculate Equated Monthly Installments (EMI) in Excel using the PMT function. Here's how:
1. Understanding the PMT Function
The PMT function calculates the payment for a loan based on constant payments and a constant interest rate. The syntax is:
=PMT(rate, nper, pv, [fv], [type])
Where:
- rate: The interest rate per period. If your annual interest rate is, say, 10%, and you make monthly payments, the rate is 10%/12.
- nper: The total number of payment periods for the loan. If you have a 5-year loan with monthly payments, the nper is 5 * 12 = 60.
- pv: The present value, or the loan amount.
- [fv]: (Optional) The future value, or a cash balance you want to attain after the last payment is made. If omitted, it's assumed to be 0 (most loan scenarios).
- [type]: (Optional) When payments are due. Set to 0 for payments at the end of the period (most common), or 1 for payments at the beginning of the period. If omitted, it's assumed to be 0.
2. Setting up Your Excel Sheet
Organize your data in your Excel sheet like this:
| Cell | Label | Example Value |
|---|---|---|
| A1 | Loan Amount (Principal) | 100000 |
| A2 | Annual Interest Rate | 0.10 (10%) |
| A3 | Loan Tenure (in Years) | 5 |
3. The EMI Formula
In a separate cell (e.g., A4), enter the following formula:
=PMT(A2/12, A3*12, A1)
This formula does the following:
A2/12: Divides the annual interest rate by 12 to get the monthly interest rate.A3*12: Multiplies the loan tenure in years by 12 to get the total number of months.A1: Refers to the loan amount (principal).
4. Formatting the Result
Excel might display the result as a negative number. This is because the PMT function returns the payment you make. To display it as a positive number, you can either:
- Multiply the entire formula by -1:
=-PMT(A2/12, A3*12, A1) - Format the cell as currency.
Example:
Let's say:
- Loan Amount (A1): $100,000
- Annual Interest Rate (A2): 10% (0.10)
- Loan Tenure (A3): 5 years
The formula =PMT(A2/12, A3*12, A1) (or =-PMT(A2/12, A3*12, A1)) would calculate the monthly EMI. In this example, the EMI would be approximately $2,124.70.
In summary, use the PMT function in Excel to efficiently calculate your EMI by inputting the interest rate, loan term and loan amount.
