Financial Functions CUMPRINC and CUMIPMT in Excel
If you wish to calculate the total interest or the total principal paid towards the loan between two periods, then you can use CUMIPMT and CUMPRINC functions respectively. The syntax of CUMIPMT is CUMIPMT (rate, nper, pv, start_period, end_period, type) and the syntax of CUMPRINC is CUMPRINC (rate, nper, pv, start_period, end_period, type). Here, rate is the interest rate per period on the loan. The nper is the number of payments made and pv is present value or the loan amount. As these functions calculate cumulative value, the start_period and end_period specifies the first period and last period in the calculation. Suppose, you wish to calculate the total principal paid towards the loan during first 5 months of a 2 year loan, then start_period would be 1 and end_period would be 5. The type is a logical value to identify whether the payment is due at the beginning of the period (type =1) or at the end of the period (type =0).
To illustrate the concept, imagine that you borrow $20,000 for one year period with an annual interest rate of 24%. You plan to pay back the loan amount in monthly instalments. If you calculate the principal and interest paid towards the loan each month using PPMT and IPMT functions, you will get an Excel sheet as follows. Let’s use this as a reference to understand the functions CUMIPMT and CUMPRINC better.
Suppose you wish to calculate the principal paid towards the loan from 3rd to 10th month (both months inclusive). Enter the formula =-CUMPRINC(B3/12,B2,B1,3,10,0) in any of the cells which is formatted to $ currency. The minus sign is not part of the formula: it is to display the principal amount value in normal format without minus sign. Here, cell B3 contains the rate value, but it is to be divided by 12 as you plan to make monthly payments. Here, start_period and end_period values are 3 and 10 respectively. After entering the formula, you will get the result $13,315.93. This means that from month 3 to month 10, you pay $13,315.93 as payment on principal. Now, if you add the monthly principal payment values for the same period, you should get the same result. To double check, enter the formula =SUM(E8:E15) in any cell (preferably in the adjacent cell which contains CUMPRINC formula). Here, E8 contains the principal paid in the 3rd month and E15 contains principal paid in the 10th month. You could see that you get the same result when you add these values.
Again, if you wish to calculate the interest paid towards the loan from 5th to 11th month, then enter the formula =-CUMIPMT(B3/12,B2,B1,5,11,0) in any of the cells. Here, start_period and end_period values are 5 and 11 respectively. After entering the formula, you will get the result $1238.56. This means that from month 5 to month 11, you pay $1238.56 as interest towards the loan. Now, if you add the monthly interest payment values for the same period, you should get the same result. To double check, enter the formula =SUM(F10:F16) in any cell (preferably in the adjacent cell which contains CUMIPMT formula). Here, F10 contains the interest paid in the 5th month and F16 contains interest paid in the 11th month. You could see that you get the same result when you add these values. Make sure that you format all cells containing amounts to accommodate $ currency to get the accurate result.
Read More about Excel here