MS Excel: CUMPRINC function for loan payment

The CUMPRINC function in Excel calculates the cumulative principal paid on a loan between two periods. In simple terms, it helps you determine how much of the loan principal has been repaid between two specific periods, based on the loan's interest rate, payment schedule, and loan amount.

This function is often used for loan amortization schedules and to analyze how much of the loan balance has been reduced over time.

Syntax

CUMPRINC(rate, nper, pv, start_period, end_period, type)

Argument	Description
rate	The interest rate for each period.
nper	The total number of periods (payments) in the loan.
pv	The present value, or principal amount of the loan.
start_period	The first period in which principal is calculated.
end_period	The last period in which principal is calculated.
type	The type of payment: 0 = Payments at the end of the period (default) 1 = Payments at the beginning of the period

Key Points

• The CUMPRINC function returns a negative value because it represents money being repaid (principal paid back to the lender).

• Principal payments are based on the loan's interest rate and loan amount.

• It’s useful for loan amortization, cash flow analysis, and understanding how much of the loan has been paid down over time.

Practical Example

Scenario:

You have a loan of $10,000 with an annual interest rate of 6%, and it is paid monthly over 5 years (60 months).You want to know how much of the principal you have repaid during the first year (periods 1 through 12).

Formula:

=CUMPRINC(6%/12, 60, 10000, 1, 12, 0)

Explanation:

• rate: 6% annual interest divided by 12 months = 0.5% monthly rate.

• nper: The loan is paid over 60 months.

• pv: The loan principal is $10,000.

• start_period: Principal payments start at the first month (period 1).

• end_period: Principal payments end at the 12th month.

• type: Payments are made at the end of each period (0).

Result: -€3,797.67

Explanation:This means that in the first year of the loan, $3,797.67 of the principal will have been paid off. The negative sign indicates that this money is being repaid.

Another Example:

Now, let’s say you want to know how much principal has been repaid from month 13 to month 24.

Formula:

=CUMPRINC(6%/12, 60, 10000, 13, 24, 0)

Result: -€4,063.68

Summary

Item	Value
Loan Amount (PV)	$10,000
Interest Rate	6% (Annual)
Payment Periods	60 (5 years)
Principal Payment Periods	1 through 12
Cumulative Principal Paid	$3,797.67 (first year)

Important Notes

• CUMPRINC calculates the principal for the period(s) between start_period and end_period, inclusive.

• The function will return a negative value because it represents money paid out (principal repaid to the lender).

• To ensure accuracy, remember that the rate should be divided by the number of periods in a year (e.g., for monthly payments, divide by 12).

• CUMPRINC is often used in loan amortization schedules to calculate how much principal has been repaid over time.

When to Use CUMPRINC?

• To calculate principal payments made over a specific period in a loan.

• For loan amortization schedules, showing how much of the loan principal has been paid off at different stages.

• To assess the total principal paid in a loan, which can be useful for budgeting and financial planning.

• For fixed-rate loans, especially when payments are made regularly (e.g., monthly).

Conclusion

The CUMPRINC function in Excel is a powerful tool for anyone dealing with loan analysis and financial calculations.It helps you determine the cumulative principal paid over a specific period, making it valuable for loan amortization schedules, budgeting, and cash flow analysis.