How to Use IPMT Function in Excel (8 Examples)

The IPMT function in Excel is used to find the interest payment of a loan amount for a certain period. In this article, you will get to know the introduction and usage of the IPMT function in Excel.

Excel IPMT Function

Download Workbook


IPMT Function: Syntax & Arguments

⦿ Function Objective

The IPMT function returns the interest payment for a given period for an investment, based on periodic, constant payments and a constant interest rate.

⦿ Syntax

IPMT(rate, per, nper, pv, [fv], [type])

Excel IPMT Function

⦿ Arguments

Argument Required/Optional Explanation
rate Required The constant interest rate for a period on the loan/investment
per Required The period for which you want to calculate the interest payment
nper Required The total number of payments during the whole period of the loan/investment
pv Required The present value of the loan/investment
[fv] Optional The future value of the loan/investment; if it is not used then zero will be taken automatically for this argument
[type] Optional It defines the due time of the payment.
  • 0 is for the payments at the end of each period.
  • 1 is for the payments at the beginning of each period.

When it is not used then zero will be taken automatically for this argument

⦿ Return Value

It returns a numeric value which is the interest payment for the loan for a certain period.

⦿ Version

The IPMT function has been introduced in Excel 2007 version and available for all versions after that.


8 Examples of Using The IPMT Function in Excel

Here, we have used the following table for demonstrating the applications of the IPMT function in Excel.
For creating the article, we have used Microsoft Excel 365 version, you can use any other versions according to your convenience.

Excel IPMT Function


1. Using IPMT Function Without Optional Arguments for Yearly Interest Payment

For having the interest payment for the loans at the end of the first year you can use the IPMT function and here we are using this function without optional arguments.

Excel IPMT Function

➤Select the output cell E5
➤Type the following formula

=IPMT(C5,1,D5,B5)

Here, C5 is the annual rate of interest, 1 is the period for which you want to calculate the interest payment ( as we want to know the interest payment for the first year so we have used 1), D5 is the total number of payments during the whole period and B5 is the present value of the loan.

As we have used no future value and type arguments so it will consider future value as zero and also type as zero which means payment will be made at the end of each period.

without optional argument

➤Press ENTER
➤Drag down the Fill Handle Tool

without optional argument

Result:
As you are paying out these interest payments you will get the values as negative value because of the cash flow sign convention.
By default, the format of the results will be Currency and you will get the values in red color and enclosed with parentheses ( Currency format for negative numbers).

without optional argument

But you can change the format into General and then you will get the following results in which negative signs will appear.

Excel IPMT Function

Similarly, you can get the result by inserting direct input instead of reference like below.

=IPMT(0.05,1,D7,1000)

without optional argument

Read More: How to Use Excel PMT Function (4 Quick Examples)


2. Using IPMT Function With Optional Arguments

Here, we will use the optional arguments of the IPMT function for calculating the yearly interest payments.

Excel IPMT Function

➤Select the output cell F5
➤Type the following formula

=IPMT(D5,2,E5,B5,C5,0)

Here, D5 is the annual rate of interest, 2 is the period for which you want to calculate the interest payment (as we want to know the interest payment for the second year so we have used 2), E5 is the total number of payments during the whole period and B5 is the present value of the loan.
The future value is C5 and 0 is for the payment at the end of the period.

with optional argument

➤Press ENTER
➤Drag down the Fill Handle Tool

with optional argument

Result:
In this way, you will get the interest payments for the end of the second year.

with optional argument

You can get the interest payments at the beginning of the second year also by using the following formula

=IPMT(D5,2,E5,B5,C5,1)

Excel IPMT Function

Read More: How to Use Excel PPMT Function (3 Suitable Examples)


3. Using IPMT Function for Semi-Annually Interest Payment

Here, we will determine the interest payment at the end of the second half of the year by using the IPMT function.

Excel IPMT Function

➤Select the output cell F5
➤Type the following formula

=IPMT(D5/2,2,E5*2,B5)
  • D5/2→ D5 is the annual rate of interest and it divided by 2 for semi-annual payment
    Output→0.025
  • 2 →The period for which you want to calculate the interest payment (as we want to know the interest payment for the second half of the first year so we have used 2),
  • E5*2→It is the total number of payments during the whole period (here, the total period is E5 or 7 and for semi-annual payment it is multiplied by 2)
    Output→14
  • B5 is the present value of the loan.

semi-annually interest payment

➤Press ENTER
➤Drag down the Fill Handle Tool

semi-annually interest payment

Result:
Afterward, you will get the interest payments for the end of the second half of the first year.

Excel IPMT Function

Read More: How to Use FV Function in Excel (4 Easy Examples)


4. Using IPMT Function for Quarterly Interest Payment

In this section, we will get the interest payment at the end of the 3rd quarter of the first year by using the IPMT function.

Excel IPMT Function

➤Select the output cell E5
➤Type the following formula

=IPMT(C5/4,3,D5*4,B5)
  • C5/4→ C5 is the annual rate of interest and it divided by 4 for quarterly payment
    Output→0.0125
  • 3 →The period for which you want to calculate the interest payment (as we want to know the interest payment for the third quarter of the first year so we have used 3),
  • D5*4→It is the total number of payments during the whole period (here, the total period is D5 or 7 and for quarterly payment it is multiplied by 4)
    Output→28
  • B5 is the present value of the loan.

quarterly interest payment

