AI Assistant
Help Center AI Assistant is now available
Got questions about Lark? Use our AI chat to find the answers.
00:00
Click and hold to drag
Got It
Try Now

CUMPRINC function for Sheets

2 min read
I. Intro
The CUMPRINC function is commonly used to calculate the cumulative principal paid on a loan, based on fixed periodic payments and interest rates.
II. About the function
  • Formula: =CUMPRINC(rate, number_of_periods, present_value, first_period, last_period, end_or_beginning)
  • Parameters:
  • rate (required): The interest rate for the loan.
  • number_of_periods (required): The number of payments to be paid.
  • present_value: The current value of the loan.
  • first_period: The first period of the calculation.
  • last_period: The last period of the calculation.
  • end_or_beginning: Determines whether payments are due at the end or beginning of each period (0 for end, 1 for beginning)
  • Example: =CUMPRINC(12%,12,100,1,5,0)
  • Note:
  • Please ensure that the units for the interest rate and total payment periods are consistent. For a four-year loan with an interest rate of 12% that is paid monthly, the rate should be 12%/12, and number_of_periods should be 4*12. For the same loan that is paid annually, the rate should be 12%, and the number_of_periods should be 4.
  • If the rate, number_of_periods, or present_value is less than or equal to 0, then #NUM! is returned.
  • If the first_period or last_period is less than 1, or the first_period is larger than the last_period, then #NUM! is returned.
  • If the end_or_beginning is any value other than 0 or 1, then #NUM! is returned.
III. Steps
Use the CUMPRINC function
  1. Select a cell.
  1. Enter the formula and parameters in the cell. For example: =CUMPRINC(B2,B3,B4,B5,B6,0).
  1. Press Enter to display the result, which is -26.32417137 in this example.
250px|700px|reset
Delete the CUMPRINC function
Select the cell with the CUMPRINC function, and press Delete.
IV. Use case
Finance: Calculate the amount of a loan to be paid
Calculations for loans can get quite complicated, involving payment periods, interest rates, present value, and more. The CUMPRINC function makes it easy to determine the amount of principal paid by entering the necessary parameters.
  • Formula used below =CUMPRINC(D2,D3,D4,D3,D4,D5,D6,0)
  • About the parameters: The cumulative principal paid on the loan is calculated based on the conditions in the figure below.
250px|700px|reset
Written by: Lark Help Center
Updated on 2022-10-08
How satisfied are you with this content?
Thank you for your feedback!
Need more help? Please contact Support.
rangeDom