CUMIPMT Function Examples – Excel, VBA, & Google Sheets

This tutorial demonstrates how to use the Excel CUMIPMT Function in Excel to calculate the cumulative interest payment of a loan or an investment.

CUMIPMT Function Overview

The CUMIPMT Function Calculates the cumulative interest payment.

To use the CUMIPMT Excel Worksheet Function, select a cell and type:

 

 

(Notice how the formula inputs appear)

CUMIPMT Function Syntax and Inputs:

=CUMIPMT(rate,nper,pv,start_period,end_period,type)

rate – The interest rate for each period.

nper – The total number of payment periods.

pv – The present value of the investment that the future payments are worth now.

start_period – It’s the first period over which the interest has to be calculated. Its value must be between 1 and nper.

end_period – It’s the end period over which the interest has to be calculated. Its value should also be an integer and must be between 1 and nper, but greater than the value of start_period.

type –  The type argument shows when the payments are made, either at the end of the period by 0 or at the start of the period by 1. Its default value is 0.

What is the Excel CUMIPMT Function?

The Excel CUMIPMT function returns the cumulative interest paid (cumulative interest payment) on an investment or loan between two specific periods.

Calculate Cumulative Interest Payment of a loan on its 1st year

Let’s calculate the cumulative interest paid on the loan of $100,000, during its first year. The bank charge an annual interest rate of 6.5% and the installments for the loan are to be paid at the end of each month. The loan has to be fully paid off in the next 10 years.

As the payments are made monthly, the annual interest rate is converted into monthly interest by

Monthly Interest Rate – 6.50% (annual interest rate) / 12 (months per year) = 0.54%

and the number of payments per period is converted into the monthly number of payments by

NPER – 10 (years) * 12 (months per year) = 120

The cumulative interest paid on the loan needs to be calculated during its first year. So, the start period for the calculation is going to be

Start_period = 1

And the end period of the first year will be

End_period = 12

The payments are made at the end of the month, so the value of [type] argument is

Type = 0

The formula used for the calculation of cumulative interest payment is:

=CUMIPMT(D7,D8,D9,D10,D11,D12)

The cumulative interest paid during the first year of the loan is

CUMIMPT = -$6,283.83

The Excel CUMIMPT returns the value in a negative sign because it represents outgoing payments.

Calculate Cumulative Interest Payment of a loan on its 2nd year

Consider we took a loan from a bank with the same values as the example 1. But, here we are going to calculate the cumulative interest paid during the second year of the loan.

All the argument values are the same as the first example, except the start_period and end_period values.

As the payments are made monthly, the annual interest rate is converted into monthly interest by

Monthly Interest Rate – 6.50% (annual interest rate) / 12 (months per year) = 0.54%

and the number of payments per period is converted into the monthly number of payments by

NPER – 10 (years) * 12 (months per year) = 120

The cumulative interest paid on the loan needs to be calculated during its second year. So, the start period for the calculation is going to be

Start_period = 13

And the end period of the first year will be

End_period = 24

The payments are made at the end of the month, so the value of [type] argument is

Type = 0

The formula used for the calculation of cumulative interest payment is:

=CUMIPMT(D7,D8,D9,D10,D11,D12)

The cumulative interest paid during the second year of the loan is

CUMIMPT = -$5,792.13

Additional Notes

Make sure the units of nper and rate are consistent, e.g., in case of monthly interest rate, the number of periods of the investment should also be in months.
In the Financial Functions the cash outflows, such as deposits or other outgoing payments, are represented by negative numbers and the cash inflows, such as dividends, are represented by positive numbers.

#NUM! Error occurs if rate ≤ 0, nper ≤ 0, pv ≤ 0, start_period < 1, end_period < 1, or start_period > end_period.

#VALUE! Error occurs if any of the argument value is non-numeric.

Return to the List of all Functions in Excel

 

CUMIPMT in Google Sheets

All of the above examples work exactly the same in Google Sheets as in Excel.

CUMIPMT Examples in VBA

You can also use the CUMIPMT function in VBA. Type:

application.worksheetfunction.cumipmt(rate,nper,startperiod,endperiod,type)

For the function arguments (rate, etc.), you can either enter them directly into the function or define variables to use instead.