How to Use the CUMIPMT and CUMPRINC Functions in Google Sheets to Calculate Cumulative Loan Payments

When modeling amortising mortgages, commercial debt facilities, or capital leases, financial analysts need to understand not only individual monthly payments, but the cumulative interest and principal paid over specific intervals (such as during a fiscal year or before a loan refinancing window). Calculating these totals by manually summing amortization tables is cumbersome and error-prone.

Google Sheets includes two dedicated financial functions engineered specifically for cumulative loan modeling: CUMIPMT (Cumulative Interest Payment) and CUMPRINC (Cumulative Principal Payment). This guide details their mathematical architecture, syntax rules, payment timing options, and spreadsheet implementations.

Understanding Cumulative Loan Amortisation

In standard fixed-rate amortisation schedules, the total payment remains constant each period, but the composition changes dynamically: early payments consist predominantly of interest, while later payments consist primarily of principal reduction. The CUMIPMT and CUMPRINC functions calculate the exact sum of interest or principal paid between two arbitrary period boundaries (e.g. from period 1 to period 12).

Syntax and Parameter Breakdown

CUMIPMT Syntax

=CUMIPMT(rate, number_of_periods, present_value, first_period, last_period, end_or_beginning)

CUMPRINC Syntax

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

Argument Definitions

• rate: The periodic interest rate (annual interest rate divided by the number of payment periods per year, e.g. 0.06 / 12 for 6% annual interest paid monthly).
• number_of_periods: The total number of payment periods in the loan term (e.g. 30 * 12 = 360 for a 30-year monthly mortgage).
• present_value: The initial principal loan balance borrowed.
• first_period: The starting payment period of the calculation interval (must be >= 1).
• last_period: The ending payment period of the calculation interval (must be >= first_period).
• end_or_beginning: The payment timing flag:
  • 0: Payment at the end of each period (standard mortgage convention).
  • 1: Payment at the beginning of each period (annuity due).

Practical Amortisation Example in Google Sheets

Suppose your business secures a £250,000 commercial equipment loan with the following specifications:

• Loan Principal: £250,000
• Annual Interest Rate: 5.5% (0.055)
• Loan Term: 5 years (60 monthly payments)
• Evaluation Interval: Year 1 (Periods 1 to 12)

A1: Parameter              | B1: Value
A2: Principal Amount       | B2: 250000
A3: Annual Interest Rate   | B3: 5.5%
A4: Total Monthly Periods  | B4: 60
A5: First Period           | B5: 1
A6: Last Period            | B6: 12
A7: Timing Flag            | B7: 0
A8: Year 1 Cumulative Int  | B8: =CUMIPMT(B3/12, B4, B2, B5, B6, B7)
A9: Year 1 Cumulative Prin | B9: =CUMPRINC(B3/12, B4, B2, B5, B6, B7)

Google Sheets calculates Year 1 interest as approximately -£12,714.52 and Year 1 principal as -£44,570.83 (negative values represent cash outflows). Combining both confirms that total debt service for Year 1 equals £57,285.35.

Comparison of Loan Function Applications

Function	Output	Primary Modeling Use Case
PMT	Total periodic payment	Determining recurring monthly cash requirement
IPMT	Interest for a single period	Monthly accounting entries and tax deduction schedules
PPMT	Principal for a single period	Monthly debt balance reduction entries
CUMIPMT	Total interest across multiple periods	Annual corporate tax returns and loan cost comparisons
CUMPRINC	Total principal across multiple periods	Balance sheet liability auditing and equity build-up

Common Modeling Errors and Solutions

• #NUM! Error: Occurs when first_period < 1, last_period < first_period, or rate <= 0. Verify that period indices fall within 1 to number_of_periods.
• Rate Frequency Mismatch: Entering the annual rate instead of the periodic rate (e.g. entering 0.06 instead of 0.06/12) multiplies interest calculations twelve-fold. Always divide annual rates by payment frequency.