HOW TO CALCULATE CUMULATIVE LOAN INTEREST
Just as you can solve many things in Excel, there are ways you can calculate cumulative loan interests. In this post, we’ll consider how to calculate cumulative loan interest using a formula approach in Excel.
The general formula to calculate cumulative loan interest:
=CUMIPMT(Annual interest rate/12, total periods per month, loan amount, start period, end period, payment timing)
The formula to calculate cumulative loan interest is based on the “CUMIPMT” function.
In figure 1 above, the cumulative principal paid over the loan’s total term is calculated using the START period and the END period.
The formula in cell G12 is:
=CUMIPMT (G6/12, G8, G5, 1, 48, 0)
We want to calculate cumulative loan interest over a period of 4-year loan of $2,500 with 2.40% interest rate. To perform this, the CUMIPMT is set up thus:
nper – the total number of payment periods for the loan, 48, from cell G8.
Rate – The 2.4% represents the interest rate per annum. The value in G6 is divided by 12 to get the monthly interest. = G6/12.
pv – The present value, or the total loan amount. This is $2500 from cell G5.
Start period – This is the first period of interest. In this instance, it is 1 since we are calculating principal over the entire loan term.
End period – This is the last period of interest. In this case, it is 48 for the full loan term.
Figure 1. Calculate cumulative loan interest
With all these inputs, the “CUMIPMT” function returns -53.3989 as the the total interest over the loan term.