CUMPRINC Function in Excel - How to use CUMPRINC Function in Excel

How to use CUMPRINC Function in Excel

The Excel CUMPRINC function is a financial function that returns the cumulative principal paid on a loan between a start period and an end period. You can use CUMPRINC to calculate and verify the total principal paid on a loan, or the principal paid between any two payment periods.

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

The CUMPRINC function syntax has the following arguments:

  • Rate    Required. The interest rate.
  • Nper    Required. The total number of payment periods.
  • Pv    Required. The present value.
  • Start_period    Required. The first period in the calculation. Payment periods are numbered beginning with 1.
  • End_period    Required. The last period in the calculation.
  • Type    Required. The timing of the payment.
Type Timing
0 (zero) Payment at the end of the period
1 Payment at the beginning of the period

Example: Let’s look at some Excel CUMPRINC function examples and explore how to use the CUMPRINC function as a worksheet function in Microsoft Excel:

CUMPRINC Function - How to use CUMPRINC Function in Excel

Syntax:  =CUMPRINC(B1/12,B2*12,B3,1,1,0)

Result:

CUMPRINC Function in Excel - How to use CUMPRINC Function in Excel

  • The payments are made monthly, so we have converted the annual interest rate of 0.08 into a monthly rate (=0.08/12), and the number of years into months (=40*12).
  • The calculated payments are negative values, as they represents outgoing payments (for the individual taking out the loan).

Based on the Excel spreadsheet above, the following CUMPRINC examples would return:

Syntax: =CUMPRINC(B1/12,B2*12,B3,1,12,0)
Result: -713.2568371

Syntax: =CUMPRINC(B1/12,B2*12,B3,13,24,0)
Result: -772.4568028

Syntax: =CUMPRINC(B1/12,B2*12,B3,25,36,0)
Result: -836.5703365

Syntax: =CUMPRINC(B1/12,B2*12,B3,37,48,0)
Result: -906.0052619

Syntax: =CUMPRINC(B1/12,B2*12,B3,49,60,0)
Result: -981.2032518

Note:

  1. #NUM! error occurs in the following scenarios:
    1. When the given start_period or end_period is less than or equal to zero.
    2. When the given start_period is greater than the end_period.
    3. When any of the given arguments – rate, nper, or PV – is less than or equal to zero.
    4. The argument type is not equal to 0 or 1.
  2. #VALUE! error – Occurs when any of the given arguments is a non-numeric value or is not recognized as a numeric value by Excel.

Leave a Reply

Your email address will not be published. Required fields are marked *