When you are architecting a financial model for a discounted security (like a treasury bill) that pays absolutely no periodic interest but mathematically guarantees a specific redemption value at maturity, standard yield formulas are catastrophically inaccurate. To force the Microsoft Excel engine to calculate the absolute annualized yield of this specific asset class based on its heavily discounted purchase price, you must use the YIELDDISC function.
Understanding the YIELDDISC Architecture
The YIELDDISC (Yield for a Discounted Security) function is a rigid financial engine. It ingests the exact date you purchased the security, the date it expires, the price you paid, and its final redemption value, and algorithmically outputs the true annualized yield percentage.
The syntax requires exactly four mandatory arguments and one optional argument: =YIELDDISC(settlement, maturity, pr, redemption, [basis])
- settlement: The absolute date the buyer takes possession of the security.
- maturity: The absolute final date the security expires and pays out.
- pr: The exact mathematical price you paid per $100 of face value (this will always be a discounted number, e.g., $95.50).
- redemption: The exact payout value per $100 of face value at maturity (almost universally 100).
- [basis]: An optional integer (0 to 4) dictating the specific day-count methodology. If omitted, Excel defaults to the US (NASD) 30/360 system (Basis 0).
Executing the Financial Calculation
Imagine you are analyzing a zero-coupon municipal bond. The settlement date is March 1, 2024 (A2). The absolute maturity date is September 1, 2024 (A3). You are paying exactly $96.00 per $100 of face value (A4). The redemption value is a pristine $100 (A5). You require the highly precise Actual/365 day-count basis (A6, value 3).
To calculate the true annualized yield, click an empty cell and type:
=YIELDDISC(A2, A3, A4, A5, A6)The exact millisecond you press Enter, the Excel engine executes the calculus. It determines the physical number of days the capital is locked up, calculates the $4.00 absolute spread against the $100 redemption, and outputs the annualized yield percentage (e.g., 8.27%).
CRITICAL ARCHITECTURAL WARNING: The pr (price) argument must mathematically be less than the redemption argument. If you input a price higher than the redemption value, the engine will violently crash and output a #NUM! error, because a discounted security cannot be sold at a premium.