YIELDDISC

The YIELDDISC function returns the effective annual interest rate for a security that is sold at a discount to redemption value and pays no interest.

YIELDDISC(settle, maturity, price, redemption, days-basis)

settle: A date/time value or date string representing the trade settlement date, usually one or more days after the trade date.

maturity: A date/time value or date string representing the date when the security matures. maturity must be after the date specified for settle.

price: A number value representing the cost of the security per $100 of par value. price is calculated as purchase price / face value * 100 and it must be greater than 0.

redemption: A number value representing the redemption value per $100 of par value. redemption is calculated as redemption value / face value * 100 and it must be greater than 0. Often, it is 100, meaning that the security’s redemption value is equal to its face value.

days-basis: An optional modal value specifying the number of days per month and days per year (days-basis convention) used in the calculations.

30/360 (0 or omitted): 30 days in a month, 360 days in a year, using the NASD method for dates falling on the 31st of a month.

actual/actual (1): Actual days in each month, actual days in each year.

actual/360 (2): Actual days in each month, 360 days in a year.

actual/365 (3): Actual days in each month, 365 days in a year.

30E/360 (4): 30 days in a month, 360 days in a year, using the European method for dates falling on the 31st of a month.

Examples
Suppose you are considering the purchase of a hypothetical discount security. The security settles May 1, 2009 (settle), matures at 100 per $100 of face value (redemption is 100) on June 30, 2015 (maturity). The security is being offered at 65.98 (price). =YIELDDISC("05/01/2009", "06/30/2015", 65.98, 100, 0) returns approximately 8.36502410155835%, which represents the annual yield based on the assumptions given and assuming interest is calculated based on the 30/360 days interest convention. =YIELDDISC("05/01/2009", "06/30/2015", 65.98, 100, 1) returns approximately 8.3639092604964%, which represents the annual yield based on the assumptions given and assuming interest is calculated based on actual days.

Examples

Suppose you are considering the purchase of a hypothetical discount security. The security settles May 1, 2009 (settle), matures at 100 per $100 of face value (redemption is 100) on June 30, 2015 (maturity). The security is being offered at 65.98 (price).

=YIELDDISC("05/01/2009", "06/30/2015", 65.98, 100, 0) returns approximately 8.36502410155835%, which represents the annual yield based on the assumptions given and assuming interest is calculated based on the 30/360 days interest convention.

=YIELDDISC("05/01/2009", "06/30/2015", 65.98, 100, 1) returns approximately 8.3639092604964%, which represents the annual yield based on the assumptions given and assuming interest is calculated based on actual days.

See alsoPRICEDISC YIELD YIELDMAT

Helpful?

Formulas and Functions Help

YIELDDISC