YIELDDISC Function in Excel

In this article, you will learn about the YIELDDISC function, the formula syntax and usage of the function in Microsoft Excel

YIELDDISC Function in Excel

The YIELDDISC function in Excel returns the annual yield for a discounted security that is, the security is non-interest-bearing security, that is issued at a discount but that matures at face value.


= YIELDDISC(settlement, maturity, pr, redemption, [basis])


  • Settlement    – The security’s settlement date.
  • Maturity – The security’s maturity date.
  • Pr   – The security’s price per $100 face value.
  • Redemption – The security’s redemption value per $100 face value.
  • Basis – The type of day count basis to use (optional).

Usage Notes and Possible Errors

  • As we know Excel stores dates as sequential series of numbers, i.e., January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 as it is 39,448 days after January 1, 1900.
  • The settlement date is the date of purchase of the coupon, such as a bond.
  • The maturity date is the date when the coupon expires.
  • Settlement, maturity, and basis are truncated to integers.
  • When the settlement or maturity is not a valid date, the YIELDDISC function returns the #VALUE! error value.
  • In case of pr ≤ 0 or redemption ≤ 0, YIELDDISC returns the #NUM! error value.
  • When basis < 0 or basis > 4, YIELDDISC returns the #NUM! error value.
  • If settlement ≥ maturity, YIELDDISC returns the #NUM! error value.

How to use the YIELDDISC function in Excel?

Using this function in a WS is simple; all you need to do is enter the function as a formula of the cell in the formula bar.

Take a look at the given example


Enter the data in the respective columns and enter the YIELDDISC formula.

Formula: =YIELDDISC(A2,B2,C2,D2,E2,F2,G2)

Here, A2 refers to the cell name or the cell address. You will get the result in the Yield Disc column.