CUMIPMT - Daxplained

CUMIPMT is a DAX function that returns the cumulative interest paid on a loan between a start period and an end period.

Syntax

CUMIPMT(
   Rate,
   Nper,
   Pv,
   Start_period,
   End_period,
   Type
)

Argument	Properties	Description
Rate		The interest rate.
Nper		The total number of payment periods.
Pv		The present value.
Start_period		The first period in the calculation. Payment periods are numbered beginning with 1.
End_period		The last period in the calculation.
Type		The timing of the payment.

Return Values

The cumulative interest paid in the specified period.

Remarks

Make sure that you are consistent about the units you use for specifying rate and nper. If you make monthly payments on a four-year loan at an annual interest rate of 10 percent, use 0.1/12 for rate and 4*12 for nper. If you make annual payments on the same loan, use 0.1 for rate and 4 for nper.
start_period, end_period, and type are rounded to the nearest integer.
An error is returned if:
- rate ≤ 0.
- nper < 1.
- pv ≤ 0.
- start_period < 1 or start_period > end_period.
- end_period < start_period or end_period > nper.
- type is any number other than 0 or 1.
This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

Examples

Data	Description
9%	Annual interest rate
30	Years of the loan
125000	Present value

Example 1

The following DAX query:

EVALUATE
{
  CUMIPMT(0.09/12, 30*12, 125000, 13, 24, 1)
}

Returns the total interest paid in the second year of payments, periods 13 through 24, assuming that the payments are made at the beginning of each month.

[Value]
-11052.3395838718

Example 2

The following DAX query:

EVALUATE
{
  CUMIPMT(0.09/12, 30*12, 125000, 1, 1, 0)
}

Returns the interest paid in a single payment in the first month, assuming that the payment is made at the end of the month.

[Value]
-937.5

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/dax/cumipmt-function-dax