Calculate Interest for Remaining Loan Payments in Excel
Question details
Calculate interest for the remaining payments on an amortizing loan without using a fixed 12-month period.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A loan has fewer than 12 monthly payments remaining and may include a balloon payment.
- Observed behavior
- A CUMIPMT formula using a fixed 12-month period returns the wrong interest for the remaining payments.
Check the annual interest rate, original loan amount, full payment count, and first and last payment numbers to include. Confirm whether a balance is due as a balloon payment at the end, because CUMIPMT assumes the loan is fully paid off by the final period.
Use the exact remaining periods in CUMIPMT
Use this method for a loan that is fully amortized over its original term.
In your worksheet, identify the first and last payment numbers to include. For the last six payments of a 24-month loan, use periods 19 through 24.
Select an empty cell and enter =-CUMIPMT(5%/12,24,59650,19,24,0). The 24 is the full loan term, while 19 and 24 define the interest calculation range.
Press Enter. The example returns approximately $226 in interest; the leading minus sign displays the interest as a positive amount.

Calculate each payment's interest when a balloon balance remains
Use IPMT when regular payments leave a specified balance due at the end of the loan.
Calculate Remaining Loan Interest in WPS Spreadsheets
Use CUMIPMT in WPS Spreadsheets to total interest over selected payment periods, or use IPMT for a loan with a balloon balance.
- 1. Open a spreadsheet: Open WPS Spreadsheets and create a workbook or open an existing Excel file.
- 2. Enter the loan details: Add the annual rate, original loan amount, full payment count, and remaining payment numbers to the worksheet.
- 3. Calculate the interest: For a fully amortized loan, select an empty cell and enter =-CUMIPMT(5%/12,24,59650,19,24,0), replacing the example values with your loan details.
- 4. Account for a balloon if needed: For a nonzero balloon balance, list the remaining periods in column A, calculate each with IPMT in column B, and use SUM to total the interest.

Frequently Asked Questions
Why should nper be the full loan term rather than the remaining months?
CUMIPMT uses nper to determine the payment schedule for the original loan. Use the start_period and end_period arguments to select only the remaining payments.
Can CUMIPMT calculate interest for a loan with a balloon payment?
Not directly: CUMIPMT assumes the loan is fully paid off by the end of the specified term. Use IPMT with a future-value argument for the balloon balance, then sum the interest for the relevant periods.
Why does CUMIPMT return a negative number?
Interest paid is treated as an outgoing cash flow. Place a minus sign before the function, as in =-CUMIPMT(...), to display the interest as a positive amount.
What does the final zero in the formula mean?
The final argument is the payment type. Use 0 for payments at the end of each period or 1 for payments at the beginning.




