Which function returns the interest payment for an investment loan for a given?
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. Show
Download WorkbookIPMT 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
⦿ Arguments
⦿ 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 ExcelHere, we have used the following table for demonstrating the applications of the IPMT function in Excel. 1. Using IPMT Function Without Optional Arguments for Yearly Interest PaymentFor 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. ➤Select the output cell E5 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. ➤Press ENTER Result: But you can change the format into General and then you will get the following results in which negative signs will appear. Similarly, you can get the result by inserting direct input instead of reference like below. Read More: How to Use Excel PMT Function (4 Quick Examples) 2. Using IPMT Function With Optional ArgumentsHere, we will use the optional arguments of the IPMT function for calculating the yearly interest payments. ➤Select the output cell F5 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. ➤Press ENTER Result: You can get the interest payments at the beginning of the second year also by using the following formula Read More: How to Use Excel PPMT Function (3 Suitable Examples) 3. Using IPMT Function for Semi-Annually Interest PaymentHere, we will determine the interest payment at the end of the second half of the year by using the IPMT function. ➤Select the output cell F5
➤Press ENTER Result: Read More: How to Use FV Function in Excel (4 Easy Examples) 4. Using IPMT Function for Quarterly Interest PaymentIn this section, we will get the interest payment at the end of the 3rd quarter of the first year by using the IPMT function. ➤Select the output cell E5
➤Press ENTER Result: Read More: How to Use PV Function in Excel (3 Examples) Similar Readings
5. Using IPMT Function for Monthly Interest PaymentYou can get the interest payment at the beginning of the sixth month of the first year by using the IPMT function. ➤Select the output cell F5
➤Press ENTER Result: Read More: How to Use RATE Function in Excel (3 Examples) 6. Using IPMT Function for Weekly Interest PaymentIn this section, we will determine the interest payment at the beginning of the 14th week of the first year by using the IPMT function. ➤Select the output cell F5
➤Press ENTER Result: 7. Having Interest Payment Using Table OptionFor having the interest payment for the loans at the end of the first year here we are using the Tableoption along with the IPMT function. ➤Go to Insert Tab>>Table Option Then, the Create Table Dialog Box will open up. After that, you will get the following table. ➤Select the output cell E5
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. ➤Press ENTER Result: 8. Using VBA CodeYou can use the IPMT function in the VBA code also. ➤Go to Developer Tab>>Visual Basic Option Then, the Visual Basic Editor will open up. After that, a Module will be created. ➤Write the following code
It will return the interest payments in cells F5 to F10 of Column F. ➤Press F5 Result: 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 SectionFor doing practice by yourself we have provided a Practice section like below in a sheet named Practice. Please do it by yourself. ConclusionIn 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
Which function returns the interest payment for an investment loan for a given period?The RATE Function[1] is an Excel Financial function that is used to calculate the interest rate charged on a loan or the rate of return needed to reach a specified amount on an investment over a given period.
Which function returns the interest rate for an annuity?The Excel RATE function is a financial function that returns the interest rate per period of an annuity. You can use RATE to calculate the periodic interest rate, then multiply as required to derive the annual interest rate.
What is the function that returns the number of payments in a loan?The Excel PMT function is a financial function that calculates the payment for a loan based on a constant interest rate, the number of periods and the loan amount. "PMT" stands for "payment", hence the function's name.
Which function returns the present value of an investment based on an interest rate and a constant payment schedule?PV, one of the financial functions, calculates the present value of a loan or an investment, based on a constant interest rate.
|