The Excel CUMIPMT function calculates the cumulative interest paid on a loan or investment, between two specified periods.
The syntax of the function is:
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).|
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.
In the following spreadsheet, the Excel Cumipmt function is 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.
Note that in this example:
Further examples of the Excel Cumipmt function are provided on the Microsoft Office website.
If you get an error from the Excel Cumipmt function, this is likely to be one of the following:
|#VALUE!||-||Occurs if any of the supplied arguments are not recognised as numeric values.|
Also, the following problem is encountered by some users:
When calculating monthly or quarterly payments, many users forget to convert annual interest rates or the number of periods to months or quarters.
|months = 12 * years|
quarters = 4 * years
|monthly rate = annual rate / 12|
quarterly rate = annual rate / 4