➤Press ENTER
➤Drag down the Fill Handle Tool

quarterly interest payment

Result:
Then, you will get the interest payments for the end of the third quarter of the first year.

Excel IPMT Function

Read More: How to Use PV Function in Excel (3 Examples)


Similar Readings


5. Using IPMT Function for Monthly Interest Payment

You can get the interest payment at the beginning of the sixth month of the first year by using the IPMT function.

Excel IPMT Function

➤Select the output cell F5
➤Type the following formula

=IPMT(D5/12,6,E5*12,B5,C5,1)
  • D5/12→ D5 is the annual rate of interest and it is divided by 12 (as a year has 12 months) for monthly payment
    Output→0.004167
  • 6 →The period for which you want to calculate the interest payment (as we want to know the interest payment for the sixth month of the first year so we have used 6),
  • E5*12→It is the total number of payments during the whole period (here, the total period is E5 or 7 and for monthly payment it is multiplied by 12)
    Output→84
  • B5→ Present value of the loan
  • C5→ Future value of the loan
  • 1→ Payment is made at the beginning of each month

monthly interest payment

➤Press ENTER
➤Drag down the Fill Handle Tool

monthly interest payment

Result:
Afterward, you will get the interest payments for the beginning of the sixth month of the first year.

monthly interest payment

Read More: How to Use RATE Function in Excel (3 Examples)


6. Using IPMT Function for Weekly Interest Payment

In this section, we will determine the interest payment at the beginning of the 14th week of the first year by using the IPMT function.

Excel IPMT Function

➤Select the output cell F5
➤Type the following formula

=IPMT(D5/52,14,E5*52,B5,C5,1)
  • D5/52→ D5 is the annual rate of interest and it is divided by 52 (as a year has nearly 52 weeks) for weekly payment
    Output→0.000962
  • 14 →The period for which you want to calculate the interest payment (as we want to know the interest payment for the 14th week of the first year so we have used 14),
  • E5*52→It is the total number of payments during the whole period (here, the total period is E5 or 7 and for weekly payment it is multiplied by 52)
    Output→364
  • B5→ Present value of the loan
  • C5→ Future value of the loan
  • 1→ Payment is made at the beginning of each week

weekly interest payment

➤Press ENTER
➤Drag down the Fill Handle Tool

weekly interest payment

Result:
Then, you will get the interest payments for the beginning of the 14th week of the first year.

Excel IPMT Function


7. Having Interest Payment Using Table Option

For having the interest payment for the loans at the end of the first year here we are using the Table option along with the IPMT function.

Excel IPMT Function

➤Go to Insert Tab>>Table Option

table

Then, the Create Table Dialog Box will open up.
➤Select the data range
➤Click the My table has headers Option
➤Press OK

table

After that, you will get the following table.

table

➤Select the output cell E5
➤Type the following formula

=IPMT([@[Rate of Interest (Annual)]],1,[@[Total Payment (Annual)]],[@[Loan (PV)]])

Here, [Rate of Interest (Annual)] is the annual rate of interest, 1 is the period for which you want to calculate the interest payment (as we want to know the interest payment for the first year so we have used 1), [Total Payment (Annual)] is the total number of payments during the whole period and [Loan (PV)] is the present value of the loan.

table

➤Press ENTER

Result:
In this way, you will get the interest payments at the end of the first year.

Excel IPMT Function


8. Using VBA Code

You can use the IPMT function in the VBA code also. 

Excel IPMT Function

➤Go to Developer Tab>>Visual Basic Option

VBA

Then, the Visual Basic Editor will open up.
➤Go to Insert Tab>> Module Option

VBA

After that, a Module will be created.

VBA

➤Write the following code

Sub interestpayment()

Range("F5") = Application.WorksheetFunction.IPmt((Range("D5")), 1, _
(Range("E5")), (Range("B5")), (Range("C5")), 0)

Range("F6") = Application.WorksheetFunction.IPmt((Range("D6")), 1, _
(Range("E6")), (Range("B6")), (Range("C6")), 0)

Range("F7") = Application.WorksheetFunction.IPmt((Range("D7")), 1, _
(Range("E7")), (Range("B7")), (Range("C7")), 0)

Range("F8") = Application.WorksheetFunction.IPmt((Range("D8")), 1, _
(Range("E8")), (Range("B8")), (Range("C8")), 0)

Range("F9") = Application.WorksheetFunction.IPmt((Range("D9")), 1, _
(Range("E9")), (Range("B9")), (Range("C9")), 0)

Range("F10") = Application.WorksheetFunction.IPmt((Range("D10")), 1, _
(Range("E10")), (Range("B10")), (Range("C10")), 0)

End Sub

It will return the interest payments in cells F5 to F10 of Column F.

VBA

➤Press F5

Result:
After that, you will get the interest payments at the end of the first year.

Excel IPMT Function


Things to Notice

🔺You have to be careful about specifying the arguments rate and nper for calculating interest payments for different types of periods like yearly, quarterly, monthly, etc.

🔺For any non-numeric arguments you will get #VALUE! Error.

🔺The results will be negative for loans (cash paid out) and positive for investments (cash received)

🔺When per is negative or greater than nper you will get #NUM! Error.


Practice Section

For doing practice by yourself we have provided a Practice section like below in a sheet named Practice. Please do it by yourself.

practice


Conclusion

In this article, we tried to cover the introduction and usage of the IPMT function in Excel. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.


Related Articles

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo