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:

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:

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.

• Amortization Schedule in Google Sheets and Extra Principal Payments.

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