Documentation/Calc Functions/IPMT
TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate
Function name:
IPMT
Category:
Financial Analysis
Summary:
Calculates the interest element of a specific payment for a fixed-rate loan that is based on a specified number of regular payments over a defined term.
While the regular payments are always for the same amount, the proportion of interest and capital repaid at each period varies over the duration of the loan. The related PMT function calculates the fixed periodic payment (capital and interest), while the PPMT function calculates the capital element of the payment for a specified period.
IPMT can also be used to calculate the interest element of a specific deposit for a fixed-rate annuity.
Syntax:
IPMT(Rate; Period; NPER; PV [; FV[; Type]])
Returns:
Returns a real number, which is the value of the interest element of a specific payment or deposit. The returned value is negative for payments (positive value of present value argument) and positive for deposits (negative value of present value argument). The number returned is in the same currency unit as the specified present and future values, and by default is formatted in a locale-dependent currency format.
Arguments:
Rate is a real number (expressed as a percentage, such as 2.5%, or a decimal fraction, such as 0.025), or a reference to a cell containing that number, which is the periodic interest rate. It is common for interest rates to be quoted annually and, where necessary, care should be taken to convert such values to be compatible with the length of your payment period.
Period is a positive real number, or a reference to a cell containing that number, which is the specific period for which the interest element of the payment or deposit is calculated.
NPER is a real number, or a reference to a cell containing that number, which is the number of payment periods in the term.
PV is a real number, or a reference to a cell containing that number, which is the present value of the amount borrowed or invested.
FV is a real number, or a reference to a cell containing that number, which is the future value of the cash balance desired at the end of the term. If FV is omitted, the function uses the value 0.0. FV must be in the same currency units as PV.
Type is a logical value, or a reference to a cell containing a logical value, which determines whether the due date is at the beginning or end of a payment period. If Type is set to 0 or FALSE, or is omitted, then the function assumes the end of the payment period. If Type is set to any other value, then the function assumes the start of the payment period.
- If any argument is non-numeric, then IPMT reports a #VALUE! error.
- If Period is less than 1 or greater than NPER, then IPMT reports an invalid argument error (Err:502).
Additional details:
None.
Examples:
Note that the formatting of the return values given in the following examples assume the English (USA) locale.
The relationship between PMT, PPMT, and IPMT
 |
Note:
|
John decides to borrow $8,000 over a period of 12 months at an annual interest rate of 4%. There is no cash balance remaining at the end of the loan and payments are due at the end of each month. The following table shows the schedule for his repayments.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Loan amount | $8,000 | ||
| 2 | Annual rate | 4% | ||
| 3 | ||||
| 4 | Month | PMT | PPMT | IPMT |
| 5 | 1 | -$681.20 | -$654.53 | -$26.67 |
| 6 | 2 | -$681.20 | -$656.71 | -$24.48 |
| 7 | 3 | -$681.20 | -$658.90 | -$22.30 |
| 8 | 4 | -$681.20 | -$661.10 | -$20.10 |
| 9 | 5 | -$681.20 | -$663.30 | -$17.90 |
| 10 | 6 | -$681.20 | -$665.51 | -$15.68 |
| 11 | 7 | -$681.20 | -$667.73 | -$13.47 |
| 12 | 8 | -$681.20 | -$669.96 | -$11.24 |
| 13 | 9 | -$681.20 | -$672.19 | -$9.01 |
| 14 | 10 | -$681.20 | -$674.43 | -$6.77 |
| 15 | 11 | -$681.20 | -$676.68 | -$4.52 |
| 16 | 12 | -$681.20 | -$678.94 | -$2.26 |
| 17 | -$8,174.39 | -$8,000.00 | -$174.39 |
Cells B5:D16 contain formulas as follows:
- Cells B5:B16 all contain the formula
=PMT($B$2/12; $A$16; $B$1). - Cell C5 contains the formula
=PPMT($B$2/12; $A5; $A$16; $B$1); cell C6 contains the formula=PPMT($B$2/12; $A6; $A$16; $B$1); and so on for cells C7:C16. - Cell D5 contains the formula
=IPMT($B$2/12; $A5; $A$16; $B$1); cell D6 contains the formula=IPMT($B$2/12; $A6; $A$16; $B$1); and so on for cells D7:D16.
Significant features of the data in the above table include:
- PMT is used to calculate the fixed monthly payment, in this example $681.20. PMT prefixes the numbers with a minus sign to indicate that the values represent payments.
- PPMT is used to calculate the capital element of each monthly payment. Note how the magnitude of the PPMT values increases throughout the term.
- IPMT is used to calculate the interest element of each monthly payment. Note how the magnitude of the IPMT values decreases throughout the term.
- Note that for each month, the sum of the PPMT and IPMT values for that month is equal to the PMT value.
- Note also that the total of all monthly payments (cell B17) is equal to the sum of the capital elements (cell C17) and the interest elements (cell D17).
Other examples
| Formula | Description | Returns |
|---|---|---|
| =IPMT(1.99%/12; 13; 36; 25000; 5000; 1) | Here the function calculates the interest element of the payment for month 13, for a loan of 25,000 currency units at an annual interest rate of 1.99% (1.99%/12 per month), with a term of 3 years (36 months). A desired cash balance of 5000 currency units is required at term and payment is due at the beginning of each month. The interest element of the payment for month 13 calculated by IPMT is 25.16 currency units and this is returned as a negative value. | -$25.16 |
| =IPMT(1.99%/12; 13; 36; 25000) | Here the function calculates the interest element of the payment for month 13, for a loan of 25,000 currency units at an annual interest rate of 1.99% (1.99%/12 per month), with a term of 3 years (36 months). No cash balance is required at term and payment is due at the end of each month. The interest element of the payment for month 13 calculated by IPMT is 27.91 currency units and this is returned as a negative value. | -$27.91 |
Related LibreOffice functions:
ODF standard:
Related (or similar) Excel functions:
IPMT