CUMPRINC

Returns the total capital repaid on a loan in specified periodic payments.

Syntax:

CUMPRINC(rate, numperiods, presentvalue, start, end, type)

rate: the interest rate per period.

numperiods: the total number of payment periods in the term.

presentvalue: the initial sum borrowed.

start: the first period to include. Periods are numbered beginning with 1.

end: the last period to include.

type: when payments are made:

0 - at the end of each period.

1 - at the start of each period (including a payment at the start of the term).

With a fixed rate loan, where you make a constant payment each period to pay off the loan over the term, some of each period payment is interest on the outstanding capital, and some is a repayment of capital. Over time (as you pay off capital), the interest becomes less and the capital repayment becomes more.

IPMT returns the interest in the payment of a specified period. PPMT returns the capital repaid in the payment of that period. Together they add up to the actual payment, given by PMT.

CUMPRINC returns the total capital repaid in payments during the periods start to end inclusive - that is, the sum of PPMT over that time.

Example:

CUMPRINC(5.5%/12, 12*2, 5000, 4, 6, 0)

returns -603.63 in currency units. You took out a 2 year loan of 5000 currency units at a yearly interest rate of 5.5%, making monthly payments at the end of the month. The capital you repaid in the 4th-6th months inclusive is 603.63 currency units. It is given as negative because you pay it.

Application:

An application of the CUMPRINC function is a mortgage loan. The function is incredibly useful for calculating the total amount of principal paid on a loan over a specific period of time, which helps a homeowner track their progress in paying off their loan.

Scenario

Let's imagine you've taken out a 30-year mortgage for a new home.

Loan Amount (PV): $300,000
Annual Interest Rate (Rate): 5%
Loan Term (NPER): 30 years (360 months)
Payment Frequency: Monthly (payments made at the end of each period)

You want to know how much principal you will have paid off in the first five years of the loan.

CUMPRINC Function Arguments

The CUMPRINC function requires the following arguments:

rate: The interest rate per period. Since your annual rate is 5% and you make monthly payments, you need to divide the annual rate by 12: 5%/12=0.004167.
nper: The total number of payment periods. A 30-year loan with monthly payments has a total of 30×12=360 periods.
pv: The present value of the loan (the principal amount): 300,000.
start_period: The first payment period in the range you want to calculate. For the first five years, this would be month 1.
end_period: The last payment period in the range you want to calculate. For the first five years, this would be month 5×12=60.
type: This indicates when payments are made. We'll use 0 for payments at the end of the period.

Calculation

Using the CUMPRINC function, the formula to calculate the cumulative principal paid in the first five years (months 1 to 60) would be:

CUMPRINC(5%/12, 30*12, 300000, 1, 60, 0)

The result of this calculation is approximately -24,513.80. The result is a negative number because it represents money you are paying out.

Table Example

The CUMPRINC function is most useful when you want to compare how much principal is paid over different periods of the loan. As you can see from the table below, the amount of principal paid increases over time, even though the total monthly payment remains constant.

	Period (Years)	Start Period (Month)	End Period (Month)	CUMPRINC Function	Cumulative Principal Paid
	A	B	C	D	E
1	Year 1	1	12	CUMPRINC(5%/12, 360, 300000, 1, 12, 0)	-$4,426.10
2	Year 5	49	60	CUMPRINC(5%/12, 360, 300000, 49, 60, 0)	-$5,403.80
3	First 5 Years	1	60	CUMPRINC(5%/12, 360, 300000, 1, 60, 0)	-$24,513.80
4	Years 26-30	301	360	CUMPRINC(5%/12, 360, 300000, 301, 360, 0)	-$85,339.67
5	Full Loan Term	1	360	CUMPRINC(5%/12, 360, 300000, 1, 360, 0)	-$300,000.00

As you can see, the CUMPRINC function allows you to quickly see that:

In the first year, a relatively small portion of your payment goes toward the principal.
In the fifth year, you are paying significantly more principal than you did in the first year.
By the final five years of the loan, the majority of your payments are applied directly to the principal, and you pay off a substantial amount of the loan's balance.