What is the CUMIPMT function?

The CUMIPMT function calculates the accumulated interest based on a start and end period on a loan.

What is accumulated interest?

Accumulated interest refers to the total amount of interest earned on an investment or paid on a loan up to a specified date. This process of interest earning interest is called compounding.

What is a loan?

A loan is an amount of money you borrow from an individual or entity. The lender is usually a corporation, financial institution, or government, however it can also be an individual. A lender is an entity or individual that makes funds available for a borrower that can be a person or a business. The borrower agrees to repay the loan amount plus interest over an agreed-upon period of time.

What are the start and end period on a loan?

The start period or start date is the date the loan begins and interest accrual starts. The end period or maturity date is the final due date when the loan must be fully repaid.

The CUMIPMT function lets you calculate the accumulated interest based on a start and end date, however, these dates must not necessarily be the start and end date of the loan.

For example, a loan is to be repaid after 5 years. The CUMIPMT function lets you calculate the accumulated interest between year 3 and 4.

CUMIPMT function Syntax

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

CUMIPMT function Arguments

rate	Required.  The interest rate.
nper	Required.  The total number of periods in an annuity. Examples: A monthly payment - 12 Quarterly payment - 4
pv	Required.  Present value.
start_period	Required.  The first period.
end_period	Required. The last period.
type	Required.  When payments are due. 0 - End of period. 1 - At the beginning of the period.

What is an annuity?

An annuity is a series of equal payments made over a defined period of time. The payments are usually made monthly, quarterly, or annually. These payment intervals are the "periods" that nper counts.

For example, if an annuity pays out quarterly for 3 years, there would be:

4 payments per year
3 years
So nper = 4 * 3 = 12 total payment periods

What is interest rate?

A fixed interest rate is an interest rate that remains the same throughout the term of a loan or an investment.

What is a monthly payment?

A monthly payment is a scheduled payment that occurs every month as part of an ongoing loan, mortgage, or other finance agreement.

What is a quarterly payment?

A quarterly payment is a scheduled payment that occurs every quarter as part of an ongoing loan, mortgage, or other finance agreement.

CUMIPMT function example

The image above shows the CUMIPMT function in cell E3 calculating the accumulated interest for month 25 to 36 for a 10 year loan of 100 000.

Formula in cell E3:

The first argument is the interest rate per year, we calculate the interest on a monthly basis so the argument has to be divide with 12, there are 12 months in a year.

The second argument is the number of periods the interest is calculated for, 10 year * 12 = 120 months or periods.

CUMIPMT function not working

The CUMIPMT function returns

• #NUM error value if:
  • rate <= 0
  • nper <= 0
  • pv <= 0
  • start_period < 1
  • end_period < 1
  • start_period > end_period
  • type is not 0 (zero) or 1

Use the same unit for rate and nper, the above example uses monthly payments. That is why the interest rate is divided by 12 and nper is multiplied by 12. There are 120 monthly payments in a 10-year period.

This image shows the interest for months 25 to 36 and the cumulative interest. The amount in cell G22 is the same as in the top image cell E3.

Functions in 'Financial' category

The CUMIPMT function function is one of many functions in the 'Financial' category.

Excel function categories

Excel categories

Leave a Comment