Zoho Sheet CUMIPMT: Total Interest by Payment Range and Timing
Calculate inclusive-period interest totals in Zoho Sheet, verify a monthly schedule and compare beginning-period payments without changing original inputs.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Quick answer: =CUMIPMT(1%;12;8000;1;6;0) has an independently expected result of approximately −384.1103732020. It adds interest from payments 1 through 6 inclusive, rather than six complete installments.
Availability and version history
- Introduced: The original introduction release and date are not established by the checked sources.
- Availability: Currently documented in Zoho Sheet’s rolling web-service help; no numbered release or retirement endpoint is established.
- Scope: English-language web-service documentation. Exact mobile and offline-client parity is not established here.
- Checked: October 14, 2026, against the official CUMIPMT reference. Documentation and independent cash-flow calculations; no native Zoho Sheet test.
Syntax and the required timing argument
=CUMIPMT(rate; nper; pv; start; end; type)
All six positions are required in Zoho’s syntax. Rate is per payment period; nper is the total payment count; pv is the initial amount borrowed. Start and end are the first and last included payment numbers, beginning at 1. Type 0 means end-period payments and 1 beginning-period payments, including one at the start of the term.
Unlike IPMT, this signature has no future-value argument. Do not insert a balloon balance into the start position, or omit type by assuming another function’s default.
Practice sheet: one year of monthly payments
Create MonthlyInterest. Put labels in A2:A9 and these entries or formulas in B:
| Cell | Meaning | Entry |
|---|---|---|
| B2 | Nominal annual rate | 12% |
| B3 | Payments per year | 12 |
| B4 | Years | 1 |
| B5 | Initial borrowed amount | 8000 |
| B6 | Monthly rate | =B2/B3 |
| B7 | Payment count | =B3*B4 |
| B8 | Timing | 0 |
| B9 | Monthly cash payment | =PMT(B6;B7;B5;0;B8) |
B6 is 1%, B7 is 12 and B9 is approximately −710.7903094267. The annual input is explicitly nominal with monthly allocation; it is not an effective annual yield divided by twelve.
Put Period, IPMT interest, Opening, Payment, Closing and Independent interest in A12:F12. Enter integers 1–12 in A13:A24.
- B13:
=IPMT($B$6;A13;$B$7;$B$5;0;$B$8), fill through B24 - C13:
=$B$5 - C14:
=E13, then fill through C24 - D13:
=$B$9, fill through D24 - E13:
=C13*(1+$B$6)+D13, fill through E24 - F13:
=-C13*$B$6, fill through F24
| Period | Expected interest B and F | Expected closing E |
|---|---|---|
| 1 | -80.0000000000 | 7369.2096905733 |
| 2 | -73.6920969057 | 6732.1114780523 |
| 3 | -67.3211147805 | 6088.6422834061 |
| 4 | -60.8864228341 | 5438.7383968134 |
| 5 | -54.3873839681 | 4782.3354713548 |
| 6 | -47.8233547135 | 4119.3685166416 |
| 7 | -41.1936851664 | 3449.7718923813 |
| 8 | -34.4977189238 | 2773.4793018784 |
| 9 | -27.7347930188 | 2090.4237854704 |
| 10 | -20.9042378547 | 1400.5377138984 |
| 11 | -14.0053771390 | 703.7527816106 |
| 12 | -7.0375278161 | 0 |
The displayed decimals are rounded; calculate with full stored precision. This independent balance recurrence reconstructs each interest component without using CUMIPMT itself.
Check inclusive halves and the complete year
Enter these formulas in H:
| Cell | Formula | Expected result, approximately |
|---|---|---|
| H2 | =CUMIPMT(B6;B7;B5;1;6;B8) |
-384.1103732020 |
| H3 | =CUMIPMT(B6;B7;B5;7;12;B8) |
-145.3733399188 |
| H4 | =CUMIPMT(B6;B7;B5;1;12;B8) |
-529.4837131208 |
| H5 | =SUM(B13:B18) |
-384.1103732020 |
| H6 | =SUM(B19:B24) |
-145.3733399188 |
| H7 | =SUM(B13:B24) |
-529.4837131208 |
| H8 | =B7*B9+B5 |
-529.4837131208 |
| H10 | =CUMIPMT(B6;B7;B5;1;1;B8) |
-80 |
The halves do not overlap: payments 1–6 and 7–12 cover twelve payments. H8 separates principal from the total cash paid. H10 illustrates the single included payment when start and end are both 1; the function’s documented invalid-order condition is start greater than end.
For a positive “interest paid” reporting magnitude, put =-H4 in H11; expect approximately 529.4837131208. Keep the signed value when reconciling it with principal and total cash flow.
Advanced: compare beginning-period timing on a separate sheet
Create DueInterest and enter periods 1–12 in A2:A13. In B2 enter =IPMT(1%;A2;12;8000;0;1) and fill through B13. The first two interest components are 0 and approximately −72.9624721839.
Put =CUMIPMT(1%;12;8000;1;12;1) in D2 and =SUM(B2:B13) in D3. Both targets are approximately −445.0333793275. In D4 enter =PMT(1%;12;8000;0;1); expect approximately −703.7527816106. In D5 enter =12*D4+8000 to reconstruct the same total interest.
The payment at time zero reduces the balance before any period’s interest accrues. This schedule changes payment timing and size, not merely an output sign. Do not change B8 on MonthlyInterest while retaining its end-period-only balance recurrence as if the same steps described both timelines.
Documented errors and remaining boundaries
Zoho explicitly lists #NUM! for a negative rate, nper or pv; start or end below 1; start greater than end; or a numeric type other than 0 or 1. Those are documentation statements, not native observed error tests.
The list says less than zero, not less than or equal to zero. It does not fully establish every zero-input, fractional-period or beyond-term result. These examples use positive rates, positive principal and integer payment numbers inside the term. Keep those unresolved boundaries separate from the verified arithmetic.
CUMPRINC totals principal, IPMT returns one interest component and PMT returns the entire installment. SUM can reconcile a displayed schedule. Real fees, variable rates, date-based accrual and payment-rounding rules require additional inputs beyond this constant-period model.