How to Use the CUMPRINC Function in Google Sheets

Published on

In this tutorial, I will describe how to use the CUMPRINC function in Google Sheets. This function is one of the financial functions available in Sheets.

Purpose of the CUMPRINC Function

The CUMPRINC function calculates the cumulative principal paid on a loan over a specified range of periods. It’s important to note that the loan must be based on constant-amount periodic payments and a constant interest rate.

The CUM in the CUMPRINC function stands for “cumulative,” while PRINC refers to “principal.” To find the principal for a single period, use the PPMT function instead of CUMPRINC.

Similarly, to calculate the interest paid on a loan over a range of periods, use the CUMIPMT function, and for a single period, use IPMT.

Syntax and Arguments of the CUMPRINC Function

Syntax:

CUMPRINC(rate, number_of_periods, present_value, first_period, last_period, end_or_beginning)

Arguments:

Please note that all arguments in the CUMPRINC function are required; there are no optional arguments.

  • rate: The interest rate for the loan.
  • number_of_periods (Nper): The total number of payments to be made.
  • present_value (Pv): The present value of the loan or annuity.
  • first_period: The starting period of the cumulative principal calculation. Payment periods are numbered beginning with 1. For example, to calculate the cumulative principal for the period between 6 to 12, set first_period to 6.
  • last_period: The ending period of the cumulative principal calculation. In the previous example, this would be 12.
  • type: Indicates the timing of the payment, i.e., whether the payment is due at the end (0) or beginning (1).

For a 24-month loan, the first_period and last_period can be any numbers from 1 to 24, with the condition that the first_period must always be less than or equal to the last_period.

Sample Data

Make sure to use consistent units for specifying the rate and number_of_periods (Nper). For example, if you want to use the CUMPRINC formula in Google Sheets, consider the following input values:

CellDescriptionValue
A1Loan Amount (Pv)25,000.00
B2Rate (Annual Interest Rate)4.50%
B3Number_of_periods (Nper)24
B4First_period1
B5Last_period24
B6End_or_beginning0

This example represents a 24-month loan with monthly payments.

You should also adjust the number_of_periods and rate to match the payment frequency. For example:

  • For quarterly payments, divide the annual interest rate by 4 and divide the number_of_periods by 3 (since there are 3 months in a quarter).
  • For semiannual payments, divide the annual interest rate by 2 and divide the number_of_periods by 6 (since there are 6 months in a semiannual period).
  • For annual payments, use the annual interest rate as is, and divide the number_of_periods by 12 (since there are 12 months in a year).

In each case, make sure that the rate and the number_of_periods are adjusted according to the payment frequency for accurate calculations.

CUMPRINC Formula Examples in Google Sheets

To calculate the total principal for the 24 months, use the CUMPRINC function as follows:

=UMINUS(CUMPRINC(4.5%/12, 24, 25000, 1, 24, 0))

The UMINUS function is used here to change the sign of the cumulative principal from negative to positive.

Result: 25,000.00

You can also rewrite the formula using cell references for the arguments:

=UMINUS(CUMPRINC(B2/12, B3, B1, B4, B5, B6))

CUMPRINC Array Formula (Running PPMT Total in Google Sheets)

By feeding the last_period argument with sequential numbers, you can generate the cumulative principal (running PPMT total) for all periods.

The following SEQUENCE formula returns the sequential numbers from 1 to 24:

=SEQUENCE(24, 1)

Replace B5 in the earlier CUMPRINC formula with the SEQUENCE formula or the ROW formula:

=ROW(A1:A24)

Then wrap the entire CUMPRINC formula with the ARRAYFORMULA function:

ARRAYFORMULA(UMINUS(CUMPRINC(B2/12, B3, B1, B4, SEQUENCE(24, 1), B6)))

Result:

997.4452888
1998.630997
3003.571152
4012.279833
5024.771171
6041.059352
7061.158613
8085.083247
9112.847598
10144.46607
11179.9531
12219.32321
13262.59097
14309.77097
15360.8779
16415.92648
17474.93149
18537.90778
19604.87022
20675.83377
21750.81344
22829.82428
23912.88141
25000

How to Calculate Cumulative Payment of a Loan in Google Sheets

Using the CUMPRINC and CUMIPMT functions, you can calculate the cumulative payment in Google Sheets. You can express the cumulative payment as:

Cumulative Payment = CUMPRINC + CUMIPMT

Assuming the above CUMPRINC formula is in cell E2, enter the following CUMIPMT formula in cell F2:

=ARRAYFORMULA(UMINUS(CUMIPMT(B2/12, B3, B1, B4, SEQUENCE(24, 1), B6)))

Please note that in the CUMIPMT function, all the parameters are the same as those used in CUMPRINC, except for the function name.

The following formula in cell G2 calculates the cumulative payment schedule of a 24-month loan:

=ARRAYFORMULA(E2:E25 + F2:F25)

Additional Resources

You may find the tutorial on creating an Amortization Schedule in Google Sheets helpful, particularly for exploring extra principal payments. In the linked tutorial, I have shared a Google Spreadsheet containing an amortization schedule. Refer to the tab “Sheet3” for the live example of the CUMPRINC function.

That’s all for now! Thank you for staying with me—enjoy!

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Finding Most Frequent Text in Excel with Dynamic Array Formulas

Looking to identify the most frequently occurring text in Excel? You can do this...

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.