Range contains specific text

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.

Formulas:   A B 1 Cumulative interest during each year of a loan of $50,000 that is to be paid off over 5 years, with an interest rate of 5% per year (payment is made at the end of each month): 2 Yr1: =CUMIPMT( 5%/12, 60, 50000, 1, 12, 0 ) 3 Yr2: =CUMIPMT( 5%/12, 60, 50000, 13, 24, 0 ) 4 Yr3: =CUMIPMT( 5%/12, 60, 50000, 25, 36, 0 ) 5 Yr4: =CUMIPMT( 5%/12, 60, 50000, 37, 48, 0 ) 6 Yr5: =CUMIPMT( 5%/12, 60, 50000, 49, 60, 0 )

Results:   A B 1 Cumulative interest during each year of a loan of $50,000 that is to be paid off over 5 years, with an interest rate of 5% per year (payment is made at the end of each month): 2 Yr1: -$2,294.98 3 Yr2: -$1,833.10 4 Yr3: -$1,347.59 5 Yr4: -$837.24 6 Yr5: -$300.79

Note that in this example:

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

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 is > nper; The supplied start_period > end_period; Any 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

When calculating monthly or quarterly payments, many users forget to convert annual interest rates 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 quarters = 4 * years

monthly rate = annual rate / 12 quarterly rate = annual rate / 4