DISC
The DISC function returns the annual discount rate of a security that pays no interest and is sold at a discount to its redemption value.
DISC(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.
Notes
The currency shown in this function result depends on your Language & Region settings (in System Preferences in macOS and in Settings in iOS and iPadOS), or on your Time Zone & Region settings in iCloud Settings.
Example |
|---|
Suppose you are considering the purchase of a hypothetical security. Your purchase will settle on 1 May 2009 (settle), the security will mature on 30 June 2015 (maturity), the purchase price is £67.14 per £100 of face value (price), the face value of the security (redemption) is 100 per £100 of face value, and interest is calculated based on the 30/360 days basis (days-basis). =DISC(“01/05/2009”, “30/06/2015”, 67.14, 100, 0) returns 5.33%, the annual discount rate implied by the pricing of this security, assuming the 30/360 days basis interest convention is utilised. |