Documentation/Calc Functions/YIELDDISC

    From The Document Foundation Wiki
    Other languages:

    Function name:

    YIELDDISC

    Category:

    Financial Analysis

    Summary:

    Calculates the annual yield for a security that is issued at less than face value (discounted), pays no interest, and pays the investor its face value at maturity. Such a financial instrument is sometimes known as a discount security.

    Syntax:

    YIELDDISC(Settlement; Maturity; Price; Redemption[; Basis])

    Returns:

    Returns a real number, which is the annual yield for a security with the specified characteristics. The number returned is a percentage expressed as a decimal fraction (for example, the returned value 0.075 would represent 7.5%).

    Arguments:

    Settlement is a date (in quotation marks) or a date-time serial number, or a reference to a cell containing one of those types, which specifies when a trade is "settled". On this date the transfer of the security to the buyer is completed, and the buyer makes the appropriate payment.

    Maturity is a date (in quotation marks) or a date-time serial number, or a reference to a cell containing one of those types, which specifies when the security matures. On maturity, the holder is paid the redemption value of the security.

    Price is a positive real number, or a reference to a cell containing that number, which is the price of the security per 100 currency units of face value.

    Redemption is a positive real number, or a reference to a cell containing that number, which is the redemption value of the security per 100 currency units of face value . Redemption should be in the same currency unit as Price.


    Basis is an integer in the range 0 to 4, or a reference to a cell containing that integer, which indicates how the year is to be calculated. For more background information, visit Wikipedia's Day count convention page.

    Basis Calculation
    0 or missing US method (NASD), 12 months of 30 days each
    1 Exact number of days in months, exact number of days in year.
    2 Exact number of days in month, year has 360 days.
    3 Exact number of days in month, year has 365 days.
    4 European method, 12 months of 30 days each.


    • If either Maturity or Settlement is not a valid date, then YIELDDISC reports a #VALUE! error.
    • If either Maturity or Settlement contain time components, then these are ignored by YIELDDISC.
    • If the Settlement date is on or after the Maturity date, then YIELDDISC reports an invalid argument error (Err:502).
    • If either Price or Redemption is non-numeric, then YIELDDISC reports a #VALUE! error.
    • If either Price or Redemption is less than or equal to 0.0, then YIELDDISC reports an invalid argument error (Err:502).
    • If Basis is non-numeric, then YIELDDISC reports an invalid argument error (Err:502).
    • If Basis is a non-integer value, then YIELDDISC truncates it to an integer value.
    • If, after any truncation, Basis is not equal to 0, 1, 2, 3, or 4, then YIELDDISC reports an invalid argument error (Err:502).

    Additional details:

    • The formula for YIELDDISC is:
    [math]\displaystyle{ \text{YIELDDISC}~=~\frac{Redemption - Price}{Price \times \text{YEARFRAC}(Settlement;\:Maturity;\:Basis)} }[/math]
    • The discount rate calculated by the DISC function and the yield calculated by the YIELDDISC function are not the same. However, assuming that the same arguments are passed to both functions, the two values returned are related by the formula:
    [math]\displaystyle{ \text{DISC}~=~\frac{Price}{Redemption}\times\text{YIELDDISC} }[/math]

    Examples:

    Formula Description Returns
    =YIELDDISC(A1; A2; A3; A4) where cells A1 and A2 contain the dates 2022-01-25 and 2022-11-15 respectively, while cells A3 and A4 contain the numbers 97 and 100 respectively. Here the function calculates the annual yield for a discount security with the specified characteristics. No value is specified for the Basis argument and so the default value of 0 (12 months of 30 days each) is applied. The function returns the value 0.0383931745467471 (3.84%). 0.0383931745467471
    =YIELDDISC(DATE(2022; 1; 25); DATE(2022; 11; 15); 97; 100; 3) Here the function calculates the annual yield for the same security as in the previous example, but utilizes the DATE function instead of typing formatted date values and uses the value 3 for the Basis argument. The function returns the value 0.0383968020197769 (3.84%). 0.0383968020197769
    =YIELDDISC("2001-01-25"; "2001-11-15"; 105.75; 110.6; 1) Here the function calculates the annual yield for a discount security with the specified characteristics. The function returns the value 0.0569386146893745 (5.69%). 0.0569386146893745

    Related LibreOffice functions:

    DISC

    PRICEDISC

    YEARFRAC

    YIELD

    YIELDMAT

    ODF standard:

    Section 6.12.54, part 2

    Related (or similar) Excel functions:

    YIELDDISC