CUMIPMT calculator and payment-range logic

Check the CUMIPMT logic for a chosen range of fixed-rate loan payments. This working tool shows the spreadsheet's negative cash-outflow result, the matching CUMPRINC result, and the function call for your inputs. Payment numbers are inclusive and counted from the start of the modeled loan.

Your interest schedule

Worked example: payments in the second year

The example inputs are from Microsoft CUMIPMT and Microsoft CUMPRINC: an original balance of $125,000, an annual rate of 9%, a 30-year term, and end-of-period monthly payments. The selected range is payments 13 through 24. Figures below are calculated from those inputs and rounded for display.

How it works

P is the original loan balance, N is the number of monthly payments, and A is the constant scheduled payment. For beginning-of-period payments, divide A by (1 + r); the first payment has no interest. With a zero rate, A = P / N and interest is zero. Sum the interest and principal separately across the requested payments.

Microsoft PMT documentation supports constant-payment calculation, and Microsoft CUMIPMT and CUMPRINC define the inclusive payment-range totals. The CFPB amortization explanation describes how the balance and interest portions change. Zero-rate results are an extension here; Excel CUMIPMT and CUMPRINC require a positive rate.

Map the spreadsheet arguments

The annual rate field becomes the periodic rate by dividing the percentage by the monthly payment frequency. The term in months becomes nper. The original loan balance becomes pv. First and last payment become start_period and end_period. The timing choice becomes type. Microsoft CUMIPMT documentation defines those arguments and numbers payment periods from the first payment.

The calculation first finds the constant payment for the entire schedule. It then walks through every payment in order, even when your selected range starts later. This matters because the opening balance for a later payment depends on all earlier principal reductions. Starting the recurrence at the original balance for a later selected payment would overstate its interest.

Follow the recurrence

For end-of-period timing, calculate interest on the opening balance, subtract that interest from the scheduled payment to find principal, and subtract principal from the opening balance. Repeat using the new balance. Add only the interest amounts whose payment numbers fall within the selected endpoints. CUMPRINC adds the corresponding principal reductions over the same range.

Beginning-of-period timing changes both the payment amount and the first interest portion. The initial payment takes place immediately, so its interest is zero. Subsequent periods accrue interest on the remaining balance before the next payment. The tool adjusts the constant payment for this timing before building the schedule. The annual table makes the selected interest and principal portions visible even when the spreadsheet outputs are signed negatives.

Understand the sign and validation

Microsoft's examples show negative results because these are payments out. This page keeps that convention in the headline results. The annual table labels positive interest and principal paid, making the amounts easier to inspect. Compare the magnitude and the sign separately when checking a spreadsheet. On the homepage calculator, the main results also use positive paid amounts.

The browser rejects reversed ranges, payment numbers outside the term, and fractional payment counts. It also requires a positive original loan amount. A zero annual rate is accepted as a useful mathematical extension: interest is zero and principal is repaid evenly. Microsoft's CUMIPMT and CUMPRINC documentation says those spreadsheet functions return an error when the rate is zero or negative, so a zero-rate tool result is not an exact Excel compatibility claim.

Check the example before using your own inputs

The worked example uses Microsoft's published balance, rate, term, and second-year range. Compare the calculated interest and principal magnitudes with the source before changing inputs. Then change one argument at a time if your spreadsheet differs. Check percentage conversion, monthly versus annual units, payment timing, and the selected endpoints. For the sum across the entire schedule, use lifetime interest; for one grouped loan year, use annual interest paid.

Frequently asked questions

Sources

• Microsoft: CUMIPMT function
• Microsoft: CUMPRINC function
• Microsoft: PMT function
• CFPB: amortization and payment allocation

Data as of 2026-10-05. Source examples and formula documentation only; no live market rates.

Related interest tools