The Excel CUMIPMT Function

Related Functions:
IPMT Function

Basic Description

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

The syntax of the function is :

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

Where the arguments are as follows:

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 number of the first period over which the interest is to be calculated (must be an integer between 1 and nper)
end_period - The number of the last period over which the interest is to be calculated (must be an integer between 1 and nper)
type -

An integer (equal to 0 or 1), that defines whether the payment is made at the start or the end of the period

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

Cash Flow Convention :

Note that, in line with the general cash flow convention, outgoing payments are represented by negative numbers and incoming payments are represented by positive numbers. This is seen in the example below.

Excel Cumipmt Function Example

The following spreadsheet shows the Excel Cumipmt function used to calculate the cumulative interest paid during each year of a loan of $50,000 which is to be paid off over 5 years. Interest is charged at a rate of 5% per year and the payment to the loan is to be made at the end of each month.

The spreadsheet on the left shows the format of the functions, and the spreadsheet on the right shows the results.

Example of use of the Excel Cumipmt Function
Excel Cumipmt Function Result

Note that in this example :

Further examples of the Excel Cumipmt function are provided on the Microsoft Office website.

Cumipmt Function Errors

If you get an error from the Excel Cumipmt function, this is likely to be one of the following:

Common Errors
#NUM! - Occurs if either:
- The supplied start_period or end_period is ≤0 or >nper
- The supplied start_period > end_period
- Either of the supplied rate, nper or pv arguments are ≤ 0
- The supplied type argument is not equal to 0 or 1
#VALUE! - Occurs if any of the supplied arguments are not recognised as numeric values

Also, the following problem is encountered by some users:

Common Problem

The result from the Excel Cumipmt function is much higher or much lower than expected.

Possible Reason

Many users, when calculating monthly or quarterly payments, forget to convert the interest rate or the number of periods to months or quarters.

Solve this problem by ensuring that the rate and the 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
Return to the Excel Financial Functions Page

Return to the List of All Built-In Excel Functions

Valid XHTML 1.0 Transitional Valid CSS!
Disclaimer   Privacy Policy
Copyright © 2008-2013