YIELDDISC Function in Google Sheets: Formula, Examples & Troubleshooting
Calculate annual yield for a discount security with YIELDDISC in Google Sheets. Audit price-based return, compare day counts, and avoid confusing yield with discount rate.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Quick answer: =YIELDDISC(settlement,maturity,price,redemption,basis) calculates annual yield for a non-interest-bearing discount security from its purchase and redemption amounts. Unlike a redemption-based discount measure, the example’s return is measured against the price paid.
Verification: NOT_TESTED_IN_GOOGLE_SHEETS. Primary Google sources were reviewed October 17, 2026. The financial arithmetic and dates below were independently calculated and are expected results, not native tests.
Syntax and argument meanings
=YIELDDISC(settlement, maturity, price, redemption, [day_count_convention])
The required arguments are the settlement and maturity dates, purchase price, and redemption amount. The optional day-count convention defaults to 0. Google documents codes 0 US NASD 30/360, 1 Actual/Actual, 2 Actual/360, 3 Actual/365, and 4 European 30/360. Use genuine dates constructed with DATE or another date conversion, rather than ambiguous text. Google YIELDDISC reference.
There is no coupon-rate or payment-frequency input. The documented purpose is a discount security with no periodic interest payment. Do not use the signature as though it modeled a stream of periodic bond coupons.
Example: buy at 98 and redeem at 100
Create this synthetic quote table:
| Cell | Label | Entry |
|---|---|---|
| B2 | Settlement | =DATE(2026,1,15) |
| B3 | Maturity | =DATE(2026,7,15) |
| B4 | Price | 98 |
| B5 | Redemption | 100 |
| B6 | Basis | 3 |
- Enter
=YIELDDISC(B2,B3,B4,B5,B6)in B8. - Display B8 as a percentage with six decimal places.
- The independently expected annual yield is approximately 4.115458%.
- Check the actual interval with
=B3-B2, displayed as a number: 181 days.
For these Actual/365 inputs, the independent calculation is:
=(B5-B4)/B4*365/(B3-B2)
The 2-unit gain divided by the 98 purchase amount gives a holding-period return of approximately 2.040816%. Multiplying by 365/181 annualizes it under this simple day-count model. It is not the same calculation as assuming the return compounds repeatedly for a full year.
Keep price and redemption on the same scale. Substituting 9,800 and 10,000 together leaves this rate unchanged; substituting only one would describe a different and likely unintended transaction.
Advanced application: separate basis and price effects
First compare bases while keeping the original price:
| Basis | Expected annual yield |
|---|---|
| 0, US 30/360 | 4.081633% |
| 2, Actual/360 | 4.059082% |
| 3, Actual/365 | 4.115458% |
Use =YIELDDISC($B$2,$B$3,$B$4,$B$5,D2) with codes 0, 2, and 3 in D2:D4. The simple non-month-end 30/360 interval is 180 days; the actual interval is 181 days.
Then hold basis 3 fixed and compare purchase prices 97, 98, and 99. Expected annual yields are approximately 6.236829%, 4.115458%, and 2.036944% respectively. Lower purchase cost for the same redemption and date interval increases the modeled return.
This sensitivity table isolates assumptions. It does not imply that those prices are available in a market or that a higher modeled yield is preferable after considering risk and other terms.
Why DISC can give a different percentage
For the original example, a redemption-based annual discount calculation is 2/100*365/181, approximately 4.033149%. YIELDDISC’s price-based model uses 98 in that denominator instead of 100. The difference is not merely display rounding.
Google identifies DISC separately as a discount-rate function and PRICE/YIELD as functions for securities with periodic interest. Choose the model before comparing percentages, and label the output precisely.
Errors and troubleshooting
- The result is 0.041…: that is the decimal rate. A percentage display renders it as about 4.115458%.
- The result seems too high: check price/redemption scale, the actual date interval, and whether the basis was left at default 0.
- The price is zero: the independent return calculation would divide by zero. Use meaningful positive purchase-price assumptions and investigate the data rather than masking an error with zero.
- Dates are text or reversed: construct and inspect actual dates; ensure maturity follows settlement.
- A coupon-paying security is being modeled: this signature has no coupon rate. Review the separate periodic-interest function instead.
- Boundary or coercion questions: the reviewed source does not establish every invalid-input error code, fractional-date rule, or month-end adjustment. The examples avoid those cases and do not import another spreadsheet’s behavior.
Support and references
Google currently documents YIELDDISC. Introduction, retirement, and last-functioning-version dates are unknown from these sources. The guide supplies synthetic calculations, not investment advice, and makes no cross-engine compatibility guarantee.
Reviewed October 17, 2026: Google YIELDDISC and Google DATE.