DISC Function in Google Sheets: Formula, Examples & Troubleshooting
Calculate a security discount rate with DISC in Google Sheets. Use explicit settlement dates, compare day-count bases, and distinguish discount from purchase-price yield.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Quick answer: =DISC(settlement,maturity,price,redemption,basis) returns a security’s discount rate from its purchase and redemption amounts. Specify the day-count basis when you need an auditable comparison.
Verification: NOT_TESTED_IN_GOOGLE_SHEETS. First-party Google references were reviewed October 17, 2026. Date intervals and rate examples were independently calculated; no native financial-function execution is claimed.
Syntax and arguments
=DISC(settlement, maturity, price, redemption, [day_count_convention])
| Argument | Meaning |
|---|---|
settlement |
Delivery date to the purchaser after issuance |
maturity |
Redemption date |
price |
Purchase price |
redemption |
Amount received at redemption |
day_count_convention |
Optional basis code; default 0 |
The documented basis codes are 0 for US NASD 30/360, 1 for Actual/Actual, 2 for Actual/360, 3 for Actual/365, and 4 for European 30/360. The two 30/360 methods differ in their end-of-month adjustments. Google DISC reference.
Use genuine dates, preferably constructed with DATE, rather than ambiguous text. Keep price and redemption on the same scale: a price quote of 98 should be paired with redemption 100, not with a whole holding’s redemption of 10,000.
Example: an explicit Actual/365 calculation
Enter this synthetic security quote:
| Cell | Meaning | Entry |
|---|---|---|
| B2 | Settlement | =DATE(2026,1,15) |
| B3 | Maturity | =DATE(2026,7,15) |
| B4 | Price | 98 |
| B5 | Redemption | 100 |
| B6 | Day-count basis | 3 |
- Enter
=DISC(B2,B3,B4,B5,B6)in B8. - Format B8 as a percentage with six decimal places.
- The independently calculated expected result is approximately 4.033149%.
- Enter
=B3-B2in B10 and format it as a number. The expected actual-day count is 181.
The mathematical audit for this Actual/365 example is:
=(B5-B4)/B5*365/(B3-B2)
The discount amount is 2, or 2% of redemption. Annualizing that discount over 181 days gives 0.02*365/181, approximately 0.0403314917. This is the independently evaluated discount-rate model for the stated inputs, not a claim about Google’s implementation internals.
The date construction is intentional. DATE(2026,1,15) supplies separate numeric components. A bare expression such as 1/15/2026 inside a formula is arithmetic division, not a reliable way to communicate January 15.
Advanced application: compare bases and horizons
Create a basis comparison in D1:E4. Put basis codes 0, 2, and 3 in D2:D4. In E2 enter =DISC($B$2,$B$3,$B$4,$B$5,D2) and fill down.
| Basis | Expected rate |
|---|---|
| 0, US 30/360 | 4.000000% |
| 2, Actual/360 | 3.977901% |
| 3, Actual/365 | 4.033149% |
For these non-month-end dates, the 30/360 interval is 180 convention days. The actual interval is 181 days. That difference, and the chosen year denominator, explains why the rates are not identical. This example deliberately does not cover February-end or 31st-day adjustment rules.
If you change only maturity to =DATE(2026,10,15), the actual interval becomes 273 days. With basis 3 and the same price and redemption, the expected discount rate is approximately 2.673993%. Receiving the same 2-unit discount over a longer period implies a lower annualized discount rate under this model.
For a scale check, replacing 98 and 100 with 9,800 and 10,000 leaves the ratio, and therefore this expected rate, unchanged. Scale both inputs together; do not mix a unit quote and a position total.
Discount rate is not purchase-price yield
The example’s discount calculation divides the 2-unit difference by the redemption amount, 100. A simple annualized return on the 98 purchase amount would instead use 2/98*365/181, approximately 4.115458%. The different denominator explains the difference; the two labels should not be swapped.
Google separately documents YIELDDISC for annual yield on a discount security. Review its own contract before choosing it. DISC is not a live quote service and does not include an unstated fee, tax, reinvestment, or credit-risk model. This worksheet is an educational calculation rather than a recommendation to buy a security.
Errors and troubleshooting
- Wrong date interval: verify that B2 and B3 are dates and that maturity follows settlement. Display formatting alone does not prove that imported text was parsed as intended.
- A surprising basis difference: show the basis as an explicit input. Leaving it out means Google’s documented default of 0, not Actual/365.
- The percentage is off by a factor of 100: inspect both the stored decimal and its display format before manually multiplying the result.
- Month-end or leap-year discrepancies: the selected convention matters. This guide does not generalize the simple-date audit into a complete implementation of all five bases.
- An error appears: check numeric amounts, a valid documented basis code, and sensible ordered dates. Exact Google error labels, coercion, and fractional-date truncation are not established by the reviewed reference; no cross-product error table is assumed.
Support and references
Google currently documents DISC. The reviewed evidence does not establish its introduction, retirement, or last-functioning version. Results transferred to other spreadsheet engines require separate verification.
Reviewed October 17, 2026: Google DISC and Google DATE.