CUMIPMT function
CUMIPMT adds up the interest part of a loan's payments over a range of periods and returns it as a negative number. Use it to find, for example, the interest paid in year two of a mortgage.
Example
Read it as a sentence: for a $125,000 loan at 9% a year with 30 years of monthly payments, add up the interest paid in payments 13 through 24, with payments made at the end of each period.
A
B
C
D
1
-11135.2321307508
2
3
Syntax
CUMIPMT(rate, nper, pv, start_period, end_period, type)
-
rate(required) - Interest rate per period.
-
nper(required) - Total number of payment periods.
-
pv(required) - Loan amount today.
-
start_period(required) - First period to include (1 is the first payment).
-
end_period(required) - Last period to include.
-
type(required) - 0 = payments at period end, 1 = at period start.
Returns: Total interest paid across the chosen payments, as a negative number.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=CUMIPMT(0.09/12,360,125000,1,12,0) | -11215.34288 | First year |
=CUMIPMT(0.09/12,360,125000,1,12,1) | -10201.33288 | Type 1 |
=CUMIPMT(0.01,10.5,1000,1,2,0) | -19.09199454 | Non-integer nper |
=CUMIPMT(0.01,10,1000,1.5,2.5,0) | -9.044179234 | Non-integer periods |
=CUMIPMT("0.01","10","1000","1","2","0") | -19.04417923 | Text number |
=CUMIPMT(0.01,10,1000,{1,2},2,0) | -19.04417923 | Spill |
=CUMIPMT(0.09/12,360,125000,12,1,0) | #NUM! | Start > end |
=CUMIPMT(0.09/12,360,125000,0,12,0) | #NUM! | Start 0 |
Watch out for
- Rate and nper must use the same time unit; divide an annual rate by 12 for monthly payments.
- All six arguments are required, including type.
Where it works
- Excel
- CUMIPMT is in every version of Excel from 2007 on.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Financial functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .