Excel Formula Help - CUMIPMT for total interest paid on a loan
Following on from an earlier blog that looked at calculating monthly loan payments, this Microsoft Excel tip will look at a similar financial formula that calculates the total interest paid on a loan over a given period. For this calculation we use CUMIPMT (cumulative payment).
Looking at our loan book, we can see that there are three loans representing separate mortgages in the row, Loan Amount. Beneath, there is the Total Number Of Payments, represented in months and below that, the agreed Interest Rate.
Our Cumulative Interest is calculated by first selecting the empty cell and in the Formulas toolbar, clicking on Insert Function on the left hand side. We can find CUMIPMT along with other financial functions by changing the search category drop down to Financial. Scroll to find CUMIPMT, select and click OK.
We are then presented with the Function Arguments dialogue box. Here we will enter first the Rate which will be our interest payment divided by 12 for the 12 monthly payments of each year. Place the cursor in the Rate filed and then select the Interests Rate of Loan 1 by clicking on B4. Complete with forward slash / and the number 12.
The other fields are Nper, for the number of payments - select B3 (note, this row must formatted to Number). Pv is the original loan value, B2. The start period is month 1, so simply enter the number 1. The end period is the same as Nper, so click on B3 again. You will need to tab down to see the final entry which requires either a 0 or 1 depending when we intend the payments to begin, 0 = end of the month, 1 = beginning of the month. As with most mortgages, we will set payments for the end of the month.
When we hit enter, we see our total interest paid over, in this case, the sum of 280 payments at 4% on a loan of $120,000. If we copy the formula across, we can see that Loans 2 and 3 require higher total interest repayments linked to their longer payment period and higher interest rates.
For more help and advice with Financial and other formulas, contact our experts.
