The syntax for the CUMPRINC function is as follows:
CUMPRINC(rate, nper, pv, start_period, end_period, type)
* `rate`: The interest rate per period.
* `nper`: The total number of payment periods in the loan.
* `pv`: The present value, or the amount of the loan.
* `start_period`: The first payment period for which to calculate the cumulative principal.
* `end_period`: The last payment period for which to calculate the cumulative principal.
* `type`: The type of loan payment. 0 for payments at the end of each period, 1 for payments at the beginning of each period.
Suppose we have a loan of $10,000 with an interest rate of 5% per year. The loan is to be paid back over five years with monthly payments of $188.71. We want to calculate the amount of principal that has been paid back at the end of each year.
To do this, we would use the following formula:
=CUMPRINC(5%/12, 5*12, $10,000, 1, 12, 0)
=CUMPRINC(5%/12, 5*12, $10,000, 13, 24, 0)
=CUMPRINC(5%/12, 5*12, $10,000, 25, 36, 0)
=CUMPRINC(5%/12, 5*12, $10,000, 37, 48, 0)
=CUMPRINC(5%/12, 5*12, $10,000, 49, 60, 0)
The first formula calculates the cumulative principal paid at the end of the first year. The second formula calculates the cumulative principal paid at the end of the second year, and so on.
The CUMPRINC function is a useful tool for calculating the cumulative principal payments on a loan. It can be used to determine the amount of principal that has been paid back at any point during the life of the loan. By understanding the syntax and usage of this function, you can easily incorporate it into your Power BI reports for more accurate and useful loan analysis.