Related Functions:

The CUMPRINC function calculates the cumulative payment on the principal of a loan or investment, between two specified periods.

Syntax

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

Arguments

Argument Description
rate The interest rate – per period
nper The number of periods over which the loan or investment is to be paid
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 =CUMPRINC(A2/12,A3*12,A4,13,24,0) -$934.1071 Principal paid in the second year of payments, periods 13 through 24
8 =COMPRINC(A2/12,A3*12,A4,1,1,0) -$68.27827 Principal paid in the first month payment

Note: In the above example, the payments are made monthly, so

  • 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, i.e.:

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