What is the IPMT Function?
The IPMT function is categorized under Financial functionsFunctionsThe IPMT function is categorized under Financial functions. The function calculates the interest portion based on given loan payment and payment. The function calculates the interest portion based on given loan payment and payment period. We can calculate using IPMT the principal amount of payment for the first period, last period or any period in between.
As a financial analystFinancial Analyst Job DescriptionSee a real Financial Analyst Job Description including all the skills, experience, and education required to be the successful candidate for the job. Perform financial forecasting, reporting, and operational metrics tracking, analyze financial data, create financial models, we will often be interested in knowing the principal component and interest component of a loan payment for a specific period. IPMT helps to calculate the amount.
Formula
=IPMT(rate, per, nper, pv, [fv], [type])
The IPMT function uses the following arguments:
- Rate (required argument) – It is the interest per period.
- Per (required argument) – It is the period for which we want to find the interest and must be in the range 1 to nper.
- Nper (required argument) – It is the total number of payment periods in an annuity.
- Pv (required argument) – It is the present value, or it is the lump sum amount that a series of future payments is worth as of now.
- Fv (optional argument) – It is the future value or a cash balance that we wish to attain after the last payment is made. If we omit the Fv argument, the function assumes it to be zero. The future value of a loan would be taken as zero.
- Type (optional argument) – It accepts the numbers 0 to 1 and indicates when payments are due. If omitted, it is assumed to be 0. Set type to 0 if payments are at the end of the period, and to 1 if payments are due at the start.
How to use the IPMT Function in Excel?
As a worksheet function, IPMT can be entered as part of a formula in a cell of a worksheet.
To understand the uses of the IPMT function, let us consider a few examples:
Example 1
Let us assume we are given the following data:
We will use the IPMT function to calculate the interest payments during months 1 and 2 of a $50,000 loan, which is to be paid off in full after 5 years. Interest is charged at a rate of 5% per year and the payment of the loan is to be made at the end of each month.
The formula to be used will be =IPMT( 5%/12, 1, 60, 50000).
The results are shown below:
In the example above:
- As the payments are made monthly, it was necessary to convert the annual interest rate of 5% into a monthly rate (=5%/12), and the number of periods from years to months (=5*12).
- With the forecast value = 0 and the payment is to be made at the end of the month, the [fv] and [type] arguments can be omitted from the above functions.
- The returned interest payments are negative values, as these represent outgoing payments (for the business taking out the loan), that is why we took the loan amount as negative (-B2).
Things to remember about the IPMT function
- We need to ensure that the units we use for specifying the rate and nper arguments. If we make monthly payments on a 4-year loan at 24% annual interest, we need to use 24%/12 for rate and 4*24 for nper. If you make annual payments on the same loan, use 24% for rate and 4 for nper.
- Cash paid out is shown as negative numbers. Cash received is shown as positive numbers.
- #NUM! error – Occurs if the supplied per argument is less than zero or is greater than the supplied value of nper.
- #VALUE! error – Occurs when any of the given arguments are non-numeric.
Click here to download the sample Excel file
Additional resources
Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional resources:
- Excel Functions for FinanceExcel for FinanceThis Excel for Finance guide will teach the top 10 formulas and functions you must know to be a great financial analyst in Excel. This guide has examples, screenshots and step by step instructions. In the end, download the free Excel template that includes all the finance functions covered in the tutorial
- Advanced Excel Formulas Course
- Advanced Excel Formulas You Must KnowAdvanced Excel Formulas Must KnowThese advanced Excel formulas are critical to know and will take your financial analysis skills to the next level. Advanced Excel functions you must know. Learn the top 10 Excel formulas every world-class financial analyst uses on a regular basis. These skills will improve your spreadsheet work in any career
- Excel Shortcuts for PC and MacExcel Shortcuts PC MacExcel Shortcuts - List of the most important & common MS Excel shortcuts for PC & Mac users, finance, accounting professions. Keyboard shortcuts speed up your modeling skills and save time. Learn editing, formatting, navigation, ribbon, paste special, data manipulation, formula and cell editing, and other shortucts