Documentatie/Calc-functies/OPT_TOUCH
TDF LibreOffice Document Liberation Project Blogs gemeenschap Weblate Nextcloud Redmine Ask LibreOffice Doneren
Functienaam:
OPT_TOUCH
Categorie:
Financieel
Samenvatting:
Berekent de prijs voor een touch/no-touch-optie met behulp van het Black-Scholes-prijsmodel.
Syntaxis:
OPT_TOUCH(Spot; Beweeglijkheid; Tarief; Buitenlands tarief; Vervaldatum; Ondergrens; Bovengrens; Buitenlands/Binnenlands; Knock-In/Out; Grenstype[; Grieks])
Retourneert:
Retourneert een reëel getal dat de prijs is voor een optie met de opgegeven kenmerken. Het geretourneerde getal is in dezelfde munteenheid als relevante argumenten, zoals de spotprijs en barrièreniveaus.
Argumenten:
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.
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.
Foreign/Domestic is a string, or a reference to a cell containing that string, which specifies whether the option pays domestic (any string that begins with a lowercase "d") or foreign (any string that begins with a lowercase "f") currency.
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_TOUCH 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. |
- Als een van de argumenten Spot, Beweeglijkheid, Tarief, Buitenlands tarief, Vervaldatum, Ondergrens of Bovengrens niet-numeriek is, dan retourneert OPT_PROB_INMONEY als foutcode #WAARDE!
- Als Spot of Beweeglijkheid kleiner is dan of gelijk is aan 0,0, dan retourneert OPT_TOUCH als foutcode Ongeldig argument (Fout:502).
- Als Vervaldatum kleiner is dan 0,0 dan retourneert OPT_TOUCH als foutcode Ongeldig argument (Fout:502).
- Als Buitenlands/Binnenlands, Knock-In/Out, Grenstype of Grieks niet voldoet aan de bovenstaande beperkingen, dan retourneert OPT_BARRIER als foutcode Ongeldig argument (Fout:502).
Aanvullende details:
Details specifiek voor de functie OPT_TOUCH
- Meer informatie over touch/no-touch opties is te vinden op veel financiële websites.
- De naamruimte voor OPT_TOUCH is
COM.SUN.STAR.SHEET.ADDIN.PRICINGFUNCTIONS.GETOPTTOUCH.
General information about Calc's option valuation functions
 |
NB:
|
- 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.
Voorbeelden:
Uitgewerkt voorbeeld, inclusief berekening van Grieks
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Spot | 1,2 | Prijs | 860,66391608499600 | |
| 2 | Beweeglijkheid | 12% | delta | 1.144,12757620840000 | |
| 3 | Tarief | 1% | gamma | −61.435,34038188970000 | |
| 4 | Buitenlands tarief | 2% | theta | 659,29777915478300 | |
| 5 | Vervaldatum | 0,4137 | vega | −4.411,45133663778000 | |
| 6 | Ondergrens | 1,05 | volga | −48.120,74826257920000 | |
| 7 | Bovengrens | 1,4 | vanna | 4.946,72395191636000 | |
| 8 | Buitenlands/Binnenlands | d | rho | 94,32777963799720 | |
| 9 | Knock-In/Out | o | rhof | −450,38444172236100 | |
| 10 | Grenstype | c |
In de bovenstaande gegevens:
- Cellen B1:B10 bevatten de waarden voor alle argumenten, behalve Grieks.
- Cellen E1:E9 bevatten de berekende waarden voor de verschillende Griekse argumenten (vermeld in cellen D1:D9).
- Cel E1 bevat de formule
=1000*OPT_TOUCH($B$1, $B$2, $B$3, $B$4, $B$5, $B$6, $ B$7, $B$8, $B$9, $B$10, $D1)en dit retourneert dezelfde waarde die zou worden verkregen door het argument Grieks weg te laten. Merk op dat de vermenigvuldigingsfactor van 1000 voor het gemak is opgenomen. - Cel E2 bevat de formule
=1000*OPT_TOUCH($B$1, $B$2, $B$3, $B$4, $B$5, $B$6, $ B$7, $B$8, $B$9, $B$10, $D2). - De formules voor cellen E3:E9 zijn vergelijkbaar, waarbij de cel waarnaar wordt verwezen in het laatste argument dienovereenkomstig wordt bijgewerkt.
Andere voorbeelden
| Formule | Beschrijving | Retourneert |
|---|---|---|
| =OPT_TOUCH(80; 0,2; 0,05; 0; 0,5; 60; 0; "f"; "o"; "c"; "r") | De functie berekent het rho Grieks voor een touch/no-touch optie met de gespecificeerde kenmerken. | 15,5516423091662 |
| =OPT_TOUCH(50; 0,25; 0,05; 0; 1; 0; 55; "d"; "i"; "c") | De functie berekent de prijs voor een touch/no-touch optie met de gespecificeerde kenmerken. Het argument Grieks wordt weggelaten en daarom retourneert de functie standaard de prijs. | 0,687637887461457 |
Gerelateerde LibreOffice-functies:
ODF standaard:
Geen
Gerelateerde (of gelijkwaardige) Excel-functies:
Geen