YIELDDISC Function
Calculates the annual yield of a discounted security.
Syntax
Parameters
| Parameter | Description | Required |
|---|---|---|
| settlement | Date the security is purchased. | Required |
| maturity | Date the security expires. | Required |
| pr | Price per $100 face value. | Required |
| redemption | Redemption value per $100 face value. | Required |
| basis | Day-count convention (0 = US 30/360, default). | Optional |
Basic Example
Yield of a discounted Treasury bill
=YIELDDISC(DATE(2024,1,1), DATE(2024,12,31), 95, 100)A T-bill priced at $95 that redeems at $100 in one year has an annual yield of about 5.41%.
Advanced Examples
Example 1: With actual/360 basis
Money marketUse basis 2 for actual/360
=YIELDDISC(DATE(2024,1,1), DATE(2024,12,31), 95, 100, 2)How YIELDDISC Works
YIELDDISC calculates the annual yield using the formula (redemption - pr) / pr * (days per year / days to maturity). It is used for zero-coupon and short-term discount instruments.
Important Notes & Limitations
Settlement must be before maturity.
Price and redemption are per $100 face value.
Basis affects the day count and year length.
Common Errors & Fixes
#NUM! errorInvalid dates, price, or redemption.Fix: Verify all inputs.
#VALUE! errorDates are not recognized.Fix: Use DATE function or valid date serial numbers.
Download Practice File
YIELDDISC
yielddiscPractice YIELDDISC with Real Data
Download a sample CSV file with pre-populated data and practice exercises for the YIELDDISC function. Works in both Excel and Google Sheets.
File format: CSV (comma-separated values) - opens in Excel, Google Sheets, and all spreadsheet apps