Excel YIELDDISC function
The YIELDDISC function returns the annual yield for a discounted security.
Syntax
YIELDDISC(settlement, maturity, pr, redemption, [basis])
Arguments
- 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.
| Basis | Date System |
| o or omitted | US (NASD) 30/360 |
| 1 | Actual/actual |
| 2 | Actual/360 |
| 3 | Actual/365 |
| 4 | European 30/360 |
Remarks
Return Value
It returns a numeric value.
Example
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.
=YIELDDISC(C5,C6,C7,C8,C9)
2. Then change the cell format to percentage according to your needs.
Related Functions
Excel YIELD function
The YIELD function returns the annual yield for a discounted security.
Excel YIELDMAT function
The YIELDMAT function returns the annual yield of a security that pays interest at maturity.
The Best Office Productivity Tools
Kutools for Excel - Helps You To Stand Out From Crowd
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...
Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)
- 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.
