DISC in Collabora Office: Calculate a Security’s Annual Discount Rate
Use Collabora DISC with per-100 prices, reproduce the documented discount rate, and distinguish a redemption-based discount from investment-based return.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Quick answer: =DISC(DATE(2001;1;25);DATE(2001;11;15);97;100;3) has documented result about 3.72%. It calculates an annualized discount rate using price and redemption values quoted per 100 units of par.
Syntax and units
=DISC(Settlement; Maturity; Price; Redemption [; Basis])
Settlement is the purchase date and Maturity is the expiry date. Price is the purchase price per 100 currency units of par; Redemption is the redemption value on that same per-100 scale. All four are required. Basis is optional and defaults to zero.
| Basis | Documented convention |
|---|---|
| 0 or omitted | US NASD, twelve 30-day months |
| 1 | Actual days in months and actual days in the year |
| 2 | Actual days in months, 360-day year |
| 3 | Actual days in months, 365-day year |
| 4 | European method, twelve 30-day months |
Use genuine date values or DATE formulas. The examples below use explicit bases and ordinary dates so the date and year conventions remain visible. A day-count basis is part of the calculation model, not a display preference.
Use numeric prices such as 97 and 100 for the quoted per-100 values. The result is a decimal rate: a value around 0.0372 should be displayed as roughly 3.72%, not multiplied by 100 and then formatted as a percentage a second time.
Example: reproduce the vendor result
Put the two dates in A2:B2 using DATE, 97 in C2, 100 in D2, and 3 in E2. In F2 enter:
=DISC(A2;B2;C2;D2;E2)
There are 294 actual days between the dates. The independent discount calculation is:
=(D2-C2)/D2*365/(B2-A2)
It gives approximately 0.037244897959, or 3.7244897959%, consistent with the vendor’s rounded 3.72% control. The gain of 3 is divided by redemption 100 before annualization.
Example: an easier half-year 30/360 control
For settlement 2026-01-15, maturity 2026-07-15, price 98, redemption 100, and basis 0, the ordinary mid-month 30/360 interval is 180 days.
The independent discount rate is (100−98)/100×360/180=0.04, or 4%. Written as a function call:
=DISC(DATE(2026;1;15);DATE(2026;7;15);98;100;0)
This is an independent model expectation, not a native observation. The 2% discount over a half-year is annualized under the chosen convention.
Advanced comparison: discount is not investment return
For that same price 98 and redemption 100, the simple gain relative to the invested price is 2/98, approximately 2.040816327% over the half-year. Annualizing by two gives approximately 4.081632653%.
DISC instead uses 2/100, giving 4% annualized in this example. Both ratios use the same cash difference but different denominators. Calling the DISC result a generic annual return hides that distinction.
It is also not an effective compounded annual return. A compounded model for repeated identical half-year growth would use (100/98)^2−1, approximately 4.123281966%. That reinvestment assumption is separate from DISC.
Advanced application: reverse-check a quoted discount
With an annual discount of 4%, redemption 100, and a half-year 30/360 fraction of 0.5, the independent price check is:
=100*(1-4%*0.5)
Expected price: 98 per 100 par. Keeping this inverse arithmetic beside the rate calculation makes the chosen denominator and time fraction visible.
For actual/365 or other bases, use the corresponding time fraction rather than reusing 0.5 automatically. ROUND controls the reported quote; it does not select the basis or resolve a denominator mismatch.
Errors and troubleshooting
Check date order, positive price/redemption inputs, consistent per-100 units, and Basis. A result displayed as 0.04 rather than 4% may only need percentage formatting. A result near 400% often signals that a percentage was entered on the wrong scale.
The dedicated help does not enumerate exact invalid-input error codes or every month-end convention. The independent examples use regular mid-month dates, and do not certify unusual schedules, transaction costs, taxes, or compounding. DATE avoids ambiguous date-text parsing when reproducing the controls.
Availability and verification
The exact function is documented in Collabora Office 26.04 desktop help, retrieved and reviewed on October 17, 2026. This establishes a documentation scope, not the first release that introduced the function or the last version in which it works; those versions and any retirement date remain unestablished. Collabora Online and mobile support are not inferred from desktop help, nor is behavior in another spreadsheet product.
The examples are documentation-based expectations with independent calculations. They have not been executed in Collabora Office. No application screenshot or native error/coercion certification is implied. Formulas use English function names and semicolon argument separators.
References
- Collabora Office 26.04: DISC (checked October 17, 2026).
- Editorial testing standards.