IPMT Function in Excel Tutorial

Excel Tutorial

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:

  1. Interest Component: The amount charged by the bank for lending you money.
  2. 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.

ArgumentMeaning
rateInterest rate for each payment period
perThe specific payment period for which you want to calculate interest
nperTotal number of payment periods
pvPresent value or original loan amount
fvFuture value or remaining balance after the final payment (optional)
typePayment 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:

  • 1 means the first payment period.
  • 2 means the second payment period.
  • 3 means the third payment period.
  • 12 means 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.

TypeMeaning
0Payment at the end of each period
1Payment 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:

ParticularsValue
Loan Amount₹1,00,000
Annual Interest Rate12%
Monthly Interest Rate1%
Loan Duration12 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 PeriodInterest 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

ParticularsValue
Loan Amount₹1,00,000
Annual Interest Rate12%
Monthly Interest Rate1%
Loan Duration12 Months
Payment Period1

Excel Formula

=IPMT(1%,1,12,100000)

Expected Result

-₹1,000.00

Explanation

  • 1% is the monthly interest rate.
  • 1 represents the first payment period.
  • 12 represents the total monthly payments.
  • 100000 is 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

ParticularsValue
Loan Amount₹1,00,000
Monthly Interest Rate1%
Total Periods12
Payment Period2

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

ParticularsValue
Car Loan₹3,00,000
Annual Interest Rate10.8%
Monthly Interest Rate0.9%
Loan Duration36 Months
Payment Period6

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

ParticularsValue
Business Loan₹5,00,000
Annual Interest Rate12%
Monthly Interest Rate1%
Loan Duration60 Months
Payment Period12

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

ParticularsValue
Business Loan₹2,00,000
Annual Interest Rate12%
Quarterly Interest Rate3%
Loan Duration3 Years
Total Quarterly Payments12
Payment Period1

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

ParticularsValue
Loan Amount₹1,50,000
Monthly Interest Rate1%
Loan Duration24 Months
Payment Period1
Payment TimingBeginning 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

ParticularsCellValue
Loan AmountB2₹2,00,000
Annual Interest RateB312%
Monthly Interest RateB41%
Total PeriodsB536
Selected Payment PeriodB610

Excel Formula

=IPMT(B4,B6,B5,B2)

Expected Result

-₹4,888.70 (approximately)

Explanation

The formula uses cell references instead of fixed numbers.

  • B4 contains the monthly interest rate.
  • B6 contains the selected payment period.
  • B5 contains the total number of payments.
  • B2 contains 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:

ParticularsValue
Loan Amount₹5,00,000
Annual Interest Rate12%
Monthly Interest RateCalculate manually as Annual Rate / 12
Loan Duration60 Months
Selected Payment Period1

Your Tasks

  1. Enter all loan details in Excel.
  2. Calculate the monthly interest rate.
  3. Use the IPMT function to calculate the interest component for the first month.
  4. Change the selected payment period to 12.
  5. Calculate the interest component for month 12.
  6. Change the selected payment period to 36.
  7. 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 rate argument represents the interest rate for each payment period.
  • The per argument identifies the payment period.
  • The nper argument represents the total number of payment periods.
  • The pv argument 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.