Excel YIELDDISC function
The YIELDDISC function returns the annual yield for a discounted security.
YIELDDISC(settlement, maturity, pr, redemption, [basis])
- Settlement (required): The settlement date of the security. It is the date after the issue date when the security is purchased;
- Maturity (required): The maturity date of the security. It is the date on which the security expires;
- Pr (required): The security’s price per $100 face value;
- Redemption (required): The security’s redemption value per $100 face value;
- Basis (optional): An integer number (0, 1, 2, 3 or 4) specifying the day basis to be used. Default is 0.
|| Date System
| o or omitted
|| US (NASD) 30/360
|| European 30/360
1. In Excel, dates can be used in calculations as they are stored as sequential numbers. By default, 1/1/1900 is serial number 1, so 12/1/2021 is serial number 40877 because it is 40876 days after 1/1/1900;
2. Dates provided for “settlement” and “maturity” should be entered:
-- As references to cells containing dates;
-- By using the DATE function;
-- As results returned from other formulas.
3. If settlement, maturity, frequency, and basis contain decimal, they will be truncated to integers;
4. The #NUM! error occurs when either of the following conditions is met:
-- The settlement date is greater than or equal to the maturity date;
-- Any of the supplied arguments “rate”, “pr”, “redemption”, “frequency” or “basis” is an invalid number.
Such as: "rate" < 0; "pr" ≤ 0; "redemption" ≤ 0; "frequency" is not equal to 1, 2 or 4; or [basis] is any number other than 0, 1, 2, 3 or 4.
5. The #VALUE! error occurs when either of the following conditions is met:
-- “Settlement” and “maturity” dates are invalid;
-- Any of the supplied arguments are non-numeric.
It returns a numeric value.
As shown in the screenshot below, a security is purchased on January 15, 2021 for $98.00 per $100 face value, and it will expire on December 1, 2021. The security’s redemption value is $100. To calculate the annual yield for this discounted security using the actual/actual day count basis (basis=1), you can do as follows.
1. Select a blank cell (here I select F5), enter the following formula and press Enter to get the result.
2. Then change the cell format to percentage according to your needs.
The Best Office Productivity Tools
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.
Please leave your comments in English