Check Sheet Logo
Check Sheet

CUMIPMT

The CUMIPMT function calculates the cumulative interest paid on a loan between two periods. This function can be useful for tracking interest payments and determining the total amount of interest paid over the life of a loan.

Usage

Use the CUMIPMT formula with the syntax shown below, it has 6 required parameters:

=CUMIPMT(rate, number_of_periods, present_value, first_period, last_period, end_or_beginning)
Parameters:
  1. rate (required):
    The interest rate of the loan. This should be the same for each period.
  2. number_of_periods (required):
    The total number of payment periods over the life of the loan.
  3. present_value (required):
    The present value, or principal, of the loan.
  4. first_period (required):
    The first period for which to calculate the interest. This should be a number between 1 and the total number of periods.
  5. last_period (required):
    The last period for which to calculate the interest. This should be a number between 1 and the total number of periods.
  6. end_or_beginning (required):
    A string indicating whether payments are due at the end or beginning of each period. Use "end" for payments due at the end of the period or "beginning" for payments due at the beginning of the period.

Examples

Here are a few example use cases that explain how to use the CUMIPMT formula in Google Sheets.

Calculating total interest paid on a loan

By using the CUMIPMT function, you can easily calculate the total amount of interest paid on a loan. Simply input the rate, number of periods, and present value of the loan, along with the first and last period for which you want to calculate interest. The function will return the total interest paid between those two periods.

Tracking interest payments over time

You can use the CUMIPMT function to track interest payments over time. By inputting different values for the first and last period, you can see how the amount of interest paid changes over the life of the loan.

Comparing loans

The CUMIPMT function can be useful for comparing different loans. By inputting the same values for rate, number of periods, and present value, but different values for first and last period, you can see how much interest would be paid on each loan during the same time period.

Common Mistakes

CUMIPMT not working? Here are some common mistakes people make when using the CUMIPMT Google Sheets Formula:

Incorrect rate input

The rate input should be the interest rate per period. Make sure the rate is divided by the number of periods per year if the rate provided is an annual rate.

Incorrect number_of_periods input

The number_of_periods input should be the total number of payment periods. Make sure this value is consistent with the other inputs and the payment frequency.

Incorrect present_value input

The present_value input should be the current value of the investment or loan. Make sure this value is not negative and consistent with the other inputs.

Incorrect first_period input

The first_period input should be the number of the first payment period. Make sure this value is not negative and consistent with the other inputs and the payment frequency.

Incorrect end_or_beginning input

The end_or_beginning input should be either 0 or 1, where 0 means payments are due at the end of the period and 1 means payments are due at the beginning of the period. Make sure this value is consistent with the payment frequency and the other inputs.

Related Formulas

The following functions are similar to CUMIPMT or are often used with it in a formula:

  • PMT

    The PMT formula in Google Sheets is a financial function that calculates the periodic payment required to fully pay off a loan or investment based on a constant interest rate and a fixed number of payments. It is commonly used to determine loan payments, mortgage payments, and annuity payments.

  • IPMT

    The IPMT function calculates the interest payment for a given period of an investment or loan. It is commonly used to determine the interest portion of a loan payment. This function can be helpful when creating loan amortization schedules or when analyzing the cost of borrowing money.

  • PPMT

    The PPMT function calculates the payment on the principal of an investment or loan given the interest rate, number of periods, and present value. It is commonly used in financial analysis to calculate the principal portion of a loan or investment payment. The function returns a negative number because it represents a payment toward the principal balance, which reduces the overall balance.

Learn More

You can learn more about the CUMIPMT Google Sheets function on Google Support.