MS Excel: YIELDDISC function for annual yield on discount securities

In fixed-income investing, some securities—like Treasury bills (T-bills) and commercial paper—are sold at a discount and do not pay periodic interest. Instead, investors earn the difference between the purchase price and the face value at maturity.

The YIELDDISC function in Excel calculates the annual yield on these discount (zero-coupon) securities, offering a straightforward way to evaluate their returns.

The YIELDDISC function returns the annualized yield for a security purchased at a discount, based on its settlement and maturity dates and its redemption and purchase price.

Syntax

=YIELDDISC(settlement, maturity, pr, redemption, basis)

Argument Details:

Argument	Description
settlement	The date you purchase the security
maturity	The date when the security matures
pr	Purchase price of the security
redemption	Face value to be received at maturity (usually 100)
basis	(Optional) Day count basis (0 = US 30/360, 1 = actual/actual, etc.)

Returns: The annual yield, as a decimal (e.g., 0.045 for 4.5%)

Example: T-Bill Yield Calculation

You purchase a Treasury bill on January 1, 2024, for $97, and it matures on June 30, 2024, with a face value of $100.

=YIELDDISC(DATE(2024,1,1), DATE(2024,6,30), 97, 100, 1)

Result: 0.0618 or 6.18% annual yield

That means you're effectively earning a 6.18% return per year based on the discount and time until maturity.

When to Use the YIELDDISC Function

Use Case	Why It’s Useful
Pricing Treasury Bills	These are zero-coupon instruments sold at discount
Valuing Commercial Paper	Another short-term debt instrument with no coupons
Yield comparisons across instruments	Helps assess return across bonds and T-bills
Short-term investment evaluation	Especially relevant for traders and treasurers

Behind the Formula: How YIELDDISC Works

The yield is calculated using:

Days in Year Days

This approach annualizes the yield based on the day-count convention selected.

About the basis Argument

Basis	Description
0	US (NASD) 30/360
1	Actual/actual
2	Actual/360
3	Actual/365
4	European 30/360

The basis affects how Excel calculates the number of days in a year and between dates. Use basis 1 for most U.S. Treasury bill yield comparisons.

Related Functions

Function	Description
PRICE	Returns price of a bond with coupons
PRICEDISC	Calculates price of a discount bond (inverse of YIELDDISC)
YIELD	Calculates yield for coupon-paying bonds
YIELDMAT	Yield of bond that pays interest at maturity
TBILLPRICE	Price of a Treasury bill
TBILLYIELD	Yield of a Treasury bill

Use YIELDDISC when there are no coupon payments, just a discounted purchase and a redemption.

Tips and Best Practices

Tip	Reason
Ensure settlement < maturity	Otherwise Excel returns #NUM!
Use consistent units (usually redemption = 100)	Ensures yield reflects a percentage
Always check day count basis	Impacts the accuracy of yield, especially for short terms
Combine with PRICEDISC to test reverse calculations	Helps validate your Excel models

Summary Table

Feature	Details
Function Name	YIELDDISC
Purpose	Calculates annual yield for a discount (zero-coupon) security
Coupon Payments	❌ None
Output	Annual yield as decimal
Uses Day Count Basis	✅ Yes
Common Instruments	T-bills, commercial paper

Final Thoughts

The YIELDDISC function is a must-have tool for anyone evaluating short-term, zero-coupon securities. It delivers a standardized way to compare investment returns across discount-based instruments.

Whether you're a:

• Portfolio manager,

• Fixed income analyst,

• Government securities dealer,

…you can rely on YIELDDISC to provide fast and accurate yield calculations.

Combine YIELDDISC with PRICEDISC and TBILLYIELD to build a complete Treasury bill analysis toolkit in Excel.