CUMIPMT

Returns the total interest paid on a loan in specified periodic payments.

Syntax:

CUMIPMT(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.

CUMIPMT returns the total interest in payments during the periods start to end inclusive - that is, the sum of IPMT over that time.

Example:

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

returns -57.80 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 interest you pay in the 4th-6th months inclusive is 57.80 currency units. It is given as negative because you pay it.

Application:

An application of using the CUMIPMT function is for calculating the total interest paid on a loan over a specific period. This is useful for borrowers who want to know how much interest they've paid during a certain timeframe, such as for tax purposes or financial planning.

Let's consider a home mortgage loan.

Scenario:

John took out a 30-year fixed-rate mortgage for $300,000 at an annual interest rate of 4.5%. He wants to find out the total interest he will have paid between the 1st and 60th payment (the first five years of the loan).

CUMIPMT Function Breakdown:

The syntax for the CUMIPMT function is:

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

rate: The interest rate per period. The annual rate of 4.5% needs to be divided by 12 to get the monthly rate. So, 4.5% / 12 or 0.045 / 12.
nper: The total number of payments for the loan. A 30-year loan with monthly payments means 30 * 12 or 360 payments.
pv: The present value, or the principal of the loan. This is $300,000.
start_period: The first period in the calculation. John wants to start from the 1st payment, so this is 1.
end_period: The last period in the calculation. John wants to go up to the 60th payment, so this is 60.
type: When payments are due. 0 for the end of the period, 1 for the beginning. Most mortgages are paid at the end of the month, so we'll use 0.

Function:

CUMIPMT(0.045/12, 360, 300000, 1, 60, 0)

Result:

The function would return a value of approximately -64,677.11. The negative sign indicates an outflow of cash (money paid). This means that over the first five years of his mortgage, John will have paid a total of $64,677.11 in interest.

Table showing the breakdown of the CUMIPMT function:

	Parameter	Description	Value in Example
	A	B	C
1	rate	Monthly interest rate (4.5% / 12)	0.045 / 12
2	nper	Total number of payments (30 years * 12 months)	360
3	pv	Present value of the loan	$300,000.00
4	start_period	First payment period to include in calculation	1
5	end_period	Last payment period to include in calculation	60
6	type	When payments are made (end of period)	0
7	Result	Total interest paid from payment 1 to 60	-$64,677.11