How to use the CUMPRINC() function in Excel

This function calculates the principal portion repaid between two specified periods for an annuity loan (a loan repaid in equal periodic installments).

Syntax:

CUMPRINC(Rate, Nper; Pv; Start_Period; End_Period; Type)

Arguments:

• Rate (required): The nominal interest rate of the loan.
• Nper (required): The total number of repayment periods.
• Pv (required): The principal loan amount.
• Start_Period (required): The first period of the calculation.
• End_Period (required): The last period of the calculation.
• Type (required): The payment timing argument, where:
  • Type = 1 indicates payments are made at the beginning of each period.
  • Type = 0 (default) indicates payments are made at the end of each period.

Notes:

• If fractional values are provided for integer-based arguments (e.g., Start_Period), the decimals are truncated.
• Rate, Nper, and Pv must be positive; otherwise, CUMPRINC() returns the #NUM! error.
• Start_Period must be ≥ 1.
• End_Period must be ≥ 1 and ≥ Start_Period.
• Type must be 0 or 1.

Background:

Loans can be repaid in different ways. In an annuity repayment:

• The borrower pays a fixed amount each period, consisting of:
  • A principal repayment portion (increases over time).
  • An interest portion (decreases as the outstanding loan balance shrinks).

This function calculates the compounded principal repayment (excluding interest). By summing principal repayments over past periods, you can determine the residual debt (remaining loan balance).

Examples:

1. Principal Repayment Over a Loan Term
   In the Repayment Calculation (Annuity) example from the PV() function:
   • A borrower takes a $176,121.76 loan at 5.5% annual interest, repaying $1,000/month for 30 years.
   • By the 19th month, the residual debt is $172,513.25, meaning $3,608.51 of principal has been repaid.

Verify this with CUMPRINC() (accounting for rounding errors):

=-CUMPRINC(5.5%/12; 30*12; 176121.76; 1; 18; 0)

(Negative result indicates cash outflow relative to the loan amount.)

1. Fixed-Rate Mortgages & Residual Debt
   Many mortgage loans have a fixed interest rate for an initial period (shorter than the full term).
   • Borrowers can use CUMPRINC() to calculate residual debt after the fixed-rate period.
   • This helps assess refinancing risks if interest rates rise.
   • Example calculation method matches the previous example.

Rounding Considerations:

Built-in functions may not reflect real-world bank calculations (which use 2 decimal places). For precise repayment schedules, use the ROUND() function in monthly plans.