Documentation/Calc Functions/YIELDMAT

    From The Document Foundation Wiki
    Other languages:

    Function name:

    YIELDMAT

    Category:

    Financial Analysis

    Summary:

    Calculates the annual yield for a security that pays interest just once at the maturity date. YIELDMAT takes no account of the compounding of interest.

    YIELDMAT assumes a security with the following general characteristics:

    • A constant interest rate throughout the security's lifetime, with all interest to be paid as a single sum at a specified maturity date.
    • A redemption value to be paid at a specified maturity date.

    Syntax:

    YIELDMAT(Settlement; Maturity; Issue; Rate; Price[; 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 and the accumulated interest.

    Issue is a date (in quotation marks) or a date-time serial number, or a reference to a cell containing one of those types, which is the date of issue 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 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 par value. Price should exclude accrued interest earned on the security during the period between the issue and settlement dates (the so-called "clean 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 any of Maturity, Settlement, or Issue is not a valid date expression, then YIELDMAT reports a #VALUE! error.
    • If any of Maturity, Settlement, or Issue contains time components, then these are ignored by YIELDMAT.
    • If the Settlement date is on or after the Maturity date, then YIELDMAT reports an invalid argument error (Err:502).
    • If the Issue date is after the Settlement date, then YIELDMAT reports an invalid argument error (Err:502).
    • If either Rate or Price is non-numeric, then YIELDMAT reports a #VALUE! error.
    • If Rate is less than 0.0, then YIELDMAT reports an invalid argument error (Err:502).
    • If Price is less than or equal to 0.0, then YIELDMAT reports an invalid argument error (Err:502).
    • If Basis is non-numeric, then YIELDMAT reports an invalid argument error (Err:502).
    • If Basis is a non-integer value, then YIELDMAT truncates it to an integer value.
    • If, after any truncation, Basis is not equal to 0, 1, 2, 3, or 4, then YIELDMAT reports an invalid argument error (Err:502).

    Additional details:

    • It is convenient to define the following:
    [math]\displaystyle{ Years_{IM}~=~\text{YEARFRAC}(Issue;\:Maturity;\:Basis) }[/math]
    [math]\displaystyle{ Years_{IS}~=~\text{YEARFRAC}(Issue;\:Settlement;\:Basis) }[/math]
    [math]\displaystyle{ Years_{SM}~=~\text{YEARFRAC}(Settlement;\:Maturity;\:Basis) }[/math]
    YearsIM is the time between the issue date and the maturity date – the lifetime of the security; YearsIS is the time between the issue date and the settlement date; and YearsSM is the time between the settlement date and the maturity date.
    • The yield for a security that pays interest just once at the maturity date can be written as:
    [math]\displaystyle{ Yield~=~\frac{Value_{Maturity}\:-\:Value_{Settlement}}{Value_{Settlement}}\:\times\:\frac{1}{Years_{SM}} }[/math]
    or equivalently:
    [math]\displaystyle{ Yield~=~\left(\frac{Value_{Maturity}}{Value_{Settlement}}-1\right)\:\times\:\frac{1}{Years_{SM}} }[/math]
    • ValueMaturity is the sum of the face (redemption) value and the total interest accrued during the lifetime of the security. When calculating the yield per 100 currency units of face value, this can be expressed as:
    [math]\displaystyle{ Value_{Maturity}~=~100\:\times\:(1\:+\:(Rate\:\times\:Years_{IM})) }[/math]
    • ValueSettlement is related to the Price argument, which should be based on the same assumptions that are described for the value returned by the PRICEMAT function. That is:
    [math]\displaystyle{ Price~=~Value_{Settlement}~-~Interest_{Accrued} }[/math]
    • InterestAccrued is the interest earned during the period before the security was purchased, between the issue and settlement dates:
    [math]\displaystyle{ Interest_{Accrued}~=~100~\times~Rate~\times~Years_{IS} }[/math]
    • The last two equations give:
    [math]\displaystyle{ Value_{Settlement}~=~Price~+~(100~\times~Rate~\times~Years_{IS}) }[/math]
    • Putting the expressions for ValueMaturity and ValueSettlement into the formula for Yield gives:
    [math]\displaystyle{ \text{YIELDMAT}~=~ \left( \frac{1\:+\:(Rate\:\times\:Years_{IM})}{\left(\frac{Price}{100}\right)+\left(Rate\:\times\:Years_{SM}\right)} -1 \right)\:\times\:\frac{1}{Years_{SM}} }[/math]
    • The formula for YIELDMAT takes no account of the compounding of interest – care should be taken when interpreting resulting yields for periods of over one year.

    Examples:

    Formula Description Returns
    =YIELDMAT(A1; A2; A3; A4; A5; A6) where cells A1:A3 contain the dates 2019-02-15, 2025-04-13, and 2018-11-11 respectively; cells A4:A6 contain the values 5.75%, 96.27, and 0 respectively. Here the function returns the annual yield as approximately 6.50%. 0.0650027615620112
    =YIELDMAT(DATE(2019; 2; 15); DATE(2025; 4; 13); DATE(2018; 11; 11); 0.0575; 96.27) 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. YIELDMAT again returns the annual yield as approximately 6.50%. 0.0650027615620112
    =YIELDMAT("1999-02-15";"1999-04-13";"1998-11-11"; 0.061; 98.5; 3) Here the function calculates the annual yield as approximately 15.69%. 0.156888865393427

    Related LibreOffice functions:

    PRICEMAT

    YEARFRAC

    YIELD

    YIELDDISC

    ODF standard:

    Section 6.12.55, part 2

    Related (or similar) Excel functions:

    YIELDMAT