CUMIPMT Function in Microsoft Excel: Syntax, Examples & Troubleshooting
Calculate cumulative loan interest in Excel with CUMIPMT. Reconcile inclusive payment ranges, negative cash flows, beginning-of-period timing and the zero-rate error.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Quick answer: CUMIPMT totals the interest portion of loan payments from a starting payment number through an ending payment number, inclusive. With a positive amount borrowed, its interest repayments are negative cash flows.
Syntax and arguments
=CUMIPMT(rate, nper, pv, start_period, end_period, type)
All six arguments are required. rate is the interest rate per payment period; nper is the loan’s total number of payment periods; pv is the positive amount initially financed. start_period and end_period are inclusive payment numbers, beginning with 1. type is 0 for payments at period end or 1 for payments at period beginning. There is no omitted-type default in this function’s documented syntax.
For monthly payments, an annual nominal rate is normally supplied as annual-rate/12 and a term in years as years×12, provided those assumptions match the loan. The following example instead uses annual payments to keep the reconciliation visible. It assumes a fixed rate, equal payments and full repayment; it is not a model of fees, variable rates or irregular extra payments.
Prepare a two-payment loan
Enter 10% in B2 (annual rate), 2 in B3 (annual payments), 1000 in B4 (amount borrowed) and 0 in B5 (end-of-year timing). The independently calculated payment magnitude is 576.1904761905. For a positive loan received, Excel’s repayment cash flows are negative.
An independent amortization calculation uses payment = 1000×0.1/[1−(1.1)^−2]. Interest is opening balance×0.1; principal is payment minus interest. The two rows are:
| Payment | Opening balance | Interest paid | Principal paid | Closing balance |
|---|---|---|---|---|
| 1 | 1000 | 100 | 476.19047619 | 523.80952381 |
| 2 | 523.80952381 | 52.38095238 | 523.80952381 | 0 |
Displayed values are rounded here; calculations retain full precision.
Worked example: total interest over both payments
- In D2 enter
=CUMIPMT(B2,B3,B4,1,2,B5). - Expected result: approximately −152.38095238.
- Format the cell as currency to display −152.38, without changing the stored precision.
The independent check is −(100 + 52.38095238). Do not interpret the entire payment total of 1152.38095238 as interest; it also repays the original 1000 principal.
Second example: isolate one payment
=CUMIPMT(B2,B3,B4,1,1,0) is expected to return −100. =CUMIPMT(B2,B3,B4,2,2,0) is expected to return approximately −52.38095238. Equal start and end select one payment, not an empty interval. Summing those two results reconciles to the full interest total.
Advanced application: compare payment timing correctly
For the same rate, term and amount, =CUMIPMT(10%,2,1000,1,1,1) is expected to return 0, because the first beginning-of-period payment occurs immediately. The equal payment magnitude for this timing is 523.80952381, leaving 476.19047619 after the immediate payment. One year of interest on that balance is 47.61904762.
Thus =CUMIPMT(10%,2,1000,1,2,1) is expected to return approximately −47.61904762. Changing type changes the payment schedule itself, so it is incorrect to reuse the end-of-period payment amount in a beginning-of-period reconciliation.
For year-by-year reporting on a longer monthly loan, use ranges 1–12, 13–24 and so on while keeping rate, nper, pv and type fixed. The ranges should partition the same original amortization schedule; do not reset pv to the original amount and restart period 1 for every report year.
Troubleshooting and limits
Microsoft documents #NUM! when rate, nper or pv is zero or negative; when start_period or end_period is less than 1; when start_period is greater than end_period; or when type is not 0 or 1. Notably, a zero-interest loan is outside this function’s documented domain even though a simple zero-interest payment schedule can be calculated directly.
Keep selected payment numbers inside the actual loan term. Do not rely on undocumented treatment of fractional or out-of-term periods. If the sign looks wrong, first consider the cash-flow viewpoint rather than negating the original positive pv, which would violate the documented input requirement.
A 10% entry is the numeric value 0.1. Entering 10 as rate means 1000% per period. Do not round each intermediate balance to match a displayed currency amount unless that rounding is part of the actual loan policy. These examples explain spreadsheet calculations; actual lender statements may include fees, timing rules and rounding conventions not represented here.
Compatibility and verification
Microsoft’s reference checked on October 17, 2026 lists: Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel 2024 Excel 2024 for Mac Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2016. This current support list does not establish the first release. The introduction version and last functioning version have not been established from the reviewed documentation. No retirement is asserted.
Example verification: Expected numerical results are independently calculated; text and shape expectations follow the documented contract. These examples have not been tested in Microsoft Excel. No screenshot or native execution is claimed.
Related guides and source
PMT in Excel | IPMT in Excel | PPMT in Excel
- Microsoft CUMIPMT reference, retrieved October 17, 2026.