Related Functions:

The CUMIPMT function calculates the cumulative interest paid on a loan or investment, between two specified periods.

Syntax

=CUMIPMT(rate,nper,pv,start_period,end_period,type)

Arguments

Argument Description
rate The interest rate – per period
nper The total number of payment periods
pv The present value of the loan / investment
start_period The first period in the calculation. Payment periods are numbered beginning with 1
end_period The last period in the calculation
type The timing of the payment. The value 0 or 1 has the following meaning:

  0 the payment is made at the end of the period
  1 the payment is made at the beginning of the period

Examples

  A B C D
1 Data      
2 9% Annual interest rate    
3 30 Loan term – years    
4 125,000 Present value    
5        
6 Formula Result Notes
7 =CUMIPMT(A2/12,A3*12,A4,13,24,0) -$11,135.23 Interest paid in the second year of payments, periods 13 through 24
8 =CUMIPMT(A2/12,A3*12,A4,1,1,0) -$937.50 Interest paid in the first month payment

Note: In the example:

  • the annual interest rate of 9% had to be converted into a monthly rate (=9%/12), and
  • the number of years had to be converted into months (=30*12)

Cash Flow Convention: In line with general cash flow conventions, outgoing payments are represented by negative numbers and incoming payments are represented by positive numbers.

Common Function Error(s)

Problem What went wrong
#VALUE! Occurs if any of the supplied arguments are not recognized as numeric values
#NUM! Occurs if:

  the start_period or end_period is ≤ 0 or > nper
  the start_period > end_period
  either of the supplied rate, nper or pv arguments are ≤ 0
  the type argument is not equal to 0 or 1
Result is much higher or much lower than expected Usually occurs due to failure to convert the rate or the nper to months or quarters. Ensure the rate and nper arguments are expressed in the correct units, i.e.:

  months = 12 * years;   monthly rate = annual rate/12
  quarters = 4 * years;   quarterly rate = annual rate/4