Syntax. CUMPRINC(Rate,Nper,Pv,Start_Period,End_Period,Type)
Definition. This function calculates the part of the repayment 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) Specifies the total number of periods in which the loan is repaid.
Pv (required) Contains the loan amount.
Start_Period (required) The first period of the calculation.
End_Period (required) The last period.
Type (required) With the (by way of exception, required) maturity argument Type, you can specify whether the payments become interest effective at the start of the periods (Type = 1) or at the end of the periods (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.
The function calculates the (compounded) part that was used with the annuity payment for loan repayment. This part, therefore, does not represent interest payments. By adding up all past periods, you can determine the residual debt of the original loan.
Note
In loan agreements, an annual interest rate is very frequently formulated as a nominal interest rate, 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.
Examples. The following examples illustrate the CUMPRINC() function.
Interest Commitment Over the Entire Duration. In the “Repayment Calculation (Annuity Repayment)” example in the description of the PV() function, shown earlier, 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. Therefore, $3,608.51 has been repaid. You can check this result with the function CUMPRINC(), but there might still be rounding errors.
=-CUMPRINC(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 entering data for a repayment plan month by month, you need to use the function ROUND() to show what is actually happening.
Fixed Interest Only at the Beginning of a Loan. Mortgage loans frequently have a fixed interest. However, the time until the interest conditions change is too short for complete repayment. In this case, the debtor can use the residual debt to determine how high his risk would be if interest rates increase, and if necessary he can compare different loan offers. If he does not want to write out a detailed repayment plan, he can determine the residual debt by subtracting the amount repaid within the fixed-interest period from the loan amount. This amount is calculated by CUMPRINC(). The amount is calculated as shown in the previous example.