ISPMT in Collabora Office: Calculate Interest Under Equal Principal Reductions
Use Collabora ISPMT for a linearly declining principal model, distinguish the period index from payment count, and avoid confusing it with level-payment interest.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Quick answer: =ISPMT(1%;18;24;120000) has independently expected result −300, matching the Collabora help example. It calculates interest remaining after eighteen equal principal reductions in a twenty-four-period model.
Syntax and repayment model
=ISPMT(Rate; Period; TotalPeriods; Invest)
Rate is the periodic interest rate. Period is the position used in the interest calculation. TotalPeriods is the total number of principal-reduction periods, and Invest is the initial principal. All four arguments are required.
The current Collabora source evaluates Invest*Rate*(Period/TotalPeriods-1). For a positive initial principal and rate, the interest output is negative before the principal is fully reduced. This follows a cash-flow sign convention; its positive cost magnitude is the negative of the returned value.
This is an equal-principal-reduction model. It is not the IPMT calculation for a constant-total-payment loan. Equal principal reductions cause the interest component, and therefore the combined payment, to decline over time.
Example 1: reproduce the documented interest amount
Put monthly Rate 1% in F2, TotalPeriods 24 in F3 and Invest 120000 in F4. Put Period 18 in A2 and enter =ISPMT($F$2;A2;$F$3;$F$4) in B2.
The equal principal reduction is 120000/24=5000 per period. After eighteen such reductions the remaining principal is 30000. One percent of that is 300, giving a function result of −300 under the stated sign convention.
The annual nominal rate of 12% becomes a monthly 1% only under that nominal monthly-compounding convention. An effective annual rate would require a different monthly conversion.
Example 2: inspect the index endpoints
Put 0, 1, 18, 23, 24 in A5:A9. Use the same formula, starting with =ISPMT($F$2;A5;$F$3;$F$4), and fill down.
Independent outputs are −1200, −1150, −300, −50 and 0. Period zero uses the full principal, while Period24 represents the fully reduced balance. These endpoints explain why an index cannot be casually replaced with a one-based payment number from another function.
For a schedule that charges interest on each period’s opening balance, use indices zero through twenty-three for the twenty-four charges. State that timing convention in the worksheet rather than assuming the function knows the payment dates.
Advanced application: build an opening-balance interest schedule
Put indices 0 through 23 in A12:A35. In B12 enter =$F$4*(1-A12/$F$3) and fill down for opening principal. In C12 enter =ISPMT($F$2;A12;$F$3;$F$4) and fill down.
The mathematical relation is C=−Rate*B. The interest magnitudes form the arithmetic sequence 1200, 1150, …, 50, totaling 15000. Constant principal reductions total 120000, so combined principal-plus-interest outflow under this specified timing is 135000.
A model that charges after each reduction instead uses a different index sequence and a different total interest. The function’s algebra does not select the contractual timing for you.
Advanced application: expose the distinction from a constant payment
The first opening-balance charge is 1200 and the principal reduction is 5000, so the combined amount is 6200. The final charge is 50 plus the same 5000 principal, totaling 5050.
Those amounts differ, as expected in an equal-principal schedule. A constant-payment amortization model must solve a different payment amount and balance path; substituting IPMT or PMT without matching the model would change the meaning.
Errors and troubleshooting
Require positive TotalPeriods for the practical model and validate integer indices within the intended schedule. The inspected source is a direct algebraic expression; it does not impose all those business-domain checks. TotalPeriods=0 causes a division problem, and an index beyond the schedule can produce an economically meaningless sign change.
Keep rate units aligned with period units and label whether interest is before or after a principal reduction. These examples establish the mathematical model and source formula, not native execution, contractual advice or a certified error display for every invalid input.
Availability and verification
The exact function is documented in Collabora Office 26.04 desktop help. The snapshot was retrieved on 2026-10-13 and hash-verified on October 15, 2026. Desktop help does not establish Online or mobile support. First introduced release and last functioning version are unknown; the dedicated evidence does not establish them.
No native Collabora or WPS run occurred. Worked values are independent mathematical expectations, not verification of every native error, coercion or numerical limit. Formulas use English names and semicolon separators.
A current Collabora interpreter source snapshot retrieved October 15, 2026 corroborates the stated formula or input handling. It is a moving-main source snapshot rather than a pinned 26.04 binary or native application execution.