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 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.
Notes
The currency shown in this function result depends on your Language and Region settings (in System Preferences in macOS and in Settings in iOS and iPadOS), or on your Time Zone and Region settings in iCloud Settings.
Examples |
|---|
Suppose you are considering the purchase of a hypothetical discount security. The security settles on 1 May 2009 (settle), matures at 100 per $100 of face value (redemption is 100) on 30 June 2015 (maturity). The security is being offered at 65.98 (price). =YIELDDISC(“01/05/2009”, “30/06/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(“01/05/2009”, “30/06/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. |