Practice free

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.

Practice this in the app

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.

FormulaExcel showsCase
=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 .