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

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 CUMPRINC.  

The CUMPRINC function

The CUMPRINC function returns the cumulative principal paid on a loan between a start period and an end period.  The CUMPRINC function has the following syntax:

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: this is required and represents the timing of the payment where one [1] represents payment at the beginning of the period and zero [0] represents payment at the end of the period.

Similar to the CUMIPMT function, 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
  
  
• the CUMPRINC 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 principal payments from period 13 to 24 (inclusively), we will write the following DAX query:    

This will return the cumulative principal payment of -927.15: the total principal paid in the second year of payments, periods 13 through 24, assuming 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.