Formula Errors

Calculate Interest for Remaining Loan Payments in Excel

John WilsonJohn Wilson Nov 14, 2026 869 views

Question details

Calculate interest for the remaining payments on an amortizing loan without using a fixed 12-month period.

Calculate Interest for Remaining Loan Payments in Excel
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.
Before you start

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.

Solution 1Recommended

Use the exact remaining periods in CUMIPMT

Use this method for a loan that is fully amortized over its original term.

1
Identify the payment range

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.

2
Enter the formula

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.

3
Check the result

Press Enter. The example returns approximately $226 in interest; the leading minus sign displays the interest as a positive amount.

Use the exact remaining periods in CUMIPMT
Keep the original term: Do not change the nper argument to six just because six payments remain. This formula assumes monthly payments at the end of each period and no remaining balloon balance.
Calculate loan interest in WPS

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. 1. Open a spreadsheet: Open WPS Spreadsheets and create a workbook or open an existing Excel file.
  2. 2. Enter the loan details: Add the annual rate, original loan amount, full payment count, and remaining payment numbers to the worksheet.
  3. 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. 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.
Calculate interest for the exact remaining payment periodsModel a balloon balance with IPMTOpen and edit Microsoft Excel XLSX filesUse familiar spreadsheet formulas and functions
microsoft office alternative - wps office

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.