Your ultimate financial calculator Excel can help find out your monthly payments on a new loan. The function for this task is CUMIPMT and it can calculate cumulative loan interest over a period of time.

Syntax

=CUMIPMT(annual interest rate/12, periods by month, loan amount, start period, end period, timing of payment)

Steps

Begin by typing in =CUMIPMT(
The first argument is the monthly rate. You can get this by dividing the annual rate by 12 (i.e. C2/12)
The second argument is the total number of periods in months (i.e C3)
The third argument is the initial loan amount (i.e. C4)
The next two arguments are the start and end periods (i.e. C5, C6)
The last argument is whether the payment is at the end or beginning of each period. Enter 0 if payments are at the end of the period.

How

The CUMIPMT function goes through each payment period and calculates the cumulative loan interest. When using this formula make sure that all units are consistent with each other. For example, if periods are in months, the interest rate should be monthly as well. Here, we used 5% as the annual interest and used months for period interval. Therefore the annual interest rate is divided by 12, which gives our monthly interest rate.

Another important point is defining the start and end periods. Choose numbers between 1 and the total period count to specify the start and end periods. To calculate for the entire duration set start period as 1, and end period as the number of total periods.

If payments are at the end of each period use 0. If they are at the beginning, use 1 instead.

=CUMIPMT(C2/12,C3,C4,C5,C6,0)

When you calculate cumulative loan interest, you will see that the calculated payment amount is negative. This is because of the direction the money is going (i.e. out of pocket). Removing the dash (-) or multiplying the result with "-1" will return a positive.

=-1*CUMIPMT(C2/12,C3,C4,C5,C6,0)

= ABS(CUMIPMT(C2/12,C3,C4,C5,C6,0))

For more financial formulas like this one, see 10 most useful formulas in financial modeling.