Documentation/Calc Functions/OPT PROB INMONEY

    From The Document Foundation Wiki
    Other languages:

    Function name:

    OPT_PROB_INMONEY

    Category:

    Financial Analysis

    Summary:

    Calculates the probability that an option will end up between two barrier levels at maturity and in-the-money.

    Syntax:

    OPT_PROB_INMONEY(Spot; Volatility; Drift; Maturity; Lower barrier; Upper barrier[; Strike[; Put/Call]])

    Returns:

    Returns a real number in the range [0, 1] which is the probability that an option with the specified characteristics will end up in-the-money at maturity.

    Arguments:

    Spot is a positive real number, or a reference to a cell containing that number, which is the current market price of the underlying asset.

    Volatility is a positive 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 estimated annual volatility of the underlying asset.

    Drift is a 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 stock price percentage drift rate.

    Maturity is a non-negative real number, or a reference to a cell containing that number, which is the time to maturity of the option in years.

    Lower barrier is a real number, or a reference to a cell containing that number, which is the predetermined lower barrier price. This should be set to zero for no lower barrier.

    Upper barrier is a real number, or a reference to a cell containing that number, which is the predetermined upper barrier price. This should be set to zero for no upper barrier.

    Strike is a non-negative real number, or a reference to a cell containing that number, which is the strike price at which the option can be exercised. If Strike is omitted, then a default value of -1.0 is applied.

    Put/Call is a string, or a reference to a cell containing that string, which specifies whether the option is a put (any string that begins with a lowercase "p") or a call (any string that begins with a lowercase "c"). If Put/Call is omitted, then the default value of "c" is assumed.

    • If any of Spot, Volatility, Drift, Maturity, Lower barrier, or Upper barrier is non-numeric, then OPT_PROB_INMONEY reports a #VALUE! error.
    • If Strike is non-numeric, then OPT_PROB_INMONEY reports an invalid argument error (Err:502).
    • If either Spot or Volatility is less than or equal to 0.0, then OPT_PROB_INMONEY reports an invalid argument error (Err:502).
    • If Maturity is less than 0.0, then OPT_PROB_INMONEY reports an invalid argument error (Err:502).
    • If Put/Call does not satisfy the constraints given above, then OPT_PROB_INMONEY reports an invalid argument error (Err:502).

    Additional details:

    Details specific to OPT_PROB_INMONEY function

    • For a call option, the function returns the probability that the asset will end up between Strike and Upper barrier.
    • For a put option, the function returns the probability that the asset will end up between Lower barrier and Strike.
    • The function ignores the possibility of knock-out before maturity.
    • The name space for OPT_PROB_INMONEY is COM.SUN.STAR.SHEET.ADDIN.PRICINGFUNCTIONS.GETOPTPROBINMONEY.


    General information about Calc's option valuation functions

    Note pin.svg

    Note:
    For convenience, the information in this subsection is repeated on all pages describing Calc’s option valuation functions.

    • In finance, an option is a contract that provides the right to purchase or sell an asset at a predetermined price in a specific time-frame. More background information can be found on Wikepedia's Option (finance) page and in the sources that it references.
    • Calc provides four functions that can be used in the valuation of financial options and these are:
    • OPT_BARRIER – calculates the pricing for a barrier option.
    • OPT_TOUCH – calculates the pricing for a touch / no-touch option.
    • OPT_PROB_INMONEY – calculates the probability that an asset will end up between two barrier levels at maturity.
    • OPT_PROB_HIT – calculates the probability that an asset hits a predetermined barrier price.
    • Such functions are not specified in ODF 1.2 and are not available in other popular spreadsheet applications.
    • These four functions are based on an implementation of the widely-used Black-Scholes pricing model, which assumes that the value of the underlying asset can be modeled as a geometric Brownian motion. The mathematics behind this model is beyond the scope of this wiki but more information can be found on Wikipedia's Black–Scholes model page and in the sources that it references.

    Examples:

    Formula Description Returns
    =OPT_PROB_INMONEY(B1; B2; B3; B4; B5; B6) where cells B1:B6 contain the values 30, 20%, 10%, 1, 0, and 50 respectively. Here the function calculates the probability that an option with the specified characteristics will end up in-the-money at maturity. The arguments Strike and Put/Call are omitted and assume their default values (-1.0 and "c" respectively). The function returns a value of 98.44%. 0.984384937022762
    =OPT_PROB_INMONEY(30; 0.2; 0.1; 1; 0; 50; 35; "c") Here the function calculates the probability that a call option with the specified characteristics will end up in-the-money at maturity. The values of the arguments in this example are similar to those of the previous example, except that the Strike argument is set to the higher value of 30 currency units. The difference between Strike and Upper barrier is reduced and this leads to a lower probability of 33.98%. 0.339795543783619
    =OPT_PROB_INMONEY(70; 0.3; 0.15; 1; 60; 0; 80; "p") Here the function calculates the probability that a put option with the specified characteristics will end up in-the-money at maturity. The function returns a value of 34.40%. 0.344045040212094

    Related LibreOffice functions:

    OPT_BARRIER

    OPT_PROB_HIT

    OPT_TOUCH

    ODF standard:

    None

    Related (or similar) Excel functions:

    None