# Power Pivot Principles: The A to Z of DAX Functions – CUMIPMT

6 December 2022

*In our long-established Power Pivot Principles articles, we
continue our series on the A to Z of Data Analysis eXpression (DAX) functions. This week, we look at **CUMIPMT**.*

* *

*The CUMIPMT function*

The **CUMIPMT **function returns the
cumulative interest that would be paid on a loan between a start period and an end
period. It has the following syntax:

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

The six [6] arguments are as follow:

**rate**: this is required and represents the interest rate**nper**: this is also required and represents the total number of payment periods**pv**: this is required and represents the present value of the amount under finance**start_period**: this is required and represents the first period in the calculation (inclusive). Payment periods are numbered beginning with one [1] and are inclusive**end_period**: this is required and represents the last period in the calculation (inclusive)**type**:

It should be noted that:

- you need to ensure 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%, use 10%/12 for**rate**and 4*12 for**nper**. If you make annual payments on the same loan, use 10% for**rate**and four [4] for**nper** - the
**start_period**,**end_period**, and**type**will be rounded to the nearest integer

- an error will be returned if:
**rate**≤ 0

**nper**< 1**pv**≤ 0**start_period**< 1**start _period**>**end_period****end_period**>**nper****type**is any number other than 0 or 1

- the
**CUMIPMT**function is not compatible with Power Pivot and currently it is only compatible with Power BI, SSAS Tabular, Azure AS and SSDT

- this function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

Please consider the following example:

If the payments are monthly and we are interested in the interest payments from periods 13 to 24 (inclusively), we will write the following DAX query in Power BI:

**EVALUATE{**

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

**}**

This will return the cumulative interest payment of -11,052.34: 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.

*Come back next week for our next post on Power
Pivot in the **Blog** section. In the meantime, please remember we have
training in Power Pivot which you can find out more about **here**. If you wish to catch up on past articles in
the meantime, you can find all of our Past Power Pivot blogs **here**.*