CUMPRINC and CUMIPMT are less familiar financial functions compared to PMT, IPMT and PPMT, at least for me.

Let’s revise PMT, IPMT and PPMT first.

If you borrow 10 000 from a bank, interest is 4 % a year and your payback time is three years, then your monthly payment to the bank is 295,24. This we can calculate with PMT function.

PMT function returns a negative value, therefore I use minus with function. For me, it makes more sense to have a positive value. PMT holds three arguments: interest, number of periods and debt capital. As we calculate monthly payment, I divided interest by twelve and multiplied number of periods by twelve.

The monthly payment is divided into interest and principal.

Payment is 295,24 each month, but division between interest and principal is changing. At the start, interest is high and principal is low. At the start, relatively high part of payment is used for paying interest. Then paying the principal back to bank is correspondingly low. When time goes by, proportion of interest is decreasing, and higher part of principal will be paid back to the bank. IPMT defines the interest for each payment and PPMT the amount of principal.

CUMPRINC and CUMIPMT functions are used if you want to calculate how much either principal or interest you have paid during certain periods during payback time.

For example, during the first year, you will pay interest for 341,74 and principal 3201,14. As you see, I have simply calculated with SUM function. 341,74 plus 3201,14 is equal to 295,24 multiplied by 12.

Same value can be calculated with CUMPRINC and CUMIPMT.

CUMPRINC and CUMIPMT functions have the same arguments:

• Rate, as we calculate monthly values, the rate is C3/12.
• Number of payment periods, that is D3*12, 36 months.
• Present value, debt capital B3.
• Start period, from which period do we calculate principal or interest.
• End period, till which period do we calculate principal or interest.
• Type, is the payment at start of the month or at the end of the month. I have the payment at the end of the month, the value is 0.

CUMPRINC and CUMIPMT can be useful functions when you calculate the cumulative principle and income. However, it is useful to revise financial functions PMT, IPMT and PPMT before using CUMPRINC and CUMIPMT.

Please note that the values Excel is calculating might be slightly different than numbers from your bank. The use of calculating financial functions in Excel to have another view to loan calculations in addition to what your bank says.