Documentation/Calc Functions/YIELD

    From The Document Foundation Wiki
    Other languages:

    Function name:

    YIELD

    Category:

    Financial Analysis

    Summary:

    Calculates the annual yield for a fixed interest security.

    YIELD assumes a security with the following general characteristics:

    • Regular interest / coupon payments (1, 2, or 4 per year).
    • A constant interest / coupon rate.
    • A redemption value to be paid at a specified maturity date.

    Syntax:

    YIELD(Settlement; Maturity; Rate; Price; Redemption; Frequency[; 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.

    Rate is a non-negative real number (expressed as a percentage, such as 2.5%, or a decimal fraction, such as 0.025), or a reference to a cell containing that number, which is the annual interest rate (coupon rate) of the security.

    Price is a positive real number, or a reference to a cell containing that number, which is the price (in currency units) 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 (in currency units) of the security per 100 currency units of face value.

    Frequency is a positive integer, or a reference to a cell containing that integer, which is the number of interest payments (coupon payments) per year. Valid values are 1 (annual), 2 (semiannual), or 4 (quarterly).


    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 Settlement or Maturity is not a valid date expression, then YIELD reports a #VALUE! error.
    • If either Settlement or Maturity contains time components, then these are ignored by YIELD.
    • If the Settlement date is on or after the Maturity date, then YIELD reports an invalid argument error (Err:502).
    • If any of Rate, Price, Frequency, or Redemption is non-numeric, then YIELD reports a #VALUE! error.
    • If Basis is non-numeric, then YIELD reports an invalid argument error (Err:502).
    • If either Basis or Frequency is a non-integer value, then YIELD truncates it to an integer value.
    • If Rate is less than 0.0, then YIELD reports an invalid argument error (Err:502).
    • If either Price or Redemption is less than or equal to 0.0, then YIELD reports an invalid argument error (Err:502).
    • If, after any truncation, Frequency is not equal to 1, 2, or 4, then YIELD reports an invalid argument error (Err:502).
    • If, after any truncation, Basis is not equal to 0, 1, 2, 3, or 4, then YIELD reports an invalid argument error (Err:502).
    • For some combinations of argument values, it is possible for YIELD to fail to converge to a solution. In such cases, YIELD reports an invalid argument error (Err:502).

    Additional details:

    • The description of the PRICE function provides details of the equation that relates the so-called clean price" for a security of this type to a constant discount / forecast yield rate. However, there is no general way to re-arrange this equation to determine the yield for a given price. YIELD uses the PRICE function, and iterates (up to 100 times) to find the yield that would give the price value specified as an argument.

    Examples:

    Formula Description Returns
    =YIELD(B1; B2; B3; B4; B5; B6; B7) where cells B1 and B2 contain the dates 2020-02-15 and 2028-12-31 respectively; cells B3:B7 contain the values 5.75%, 94.99, 100, 2, and 0 respectively. Here YIELD returns the annual yield as 6.50%. 0.0650050572049512
    =YIELD(DATE(2020; 2; 15); DATE(2028; 12; 31); 0.0575; 94.99; 100; 2) 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. Also, no value is specified for the Basis argument and so the default value of 0 (12 months of 30 days each) is applied. YIELD again returns the annual yield as 6.50%. 0.0650050572049512
    =YIELD("1999-02-15"; "2007-11-15"; 0.0575; 114.10; 110.5; 4; 3) Here YIELD returns the annual yield as 4.75%. 0.047505981507864

    Related LibreOffice functions:

    PRICE

    YIELDDISC

    YIELDMAT

    ODF standard:

    Section 6.12.53, part 2

    Related (or similar) Excel functions:

    YIELD