Excel: Calculate Total Interest Paid on Loan

---

You can use the CUMIPMT function in Excel to calculate the total interest that will be paid on a loan.

This function uses the following syntax:

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

where:

• rate: The annual interest rate
• nper: The total number of payment periods
• pv: The present value (i.e. starting value of the loan)
• start_period: The first period in the calculation
• end_period: The last period in the calculation
• type: 0 = payment at end of period, 1 = payment at beginning of period

The following example shows how to use this function in practice to calculate the total interest that will be paid on a loan.

Example: Calculate Total Interest Paid on Loan in Excel

Suppose we take out a $100,000 loan that has a 7.50% annual interest rate and a total duration of 10 years.

We can start by typing this information into Excel:

Next, we can type the following formula into cell B5 to calculate the total interest that will be paid on this loan, assuming we make monthly payments:

=CUMIPMT(B2/12, B3*12, B1, 1, B3*12, 0)

The following screenshot shows how to use this formula in practice:

The formula returns a value of -42442.12, which tells us that we will pay $42,442.12 in total interest over the course of this 10-year loan.

Note that the higher the interest rate, the more interest will be paid over the course of the loan.

For example, suppose we change the annual interest rate to 8.5% instead:

The formula now returns a value of -48782.82, which tells us that we will pay $48,782.83 in total interest over the course of this 10-year loan.

This should make sense that the total interest paid increased since we increased the annual interest rate.

Feel free to play around with the numbers in your own spreadsheet to calculate the total interest paid based on your own beginning balance, annual interest rate, and loan term.

Note: You can find the complete documentation for the CUMIPMT function in Excel .