Documentation/Calc Functions/OPT BARRIER
TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate
Function name:
OPT_BARRIER
Category:
Financial Analysis
Summary:
Calculates the pricing for a barrier option using the Black-Scholes pricing model.
Syntax:
OPT_BARRIER(Spot; Volatility; Rate; Foreign rate; Maturity; Strike; Lower barrier; Upper barrier; Rebate; Put/Call; Knock-In/Out; Barrier type[; Greek])
Returns:
Returns a real number which is the pricing for an option with the specified characteristics. The number returned is in the same currency unit as relevant arguments, such as the spot price, strike price, rebate, and barrier levels.
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.
Rate 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 continuously compounded annual risk-free interest rate.
Foreign rate 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 continuously compounded annual foreign interest 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.
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.
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.
Rebate is a real number, or a reference to a cell containing that number, which is the amount to be paid at maturity if the barrier is hit.
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").
Knock-In/Out is a string, or a reference to a cell containing that string, which specifies whether the option is a knock-in (any string that begins with a lowercase "i") or a knock-out (any string that begins with a lowercase "o").
Barrier type is a string, or a reference to a cell containing that string, which specifies whether the barrier is monitored continuously (lowercase "c") or only at the end/maturity (lowercase "e").
Greek is a string of lowercase characters, or a reference to a cell containing that string, which determines which of the available "Greeks" should be used. A Greek is a measure of how the option price changes as a parameter in the Black-Scholes model is changed. The following table lists the available options.
| String | Description |
|---|---|
| "value" or "v" or "price" or "p" | No price sensitivity analysis is required and so OPT_BARRIER simply returns the option price. This is the default that is applied if the Greek argument is omitted. |
| "delta" or "d" | Represents the sensitivity of the option price to changes in the value of the underlying asset. |
| "gamma" or "g" | gamma is a second order Greek that measures the sensitivity of delta to changes in the value of the underlying asset. |
| "theta" or "t" | Represents the sensitivity of the option price to changes in the time to maturity. |
| "vega" or "e" | Represents the sensitivity of the option price to changes in volatility. |
| "volga" or "o" | volga is a second order Greek that measures the sensitivity of vega to changes in volatility. |
| "vanna" or "a" | vanna is a second order Greek that measures the sensitivity of delta to changes in volatility. |
| "rho" or "r" | Represents the sensitivity of the option price to changes in the (domestic) interest rate. |
| "rhof" or "f" | Represents the sensitivity of the option price to changes in the foreign interest rate. |
- If any of Spot, Volatility, Rate, Foreign rate, Maturity, Strike, Lower barrier, Upper barrier, or Rebate is non-numeric, then OPT_BARRIER reports a #VALUE! error.
- If either Spot or Volatility is less than or equal to 0.0, then OPT_BARRIER reports an invalid argument error (Err:502).
- If either Maturity or Strike is less than 0.0, then OPT_BARRIER reports an invalid argument error (Err:502).
- If any of Put/Call, Knock-In/Out, Barrier type, or Greek does not satisfy the constraints given above, then OPT_BARRIER reports an invalid argument error (Err:502).
Additional details:
Details specific to OPT_BARRIER function
- Further information about barrier options may be found on Wikipedia's Barrier option page.
- The name space for OPT_BARRIER is
COM.SUN.STAR.SHEET.ADDIN.PRICINGFUNCTIONS.GETOPTBARRIER.
General information about Calc's option valuation functions
 |
Note:
|
- 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:
Worked example, including calculation of all Greeks
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Spot | 1.2 | price | 96.84831132786850 | |
| 2 | Volatility | 12% | delta | 953.34792282149000 | |
| 3 | Rate | 1% | gamma | 1499.53779025502000 | |
| 4 | Foreign rate | 3% | theta | 8.30162545163017 | |
| 5 | Maturity | 0.167 | vega | 43.27337065378570 | |
| 6 | Strike | 1.1 | volga | 1014.19081771509000 | |
| 7 | Lower barrier | 0 | vanna | −1263.54209880903000 | |
| 8 | Upper barrier | 1.5 | rho | 174.87818249484000 | |
| 9 | Rebate | 0 | rhof | -191.05185048659400 | |
| 10 | Put/Call | c | |||
| 11 | Knock-In/Out | o | |||
| 12 | Barrier type | c |
In the above data:
- Cells B1:B12 contain the values for all arguments, except Greek.
- Cells E1:E9 contain the values calculated for the various Greek arguments (listed in cells D1:D9).
- Cell E1 contains the formula
=1000*OPT_BARRIER($B$1, $B$2, $B$3, $B$4, $B$5, $B$6, $B$7, $B$8, $B$9, $B$10, $B$11, $B$12, $D1)and this returns the same value that would be obtained by omitting the Greek argument. Note that the multiplicative factor of 1000 is included for convenience. - Cell E2 contains the formula
=1000*OPT_BARRIER($B$1, $B$2, $B$3, $B$4, $B$5, $B$6, $B$7, $B$8, $B$9, $B$10, $B$11, $B$12, $D2). - The formulas for cells E3:E9 are similar, with the cell referenced in the final argument updated accordingly.
Other examples
| Formula | Description | Returns |
|---|---|---|
| =OPT_BARRIER(30; 0.2; 0.06; 0; 1; 40; 25; 0; 0; "c"; "o"; "c") | The function calculates the pricing for a barrier option with the specified characteristics. The Greek argument is omitted and so the function defaults to returning the price. | 0.424281900452897 |
| =OPT_BARRIER(50; 0.4; 0.05; 0; 0.5; 65; 0; 80; 0; "p"; "o"; "c"; "e") | The function calculates the vega Greek for a barrier option with the specified characteristics. | 10.1584598399095 |
Related LibreOffice functions:
ODF standard:
None
Related (or similar) Excel functions:
None