CUMIPMT
Add up the interest portion of a loan's payments between two periods, such as one year of mortgage interest.
Syntax
CUMIPMT(rate, nper, pv, start_period, end_period, type)| Argument | Required | Description |
|---|---|---|
rate | Required | The interest rate per period. |
nper | Required | The total number of payments. |
pv | Required | The amount borrowed, as a positive number. |
start_period | Required | The first payment to include, counting from 1. |
end_period | Required | The last payment to include. |
type | Required | 0 for payments at the end of each period, 1 for the start. Required. |
Examples
Every example below was executed against VisiGrid engine 0.54.0 — not
transcribed from another vendor's documentation. Reproduce any of them with vgrid calc.
| Formula | Result | Notes |
|---|---|---|
=CUMIPMT(0.05/12,360,200000,1,12,0) | -9932.99 | Interest paid in the first year of a 200,000 mortgage at 5%. |
=CUMIPMT(0.05/12,360,200000,13,24,0) | -9782.02 | The second year, slightly less, because the balance is lower. |
=CUMIPMT(0.05/12,360,200000,1,360,0) | -186511.57 | All the interest over the life of the loan. |
=CUMIPMT(0.05/12,360,-200000,1,12,0) | #NUM! | pv must be positive here, unlike PMT. |
A year of interest
The everyday use is the interest figure on a mortgage statement or a tax return. For the calendar year of payments 13 to 24:
=CUMIPMT(0.05/12,360,200000,13,24,0) → -9782.02It is the same as summing IPMT over those twelve periods,
without the helper column.
Stricter than PMT
Two rules catch people out. type is required, not optional, so a five-argument
call is an error. And pv must be positive: entering the loan as a negative
number, which PMT accepts happily, returns #NUM! here.
Both match Excel.
Excel compatibility
Matches Excel, including the required type argument and #NUM! for a negative pv or a start after the end.