This function calculates the accrued interest paid between two specified periods for an annuity loan (a loan repaid in equal periodic installments).
Syntax:
CUMIPMT(Rate, Nper; Pv; Start_Period; End_Period; Type)
Arguments:
- Rate (required): The nominal interest rate of the loan.
- Nper (required): The total number of repayment periods.
- Pv (required): The principal loan amount.
- Start_Period (required): The first period of the calculation.
- End_Period (required): The last period of the calculation.
- Type (required): The payment timing argument, where:
- Type = 1 indicates payments are made at the beginning of each period.
- Type = 0 (default) indicates payments are made at the end of each period.
Notes:
- If fractional values are provided for integer-based arguments (e.g., Start_Period), the decimals are truncated.
- Rate, Nper, and Pv must be positive; otherwise, CUMIPMT() returns the #NUM! error.
- Start_Period must be ≥ 1.
- End_Period must be ≥ 1 and ≥ Start_Period.
- Type must be 0 or 1.
Background:
Loans can be repaid in different ways. In an annuity repayment, the borrower pays a fixed amount each period, consisting of:
- A repayment portion (increases over time).
- An interest portion (decreases as the outstanding loan balance shrinks).
While summing partial repayments is valid (to determine residual debt), summing interest payments lacks financial-mathematical significance. It is commonly used for loan comparisons (even by financial institutions), but it does not reflect a true financial analysis. Interest totals are meaningful only when evaluated at the loan’s origination. For example, a $100,000 loan requires repayment of the principal plus accrued interest if repaid later.
Example:
In the Repayment Calculation (Annuity) example from the PV() function:
- A borrower takes a $176,121.76 loan at 5.5% annual interest, repaying $1,000/month for 30 years.
- By the 19th month, the residual debt is $172,513.25, indicating $3,608.51 has been repaid. Thus, the interest paid is:
$18,000 (total paid) – $3,608.51 (principal) = $14,391.49 (interest).
This can be verified with CUMIPMT() (accounting for rounding errors):
=-CUMIPMT(5.5%/12, 30*12, 176121.76, 1, 18, 0)
(Negative result indicates cash outflow relative to the loan amount.)
Rounding Considerations:
Built-in functions may not reflect real-world bank calculations (which use 2 decimal places). For precise repayment schedules, use the ROUND() function in monthly plans.