Syntax. CUMIPMT(Rate,Nper,Pv,Start_Period,End_Period,Type)
Definition. This function calculates the accrued interest that is paid between two points in time when a loan is repaid as an annuity loan.
Arguments
Rate (required) The nominal interest of the loan.
Nper (required) The total number of periods in which the loan is repaid.
Pv (rquired) The loan amount.
Start_Period (required) The first period of the calculation.
End_Period (required) The last period of the calculation.
Type (required) With the (by way of exception, required) maturity argument M, you can specify whether the payments become interest effective at the start of the periods (Type = 1) or at the end of the period (Type = 0).
If the values of function arguments (which require integers) are evaluated as fractions, the decimal places are truncated.
The arguments Rate, Nper, and Pv must be positive numbers, otherwise CUMPRINC() returns the error #NUMBER!.
The other arguments must make sense as well: Start_Period must be greater than or equal to 1. End_Period must be greater than or equal to 1 and not less than Start_Period.
Type must return 0 or 1.
Background. The repayment of loans can take place in various ways. One way is for the debtor to pay the same amount each period. This amount is composed of a repayment part (which increases as time passes) and an interest part (which decreases as time passes). The change in this composition is because the loan that needs to be repaid shrinks over time. This version is called annuity repayment. The total amount is called annuity, even though its payment does not necessarily happen annually.
Although adding up partial repayments is correct (this is how the residual debt is determined), adding up the interest has no finance mathematical relevance. It is a popular method for comparing loans (even financial institutions do it), but it is not a finance mathematical comparison. Adding up interest therefore makes sense only after the interest’s evaluation at the time of the loan agreement. If a debtor takes out a loan of $100,000.00, he pays back only that amount. If he pays it back later, he has to pay interest as well.
Note
In loan agreements, an annual interest rate is very frequently formulated as a nominal interest rate (debit interest), but the payment takes place intra-annually. In such cases, you need to determine the intra-annual period interest rate by dividing the annual interest rate by the number of payments per year. The total number of periods results from the periods per year multiplied by the number of years.
Example. In the “Repayment Calculation (Annuity Repayment)” example in the description of the PV() function, the following data was specified: A debtor takes out a loan in the amount of $176,121.76, which he pays back at 5.5 percent with $1,000.00 per month over 30 years.
The repayment plan shows a residual debt of $172,513.25 in the 19th month. $18,000.00 of the loan has been paid back, and you would like to find out how much of it is interest only (in spite of the issues mentioned). The residual debt tells you that $3,608.51 has been paid; therefore, the difference of $14,391.49 must be the amount of interest. You can check this result with the function CUMIPMT(), even though there may be rounding errors.
=-CUMIPMT(5.5%/12,30*12,176121.76,1,18,0)
(The correct numbers are derived from references to the cells in which the data are located). The result is negative (it has a minus sign), because the payment flow has the opposite direction of the cash value (loan amount).
Rounding errors occur frequently when the integrated functions are used. After all, they cannot take into consideration what actually happens at the bank counter: In the real world, money is calculated with only two decimal places. If you are writing down a repayment plan month by month, you need to use the ROUND() function to show what is actually happening.