Documentation/Calc Functions/CUMIPMT
TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate
Function name:
CUMIPMT
Category:
Financial Analysis
Summary:
Calculates the total or cumulative interest paid during a time interval, 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 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 and the IPMT function calculates the interest element of the corresponding payment.
CUMIPMT calculates the total interest in payments made between a specified start period and a specified end period. This is equivalent to summing the IPMT values for each period during that interval.
Syntax:
CUMIPMT(Rate; NPER; PV; S; E; Type)
Returns:
Returns a negative real number, which is the total value of the interest elements of the payments made within the specified time interval. The number returned is in the same currency unit as the specified present value, 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.
NPER is a positive real number, or a reference to a cell containing that number, which is the number of payment periods in the term.
PV is a positive real number, or a reference to a cell containing that number, which is the present value of the amount borrowed.
S is a positive integer, or a reference to a cell containing that integer, which is the first period for which the interest element of the payment is to be summed.
E is a positive integer, or a reference to a cell containing that integer, which is the last period for which the interest element of the payment is to be summed.
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, then the function assumes the end of the payment period. If Type is set to 1 or FALSE, then the function assumes the start of the payment period.
- If any argument is non-numeric, then CUMIPMT reports a #VALUE! error.
- If Rate is less than or equal to 0.0, then CUMIPMT reports an invalid argument error (Err:502).
- If PV is less than or equal to 0.0, then CUMIPMT reports an invalid argument error (Err:502).
- If either of S or E is a non-integer value, then CUMIPMT truncates it to an integer value. Note that non-integer values of NPER are not truncated.
- If after any truncation S is less than 1.0, then CUMIPMT reports an invalid argument error (Err:502).
- If after any truncation E is less than S or greater than NPER, then CUMIPMT reports an invalid argument error (Err:502).
- If Type is not equal to 0, FALSE, 1, or TRUE, then CUMIPMT reports an invalid argument error (Err:502).
Additional details:
- The purpose of CUMIPMT can be expressed in the following formula:
- [math]\displaystyle{ \text{CUMIPMT}(Rate;\:NPER;\:PV;\:S;\:E;\:Type)~=~\sum_{p=S}^{E}~\text{IPMT}(Rate;\:p;\:NPER;\:PV;\: 0;\:Type) }[/math]
Examples:
Note that the formatting of the return values given in the following examples assume the English (USA) locale.
Simple example with multiple calls to CUMIPMT
Lucas decides to borrow $20,000 over a period of 6 years at an annual interest rate of 8%. There is no cash balance remaining at the end of the loan and payments are due at the end of each year. The following table shows the interest components of his repayments.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Loan amount | $20,000 | ||||||
| 2 | Annual rate | 8% | ||||||
| 3 | ||||||||
| 4 | End Year | |||||||
| 5 | 1 | 2 | 3 | 4 | 5 | 6 | ||
| 6 | Start Year | 1 | -$1,600.00 | -$2,981.90 | -$4,128.24 | -$5,020.18 | -$5,637.38 | -$5,957.85 |
| 7 | 2 | -$1,381.90 | -$2,528.24 | -$3,420.18 | -$4,037.38 | -$4,357.85 | ||
| 8 | 3 | -$1,146.34 | -$2,038.29 | -$2,655.48 | -$2,975.95 | |||
| 9 | 4 | -$891.95 | -$1,509.14 | -$1,829.61 | ||||
| 10 | 5 | -$617.20 | -$937.66 | |||||
| 11 | 6 | -$320.47 |
Cells C6:H11 show the interest components paid during all possible time periods. The possible start years are listed in cells B6:B11, while the possible end years are listed in cells C5:H5. Cells C6:H11 contain formulas as follows:
- Cell C6 contains the formula
=CUMIPMT($B$2; $B$11; $B$1; $B6; C$5; 0), showing the interest component of the first year’s payment (the same value would be returned by the formula=IPMT($B$2; $B6; $B$11; $B$1; 0)). Cell D7 contains the formula=CUMIPMT($B$2; $B$11; $B$1; $B7; D$5; 0), showing the interest component of the second year's payment. Cells E8, F9, G10, and H11 contain corresponding formulas to calculate the interest component of each subsequent year’s payment. - Cell H6 shows the value -$5,957.85 and this is the total interest payable during the six-year term of the loan.
- Cell D6 contains the formula
=CUMIPMT($B$2; $B$11; $B$1; $B6; D$5, 0), showing the sum of the interest components of the first and second year's payments (the same value would be returned by the formula=C6+D7). Cell E6 contains the formula=CUMIPMT($B$2; $B$11; $B$1; $B6; E$5; 0), showing the sum of the interest components of the first, second and third year’s payments. Cells F6, G6, and H6 contain corresponding formulas to calculate the interest components of payments made in longer time periods that start with year 1. - Cells E7:H7 contain appropriate calls to CUMIPMT to calculate the interest components of payments made in multiple year time periods that start with year 2.
- Cells F8:H8 contain appropriate calls to CUMIPMT to calculate the interest components of payments made in multiple year time periods that start with year 3.
- Cells G9:H9 contain appropriate calls to CUMIPMT to calculate the interest components of payments made in multiple year time periods that start with year 4.
- Cell H10 contains an appropriate call to CUMIPMT to calculate the interest components of payments made in the multiple year time period that covers years 5 and 6.
- The cells colored dark grey represent cases where the start month would be greater than the end month, a condition that would result in an error from the CUMIPMT function.
Additional examples
| Formula | Description | Returns |
|---|---|---|
| =CUMIPMT(5.5%/12; 24; 5000; 4; 6; 1) | Here the function calculates the total interest element of the payments for months 4 to 6, for a loan of 5,000 currency units at an annual interest rate of 5.5% (5.5%/12 per month), with a term of 2 years (24 months). Payments are due at the beginning of each month. The interest element of the payments for months 4-6 is calculated by CUMIPMT as 57.54 currency units and this is returned as a negative value. | -$57.54 |
| =CUMIPMT(5.5%/12; 24; 5000; 4; 6; 0) | Here the function calculates the total interest element of the payments for months 4 to 6, for a loan of 5,000 currency units at an annual interest rate of 5.5% (5.5%/12 per month), with a term of 2 years (24 months). Payments are due at the end of each month. The interest element of the payments for months 4-6 is calculated by CUMIPMT as 57.80 currency units and this is returned as a negative value. | -$57.80 |
Related LibreOffice functions:
ODF standard:
Related (or similar) Excel functions:
CUMIPMT