YIELDDISC Function in Microsoft Excel: Syntax, Examples & Troubleshooting
Calculate annual yield on a discounted security with Excel YIELDDISC. Reconcile price-based return, compare it with a discount quotation and test basis and scaling.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Quick answer: YIELDDISC returns the annualized yield of a discounted security from its price and redemption value. The gain is measured relative to the price paid. This differs from a discount rate measured relative to redemption.
Syntax and arguments
=YIELDDISC(settlement, maturity, pr, redemption, [basis])
The first four arguments are required. pr is the price per 100 face value and redemption is the redemption amount on that same scale. Basis is optional. The function models the quoted discounted security, not an arbitrary list of intermediate cash flows.
Use real date values or DATE formulas for settlement and maturity. Settlement is the purchase settlement date, which is not necessarily the original issue date. The optional basis selects 0 or omitted = US (NASD) 30/360; 1 = actual/actual; 2 = actual/360; 3 = actual/365; 4 = European 30/360. Microsoft documents that settlement, maturity and basis are truncated to integers; time-of-day fractions are not used for intraday accrual.
The examples use January 15 through April 15, 2026, an independently checked 90-day interval. Actual/360 therefore gives a year fraction of 90/360 = 0.25. Enter =DATE(2026,1,15) in B2, =DATE(2026,4,15) in B3 and 2 in B6. Using fixed dates rather than TODAY makes the expected results reproducible.
Worked example: yield on a price of 98
- Enter 98 in B4 (price) and 100 in B5 (redemption).
- In D2 enter
=YIELDDISC(B2,B3,B4,B5,B6). - Expected result: approximately 0.08163265306, displayed as 8.163265306%.
The independent calculation is (100-98)/98*360/90. The holding-period gain is 2/98, about 2.040816327%. Multiplying by four annualizes that return on the actual/360 basis.
Second example: a price closer to redemption
=YIELDDISC(B2,B3,99,100,2) is expected to return approximately 0.0404040404, or 4.04040404%. The gain is now 1 on an invested price of 99. The rate is not exactly 4%, because 99 rather than 100 is the denominator.
With the original price of 98 but basis 3, the expected rate is approximately 8.276643991%, calculated as 2/98*365/90. That difference follows annualization convention rather than a changed cash receipt.
Advanced application: compare like-for-like quotations
For price 98 and redemption 100 over the 90-day actual/360 interval, a redemption-based discount quotation is 2/100*4 = 8%. YIELDDISC gives about 8.163265306%, using price 98. Both describe the same cash flows but use different denominators.
The holding-period return can be recovered from the yield by multiplying by 90/360. This gives approximately 2.040816327%. Multiplying that holding return by the price 98 recovers the gain 2. Those two checks expose a mistaken redemption denominator or a percentage entered at the wrong scale.
If the actual position is 25000 face value, price 98 implies investment 24500 and gain 500. The ratio 500/24500*4 gives the same annualized yield. Scale both purchase and redemption consistently; the function’s quote convention remains per 100.
This simple annualized rate does not assume that the proceeds can be reinvested at the same return each quarter. A compounded annual scenario requires a separate reinvestment assumption.
Troubleshooting and edge cases
Microsoft documents #VALUE! for invalid date inputs and #NUM! for settlement greater than or equal to maturity or basis outside 0 through 4. It also documents #NUM! when price or redemption is zero or negative.
A percent-formatted result and its stored decimal are the same rate: 8% is 0.08. Do not multiply a rate by 100 and then apply percentage formatting again. Keep a price per 100 separate from the entire investment amount, and preserve full precision until the reporting step.
Actual/actual and the two 30/360 variants have their own date rules; one ordinary 90-day example does not certify every leap-year or month-end boundary. Match the contract’s convention rather than choosing whichever basis produces the most attractive number. The examples explain calculation mechanics and exclude transaction costs, taxes and advice about a real investment.
Compatibility and verification
Microsoft’s reference checked on October 17, 2026 lists: Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel 2024 Excel 2024 for Mac Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2016. This current support list does not establish the first release. The introduction version and last functioning version have not been established from the reviewed documentation. No retirement is asserted.
Example verification: Expected numerical results are independently calculated; text and shape expectations follow the documented contract. These examples have not been tested in Microsoft Excel. No screenshot or native execution is claimed.
Related guides and source
DATE in Excel | DAYS in Excel | RATE in Excel | XIRR in Excel
- Microsoft YIELDDISC reference, retrieved October 17, 2026.