The Math of the Zero-Coupon Return
If you purchase a standard corporate bond, calculating your annual return (your yield) is easy: you simply look at the contract to see what percentage of interest the corporation promises to deposit into your bank account every year. However, if you purchase a “Zero-Coupon” bond—like a United States Treasury Bill—there are no annual interest payments. The government simply sells you the bond at a massive discount today, and pays you the full face value when the bond eventually matures.
Because there are no regular interest payments, calculating the true annual yield is incredibly difficult. If you buy a Treasury Bill for $95,000 today and the government promises to pay you $100,000 in exactly 214 days, you have guaranteed a flat $5,000 profit. But what is the annual percentage yield of that profit? Is it better than leaving that $95,000 in a savings account that pays a 4.5% annual interest rate?
To mathematically convert a flat, discounted cash profit into a highly accurate, annualized percentage yield, you must use the YIELDDISC (Yield of a Discounted Bond) function in Microsoft Excel.
Understanding the Syntax
The YIELDDISC function requires you to feed it four specific pieces of data about the transaction.
=YIELDDISC(settlement, maturity, pr, redemption, [basis])
- settlement: The exact date you originally purchased the bond at the discounted price.
- maturity: The exact date the bond officially expires and you receive the final payout.
- pr (Price): The discounted price you paid, expressed per $100 of face value.
- redemption: The final payout amount per $100 of face value (this is almost always exactly
100). - [basis]: (Optional) The day-count methodology used by the financial market. For US Treasury Bills, this is usually
2(Actual/360).
Example 1: Calculating the Annual Yield
Assume it is March 1, 2024. You are an institutional investor, and you just purchased a massive block of Treasury Bills on the secondary market. The bills are scheduled to mature on October 15, 2024.
The total face value of the bills is $2,000,000, but you managed to negotiate a discounted purchase price of exactly $1,920,000. Your Board of Directors demands to know the exact annualized yield on this investment.
Before you run the formula, you must calculate the Price (pr) variable. You take the price paid ($1,920,000) and divide it by the face value ($2,000,000). The result is 0.96. Multiply by 100, and your Price variable is 96.00.
Let’s map out the variables cleanly in a spreadsheet:
- Cell A1 (Settlement):
=DATE(2024, 3, 1) - Cell A2 (Maturity):
=DATE(2024, 10, 15) - Cell A3 (Price):
96.00 - Cell A4 (Redemption):
100 - Cell A5 (Basis):
2
To reveal the annualized yield, click on cell B1 and type:
=YIELDDISC(A1, A2, A3, A4, A5)
How this works:
- Excel calculates the exact physical number of days between March 1st and October 15th (228 days).
- It analyzes the gap between the discounted price (96.00) and the final payout (100).
- It mathematically inflates that short-term profit margin to reflect a full, 360-day annualized financial year.
- It instantly outputs 0.06578.
If you highlight cell B1 and click the “%” button on the Excel toolbar, it will format the number beautifully as 6.58%. You can confidently report to the Board of Directors that the Treasury Bill transaction is generating an annualized yield of 6.58%, completely crushing the 4.5% rate offered by standard corporate savings accounts.
The Error Traps
The YIELDDISC function is mathematically rigid. If it returns a #NUM! error, verify the following:
- Chronological Failure: The Settlement date must be strictly older than the Maturity date. Time cannot flow backward.
- Negative Value: Both the Price (pr) and the Redemption value must be positive numbers greater than zero.
By mastering the YIELDDISC function, you can instantly compare the profitability of short-term, zero-coupon government debt against any other investment vehicle on the market.