DEV Community

Andrews
Andrews

Posted on • Originally published at geekexcel.com on

How to use the CUMPRINC function in Excel Spreadsheet?

#tousethecumprincfunc #excel #excelfunctions

CUMPRINC function in Excel returns the cumulative principal paid between two given periods. The function takes the rate, total period of the loan, and Loan amount as arguments. The principal amount is the amount of money one borrows. Here I will introduce you to some quick tricks for how to use the CUMPRINC Function In Excel Office 365. Let’s get them below!! Get an official version of MS Excel from the following link: https://www.microsoft.com/en-in/microsoft-365/excel

CUMPRINC function Syntax 

=CUMPRINC (rate, nper, pv, start_period, end_period, type)
Enter fullscreen mode Exit fullscreen mode

Syntax Explanation:

  • rate – The interest rate per period.
  • nper – The total number of payments.
  • pv – the loan amount.
  • start_period – start month for the period.
  • end_period – end month for the period.
  • type – The timing of the payment , either at the beginning or end of the period. Numbers 0 or 1 represent the payment date. The number 0 represents payment at the end of the period , and the number 1 represents payment at the beginning of the period.

Example

  • Firstly, you have to create the data with loan amount, annual interest rate , and period.

Sample data

  • Then, you need to use the formula to calculate principal paid from 1st month to 6th month. 
=CUMPRINC(B2/12,B3,B1,D3,E3,0)
Enter fullscreen mode Exit fullscreen mode

Explanation:

  • D4 : rate/12, annual rate divided by 12 for monthly payments.
  • D5 : total number of payments.
  • D3 : total loan amount.
  • F3 : start period.
  • G3 : end period.
  • 0 : payment at the end of period.

Use the formula

  • Now, the result amount is with a negative sign , and the loan amount or borrowed amount is positive , so the amount returned comes with a negative sign.
  • You need to change the start period and end period argument in the formula.
  • Finally, you have to calculate the amount varying with different intervals of time.

different interval time

Check this too: How to use the TBILLYIELD function in Excel office 365?

NOTES:

  • You have to type argument is not default in this function, throws an error “you’ve entered too few arguments for this function”, If argument is not used.
  • The formula only works with numbers only, returns #VALUE! Error if any argument is non-numeric.
  • Then, you do not use negative numbers in the argument , as the formula generate #NUM! Error.
  • Now,if you make monthly payments on a four-year loan at an annual interest rate of 12 percent , use rate/12 for monthly rate. Use rate directly 12%, if calculating for annual payments
  • If the start period < 1, end period < 1 or Start period > end period , the formula returns #NUM! Error.
  • You need to type argument other than 0 or 1 , the formula returns #NUM! error.

Verdict

From this chapter, you can get some clarification about how to use the CUMPRINC Function In Excel Office 365. Let me know if you have any doubts regarding this article or any other Excel/VBA topic.

Thank you so much for visiting Geek Excel!! *If you want to learn more helpful formulas, check out Excel Formulas *!!

Read Also:

Top comments (0)