How to use CUMIPMT Function in Excel

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

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

The CUMIPMT 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 CUMIPMT function examples and explore how to use the CUMIPMT function as a worksheet function in Microsoft Excel:

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

Result:

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

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

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 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 numerical value by Excel.