Calculate cumulative loan interest

How to calculate cumulative loan interest

To calculate cumulative loan interest based on the interest rate, loan amount, monthly payments, and period, use the CUMIPMT function.

This article is a part of our definitive guide on Excel formulas.

How to calculate cumulative loan interest

  1. Select cell C8
  2. Type =CUMIPMT(C3/12,C5,C2,1,48,0)
  3. Here, C3 is the interest rate, C2 is the loan amount, C5 is the total number of periods.
  4. Press Enter.
  5. The formula returns the cumulative loan interest paid throughout the loan’s duration.

Example

In this example, we have a dataset outlining loan details, including a loan amount of $10,000, an interest rate of 6.03%, a monthly payment of $235, and a loan term of 48 months. The goal is to calculate the total interest paid over the loan term and determine the cumulative interest paid between specific loan payment periods.

Formula:

=CUMIPMT(C3/12,C5,C2,1,C5,0)

How to calculate cumulative loan interest

The formula in cell C8 uses the CUMIPMT function to calculate the cumulative interest, taking into account the periodic interest rate, total number of periods, present value, and start and end periods.

Explanation

Here’s a breakdown of the formula:

The CUMIPMT function calculates the cumulative interest paid on a loan over a specified period. It is particularly useful in financial scenarios where you want to determine the total interest cost for part or all of a loan term.

  • C3/12 (Rate): C3 contains the annual interest rate (6.03% in this example). Dividing this by 12 converts it into a monthly interest rate, as there are 12 months in a year. So, C3/12 = 0.0603 / 12 ≈ 0.005025, or 0.5025% per month.
  • C5 (Number of periods per year): C5 is the number of payments made each year, which is 12 for monthly payments. This ensures the function aligns with the compounding frequency of the loan.
  • C2 (Present value or loan amount): C2 represents the principal loan amount, which is $10,000 in this case.
  • Start period = 1: This value specifies the start of the calculation period. Here, it begins with the 1st period (month)
  • End period = 48: The loan runs for 48 periods (months), so this indicates the end of the calculation period.
  • Type = 0: The last argument specifies whether payments are made at the beginning (1) or end (0) of each period. Here, 0 means payments are made at the end of each period.

The negative result (-1,280) reflects outgoing cash (the borrower is paying this interest amount). It aligns with Excel’s convention for financial functions: payments or costs are negative, while receipts or income are positive.

Workaround with SUMPRODUCT and IPMT

Here are two ways to achieve similar results. The first formula calculates and sums the monthly interest payments over the specified period.

=SUMPRODUCT(-IPMT(C3/12,ROW(1:48),C5,C2))

Furthermore, you can break down the payment schedule into individual interest components:

=SUM(IPMT(C3/12,ROW(1:48),48,C2))

Download the Workbook.