Introduction
The IPMT function in Excel is used to calculate the interest portion of a loan or investment payment for a specific period. It is useful for understanding how much interest you pay in each EMI and how the interest component changes over the loan period. In this tutorial, you will learn its syntax, arguments, and practical financial examples.
What Is the IPMT Function in Excel?
IPMT stands for Interest Payment.
When you pay an EMI on a loan, the payment generally contains two parts:
- Interest Component: The amount charged by the bank for lending you money.
- Principal Component: The amount that reduces your original loan balance.
The IPMT function calculates only the interest component for a particular payment period.
For example, suppose you take a loan of ₹1,00,000 at an annual interest rate of 12%, with monthly payments over 12 months.
Your monthly interest rate is 1%.
In the first month, the interest is calculated on the full outstanding loan amount:
₹1,00,000 × 1% = ₹1,000
The IPMT function can calculate this interest amount for the first month and for every other payment period.
Important: IPMT calculates interest for one specific period. It does not calculate the complete EMI or the principal portion.
IPMT Function Syntax
=IPMT(rate, per, nper, pv, [fv], [type])
The function contains six arguments.
| Argument | Meaning |
|---|---|
| rate | Interest rate for each payment period |
| per | The specific payment period for which you want to calculate interest |
| nper | Total number of payment periods |
| pv | Present value or original loan amount |
| fv | Future value or remaining balance after the final payment (optional) |
| type | Payment timing: 0 for end of period, 1 for beginning of period (optional) |
Understanding Each Argument
1. rate – Interest Rate
This represents the interest rate for one payment period.
For example:
- Annual interest rate = 12%.
- Monthly interest rate = 12% / 12 = 1%.
- Quarterly interest rate = 12% / 4 = 3%.
If your loan payments are monthly, use the monthly interest rate.
2. per – Payment Period
This argument identifies the particular payment period for which you want to calculate interest.
For example:
1means the first payment period.2means the second payment period.3means the third payment period.12means the twelfth payment period.
The value of per must be between 1 and the total number of payment periods.
3. nper – Total Number of Periods
This represents the total number of payments during the loan or investment period.
For example:
- 12 monthly payments = 12 periods.
- 24 monthly payments = 24 periods.
- 5 annual payments = 5 periods.
4. pv – Present Value
Present Value represents the current loan amount or investment amount.
For example, if a bank provides a loan of ₹1,00,000, the PV is ₹1,00,000.
5. fv – Future Value
This is the balance you want to have after the final payment.
For a fully repaid loan, FV is generally 0.
This argument is optional. If omitted, Excel assumes FV is 0.
6. type – Payment Timing
This argument tells Excel when payments are made.
| Type | Meaning |
|---|---|
| 0 | Payment at the end of each period |
| 1 | Payment at the beginning of each period |
If you omit this argument, Excel assumes 0.
How Does IPMT Function Calculate Interest?
Let’s understand the calculation using a simple loan.
Suppose:
| Particulars | Value |
|---|---|
| Loan Amount | ₹1,00,000 |
| Annual Interest Rate | 12% |
| Monthly Interest Rate | 1% |
| Loan Duration | 12 Months |
In the first month, interest is calculated on the complete loan amount:
₹1,00,000 × 1% = ₹1,000
As you repay the loan, the outstanding principal reduces. Therefore, the interest portion of later payments also decreases.
For this loan, the monthly EMI is approximately ₹8,884.88.
| Payment Period | Interest Component (Approx.) |
|---|---|
| Month 1 | ₹1,000.00 |
| Month 2 | ₹921.15 |
| Month 3 | ₹841.51 |
| Month 4 | ₹761.08 |
| Month 5 | ₹679.84 |
Notice that the interest component decreases with each payment because the outstanding loan balance becomes smaller.
Important: Excel generally displays IPMT results as negative amounts when the loan amount (PV) is positive. This represents interest paid by the borrower.
Practical Examples of IPMT Function in Excel
Let’s understand IPMT Function through practical examples from loan calculations, business finance, and payment schedules.
Question 1: Calculate the Interest Component of the First EMI
Problem Statement
A customer takes a personal loan of ₹1,00,000 from a bank. The annual interest rate is 12%, and the loan must be repaid over 12 months.
Calculate the interest portion of the first monthly EMI.
Data Table
| Particulars | Value |
|---|---|
| Loan Amount | ₹1,00,000 |
| Annual Interest Rate | 12% |
| Monthly Interest Rate | 1% |
| Loan Duration | 12 Months |
| Payment Period | 1 |
Excel Formula
=IPMT(1%,1,12,100000)
Expected Result
-₹1,000.00
Explanation
1%is the monthly interest rate.1represents the first payment period.12represents the total monthly payments.100000is the original loan amount.
Excel calculates ₹1,000 as the interest for the first month.
The negative sign indicates an outgoing payment.
Concept Covered: Calculating the interest component of the first EMI.
Question 2: Calculate Interest for the Second Month
Problem Statement
A company employee has taken a loan of ₹1,00,000 at 12% annual interest. The loan duration is 12 months.
Calculate the interest component for the second monthly payment.
Data Table
| Particulars | Value |
|---|---|
| Loan Amount | ₹1,00,000 |
| Monthly Interest Rate | 1% |
| Total Periods | 12 |
| Payment Period | 2 |
Excel Formula
=IPMT(1%,2,12,100000)
Expected Result
-₹921.15 (approximately)
Explanation
The second month’s interest is lower than the first month’s interest because the first EMI has already reduced the outstanding loan balance.
IPMT calculates the interest for period 2 rather than calculating the complete EMI.
Concept Covered: Understanding how the interest component decreases as the loan balance reduces.
Question 3: Calculate Interest for the Sixth EMI
Problem Statement
A customer takes a car loan of ₹3,00,000 at an annual interest rate of 10.8%. The loan is repayable through 36 monthly payments.
Calculate the interest portion of the sixth EMI.
Data Table
| Particulars | Value |
|---|---|
| Car Loan | ₹3,00,000 |
| Annual Interest Rate | 10.8% |
| Monthly Interest Rate | 0.9% |
| Loan Duration | 36 Months |
| Payment Period | 6 |
Excel Formula
=IPMT(0.9%,6,36,300000)
Expected Result
-₹2,444.67 (approximately)
Explanation
The monthly interest rate is 0.9%, and the total loan period is 36 months.
The value 6 tells Excel to calculate interest for the sixth payment period.
Excel returns approximately ₹2,444.67 as the interest portion of the sixth EMI.
Concept Covered: Calculating interest for a selected EMI in a car loan.
Question 4: Calculate Interest for the Twelfth EMI
Problem Statement
A business takes a loan of ₹5,00,000 at an annual interest rate of 12%. The loan is repayable over 60 months.
Calculate the interest component of the twelfth monthly payment.
Data Table
| Particulars | Value |
|---|---|
| Business Loan | ₹5,00,000 |
| Annual Interest Rate | 12% |
| Monthly Interest Rate | 1% |
| Loan Duration | 60 Months |
| Payment Period | 12 |
Excel Formula
=IPMT(1%,12,60,500000)
Expected Result
-₹4,287.93 (approximately)
Explanation
The first payment period calculates interest on the original loan amount. As payments continue, the outstanding principal decreases.
By the twelfth month, the interest component is approximately ₹4,287.93.
Concept Covered: Finding the interest portion of a payment after several months of loan repayment.
Question 5: Calculate Interest for the First Quarterly Payment
Problem Statement
A business takes a loan of ₹2,00,000 at an annual interest rate of 12%. The business makes quarterly payments over 3 years.
Calculate the interest portion of the first quarterly payment.
Data Table
| Particulars | Value |
|---|---|
| Business Loan | ₹2,00,000 |
| Annual Interest Rate | 12% |
| Quarterly Interest Rate | 3% |
| Loan Duration | 3 Years |
| Total Quarterly Payments | 12 |
| Payment Period | 1 |
Excel Formula
=IPMT(3%,1,12,200000)
Expected Result
-₹6,000.00
Explanation
The loan has quarterly payments, so the annual interest rate is divided by four.
12% / 4 = 3%
The first quarter’s interest is calculated on the full loan amount:
₹2,00,000 × 3% = ₹6,000
Concept Covered: Calculating interest when payments are made quarterly instead of monthly.
Question 6: Calculate Interest When Payments Are Made at the Beginning of Each Period
Problem Statement
A company takes a loan of ₹1,50,000 at a monthly interest rate of 1%. The loan is repayable over 24 months, and payments are made at the beginning of each month.
Calculate the interest component of the first payment period.
Data Table
| Particulars | Value |
|---|---|
| Loan Amount | ₹1,50,000 |
| Monthly Interest Rate | 1% |
| Loan Duration | 24 Months |
| Payment Period | 1 |
| Payment Timing | Beginning of Month |
Excel Formula
=IPMT(1%,1,24,150000,0,1)
Expected Result
₹0.00
Explanation
The final argument is 1, which means payments are made at the beginning of each period.
The first payment is made immediately, before any interest has accumulated during the first month.
Therefore, the interest component for the first period is zero.
Concept Covered: Understanding how beginning-of-period payments affect the interest calculation.
Question 7: Calculate Interest Using Cell References
Problem Statement
A finance department maintains loan details in Excel. The team wants to calculate the interest component for a selected payment period without changing the formula manually.
Create the following table and calculate the interest amount using cell references.
Data Table
| Particulars | Cell | Value |
|---|---|---|
| Loan Amount | B2 | ₹2,00,000 |
| Annual Interest Rate | B3 | 12% |
| Monthly Interest Rate | B4 | 1% |
| Total Periods | B5 | 36 |
| Selected Payment Period | B6 | 10 |
Excel Formula
=IPMT(B4,B6,B5,B2)
Expected Result
-₹4,888.70 (approximately)
Explanation
The formula uses cell references instead of fixed numbers.
B4contains the monthly interest rate.B6contains the selected payment period.B5contains the total number of payments.B2contains the original loan amount.
You can change the selected payment period in B6 to calculate interest for another month.
Concept Covered: Creating a flexible interest calculator using cell references.
Student Practice Tasks
Try solving these questions using the IPMT function.
Task 1: A person takes a loan of ₹2,00,000 at an annual interest rate of 12% for 24 months. Calculate the interest component of the third EMI.
Task 2: A customer takes a car loan of ₹4,00,000 at an annual interest rate of 9.6% for 48 months. Calculate the interest component of the tenth EMI.
Task 3: A business takes a loan of ₹3,00,000 at an annual interest rate of 8% and makes quarterly payments over 4 years. Calculate the interest component of the second quarterly payment.
Task 4: A person takes a loan of ₹1,20,000 at a monthly interest rate of 1% for 12 months. Calculate the interest component of the last EMI.
Final Student Challenge: Build a Loan Interest Calculator
Create the following Excel table:
| Particulars | Value |
|---|---|
| Loan Amount | ₹5,00,000 |
| Annual Interest Rate | 12% |
| Monthly Interest Rate | Calculate manually as Annual Rate / 12 |
| Loan Duration | 60 Months |
| Selected Payment Period | 1 |
Your Tasks
- Enter all loan details in Excel.
- Calculate the monthly interest rate.
- Use the IPMT function to calculate the interest component for the first month.
- Change the selected payment period to 12.
- Calculate the interest component for month 12.
- Change the selected payment period to 36.
- Compare the interest amounts for months 1, 12, and 36.
Challenge Goal: Create a simple calculator that shows how the interest portion of a loan payment changes over time.
Key Takeaways
- IPMT Function stands for Interest Payment.
- It calculates the interest portion of a payment for a specific period.
- The syntax is
=IPMT(rate,per,nper,pv,[fv],[type]). - The
rateargument represents the interest rate for each payment period. - The
perargument identifies the payment period. - The
nperargument represents the total number of payment periods. - The
pvargument represents the original loan or investment amount. - Type 0 represents payments at the end of each period.
- Type 1 represents payments at the beginning of each period.
- IPMT Function does not calculate the complete EMI; it calculates only the interest portion.
- For a typical reducing-balance loan, the interest component decreases as the outstanding principal reduces.
Frequently Asked Questions (FAQs)
1. What is the IPMT function in Excel?
The IPMT function calculates the interest portion of a loan or investment payment for a specific period.
2. What is the full form of IPMT Function in excel?
IPMT stands for Interest Payment.
3. What is the syntax of the IPMT function?
The syntax is =IPMT(rate,per,nper,pv,[fv],[type]).
4. What does the per argument mean?
The per argument specifies the payment period for which you want to calculate interest, such as the first, second, or twelfth month.
5. Can IPMT Function calculate monthly loan interest?
Yes. Use the monthly interest rate, total number of monthly payments, and the required payment period.
6. Why does IPMT Function return a negative value?
Excel generally returns a negative value because interest is treated as money paid by the borrower. This follows Excel’s cash-flow sign convention.
7. What is the difference between PMT and IPMT Function?
PMT calculates the complete periodic payment, including interest and principal. IPMT calculates only the interest portion.
8. What is the difference between PPMT and IPMT Function?
IPMT calculates the interest portion of a payment, whereas PPMT calculates the principal portion.
9. What happens if the type argument is 0?
Type 0 means payments are made at the end of each payment period. This is also the default setting.
10. What happens if the type argument is 1?
Type 1 means payments are made at the beginning of each payment period.
11. Can IPMT Function calculate quarterly interest?
Yes. Use the quarterly interest rate and the total number of quarterly payments.
12. Can IPMT Function calculate interest for the last EMI?
Yes. Enter the total number of payment periods in the per argument to calculate the interest component of the final period.

