Summary

To calculate the cumulative principal paid between any two loan payments, you can use the CUMIPMT function. In the example shown, we calculate the total principal paid over the full term of the loan by using the first and last period. The formula in C10 is:

=CUMIPMT(C6/12,C8,C5,1,60,0)

Generic formula

=CUMIPMT(rate,nper,pv,start,end,type)

Explanation 

For this example, we want to calculate cumulative interest over the full term of a 5-year loan of $5,000 with an interest rate of 4.5%. To do this, we set up CUMIPMT like this:

rate - The interest rate per period. We divide the value in C6 by 12 since 4.5% represents annual interest: 

=C6/12

nper - the total number of payment periods for the loan, 60, from cell C8.

pv - The present value, or total value of all payments now, 5000, from cell C5.

start_period - the first period of interest, 1 in this case, since we are calculating principal across the entire loan term.

end_period - the last period of interest, 60 in this case for the full loan term.

With these inputs, the CUMIPMT function returns -592.91, the total interest paid for the loan.

Related formulas

Excel formula: Calculate interest for given period

Calculate interest for given period

For this example, we want to calculate the interest portion for payment 1 of a 5-year loan of $5,000 with an interest rate of 4.5%. To do this, we set up PPMT like this: rate - The interest rate per period. We divide the value in C6 by 12 since 4.5% represents annual interest: =C6/12 per - the...
Excel formula: Calculate principal for given period

Calculate principal for given period

For this example, we want to calculate the principal portion for payment 1 of a 5-year loan of $5,000 with an interest rate of 4.5%. To do this, we set up PPMT like this: rate - The interest rate per period. We divide the value in C6 by 12 since 4.5% represents annual interest: =C6/12 per - the...
Excel formula: Calculate interest rate for loan

Calculate interest rate for loan

Loans have four primary components: the amount, the interest rate, the number of periodic payments (the loan term) and a payment amount per period. One use of the RATE function is to calculate the periodic interest rate when the amount, number of payment periods, and payment amount are known. In...
Excel formula: Calculate original loan amount

Calculate original loan amount

Loans have four primary components: the amount, the interest rate, the number of periodic payments (the loan term), and the payment amount per period. One use of the PV function is to calculate the original loan amount, when given the other 3 components. For this example, we want to find the...
Excel formula: Calculate payment for a loan

Calculate payment for a loan

Loans have four primary components: the amount, the interest rate, the number of periodic payments (the loan term) and a payment amount per period. You can use the PMT function to get the payment when you have the other 3 components. For this example, we want to find the payment for a $5000 loan...
Excel formula: Calculate payment periods for loan

Calculate payment periods for loan

Loans have four primary components: the amount, the interest rate, the number of periodic payments (the loan term) and a payment amount per period. One use of the NPER function is to calculate the number of periodic payments for loan. For this example, we want to calculate the number of payments...
Excel formula: Calculate cumulative loan principal payments

Calculate cumulative loan principal payments

For this example, we want to calculate cumulative principal payments over the full term of a 5-year loan of $5,000 with an interest rate of 4.5%. To do this, we set up CUMPRINC like this: rate - The interest rate per period. We divide the value in C6 by 12 since 4.5% represents annual interest: =C6...

Related functions

Excel CUMIPMT function

CUMIPMT Function

The Excel CUMIPMT function is a financial function that returns the cumulative interest paid on a loan between a start period and an end period. You can use CUMIPMT to determine the total interest paid on a loan, or the interest paid between any two payment periods.
Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

Formulas

Functions