CUMIPMT function

The CUMIPMT function in Excel calculates the cumulative interest paid on a loan or investment between two periods. This function is useful when you want to know how much total interest has been paid over a specific range of periods, such as from the start of the loan until a particular month or from one year to the next.

Syntax

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

Parameters

• rate: The interest rate for each period.
  • For example, if the annual interest rate is 6% and you are making monthly payments, the monthly interest rate would be 0.06/12.
• nper: The total number of payment periods in the loan or investment.
  • For example, if the loan is for 5 years and payments are made monthly, nper would be 5 * 12 = 60.
• pv: The present value (the principal amount of the loan or investment).
  • For example, if you are borrowing $10,000, the principal would be 10000.
• start_period: The first period in which to calculate the interest.
  • For example, if you want to start calculating from the 2nd period, you would use 2 for this parameter.
• end_period: The last period in which to calculate the interest.
  • For example, if you want to calculate the interest from the 2nd period to the 5th period, you would use 5 for this parameter.
• type: The timing of the payments:
  • 0 (default) means payments are made at the end of each period.
  • 1 means payments are made at the beginning of each period.

How It Works

The CUMIPMT function calculates the total interest paid between the start period and the end period for a loan or investment, based on the loan’s interest rate, total number of periods, and the principal amount. The result will be a negative number, as it represents a cash outflow.

Example

Let’s say you have a loan with the following details:

• Annual interest rate: 6% (monthly interest rate = 6% / 12 = 0.5%)
• Loan amount (present value): $10,000
• Total periods: 5 years (60 months)
• First period to calculate from: Period 1
• Last period to calculate to: Period 12 (i.e., the interest paid in the first year)

The formula would be:

=CUMIPMT(0.06/12, 60, 10000, 1, 12, 0)

Explanation of the Example:

• 0.06/12: The monthly interest rate (6% annual interest rate divided by 12 months).
• 60: The total number of months (5 years * 12 months).
• 10000: The principal or loan amount.
• 1: The start period (Month 1).
• 12: The end period (Month 12).
• 0: Payments are made at the end of each period.

The function will return the cumulative interest paid from Month 1 to Month 12 (the first year).

Important Notes

• The CUMIPMT function only calculates interest. If you want to calculate the total principal paid, you would use the CUMPRINC function.
• The result is usually a negative number, since it represents an outgoing payment. If you prefer a positive result, you can multiply the result by -1.
• Payments are assumed to be made in equal installments throughout the periods unless otherwise specified.
• The type argument specifies whether payments are made at the beginning or the end of each period. Typically, mortgages or loans assume payments are made at the end of each period, which is why type is often set to 0.

Summary

The CUMIPMT function calculates the cumulative interest paid on a loan or investment between two specified periods. It is useful for understanding how much interest has been paid up to a certain point in time. The result will be negative, representing the interest paid out.