Valuation of derivative products is the flagship of Deriscope’s offerings.
A continuously expanding number of products and pricing models are supported. The PRODUCTS page of the Deriscope site provides an up-to-date, exhaustive list of all supported product types, along with their features and applicable models, with detailed technical information.
Every single product is identified by its Type. For example, the product used in Risk Management in Excel to demonstrate how pricing is done – a bond that pays a single amount at maturity but no intermediate coupons – has the Type of Zero Bond. Both European and American options on Microsoft stock have the Type of Stock Option, as do the respective options on Google stock. The details concerning the exercise style (American or European) or the issuer of the underlying stock (Microsoft or Google) do not characterize the Type. They are part of specific instances of that type, referred in Deriscope as Objects.
Types follow a hierarchy similar to that of object-oriented languages like Java or C++.
This means that any Type is part of some bigger parent Type or contain smaller children Types.
The biggest Type that contains all others is called – guess what – also Type.
The Deriscope wizard makes it easy to find and select a specific Type through its Type Selector (see image below).
As of October 2018, Deriscope contains a total of 315 different Types, according to the little tooltip that appears when the mouse hovers above the Type element of the Type Selector:
This is indeed the number of all possible Types because the Type Viewing Filter is set to all (see image below).
Changing it to eq, the tooltip reports the existence of 95 Types that are somehow related to equity:
You can use the Type Selector to navigate through the inheritance tree until you have found the Type you are looking for.
When you click on the root Type element, you will see the 6 available sub-types, of which the most important is one called Tradable:
Plenty of information is displayed in the Info Area of the wizard when you hover above or select a Type:
Every Type has a list of Functions that are compatible with it. The wizard’s Function Selector displays that list and allows you to select a specific Function. The next image shows the list of Functions available after you have selected Stock Option in the Type Selector:
The most important Function is the highlighted one called Create because it is used to create specific Objects (instances) of the referred Type.
You also notice that the Browse Area of the wizard (the one in the middle of the image above) displays the parameters that the highlighted Function expects as input. You see, for example, that the Create Function of the Stock Option Type expects 6 input parameters.
Finally the Info Area at the bottom display,s information about the highlighted Function.
If you now click on the highlighted Function element, the Browse Area gets filled with default input parameter values and appears as below:
Only 3 out of the original 6 input parameters are displayed due to the setting of the – yellow highlighted – Optional Parameters Visibility button.
The next logical step is to paste the chosen Function – Create in this case – along with its default input parameters in the spreadsheet.
You achieve this by clicking on Go and selecting one of the top three menu options, as shown below:
Assuming that cell A1 was already selected, the result is two new spreadsheet formulas created in cells A1 and A8, as shown below, with the precedence arrows displayed for an easier view of the various dependencies.
The formula in cell A1 creates an object, the Type of which is Stock Option. The returned text &SIE.DEOpt_A1:1.1 is the so-called handle name of that object and uniquely identifies it.
The formula in cell A8 creates an object, the Type of which is Payoff. The returned text &Payoff_A8:1.1 is its handle name.
The Deriscope wizard did the extra mile of creating the latter object only because the Payoff input for the Stock Option Create Function in cell B5 expects an object rather than just a number.
It is interesting that the same formula =ds(…) is used in both cells A1 and A8.
As explained in Risk Management in Excel, ds expects one or more ranges, which supply the so-called key/value pairs that specify everything ds needs to know to fulfill its task.
At the spreadsheet level now, you can get information on any cell by simply selecting it. The type of received information will depend on that cell’s contents.
For example, you see in above image that the Browse Area of the wizard displays certain data. These are the contents of the object referenced by the currently selected cell A1.
If you select the cell A13 that contains the key Strike=, then you will get information on the meaning of that key, as shown below:
If you select the cell B12 that contains the value Call, then you will get information on the meaning of that value and a cell validation dropdown with the allowed values will be also displayed, as shown below:
In order to calculate the price of this option, you need to tell the wizard to generate the applicable formulas.
As I mentioned above, every Deriscope Type has a list of compatible Functions. Some of these Functions though are so-called local Functions, in the sense that they are invoked by a concrete object of that Type. The Functions that are not local are called static.
The Function Create we used above is an example of a static Function because it is invoked by the Type directly.
On the other hand, the Function Price is a local Function because it requires a pre-existing object, out of which it is invoked. As a matter of fact, the Function Price applies to all objects of which the Type inherits from Tradable.
The practical implication of all this is that in order to price your option of cell A1, you first need to let the wizard know of that option object. You do that by simply selecting the cell A1. When you afterwards click on the Function Selector, you will see a list of Functions that are miraculously related to your selected object. When you select the Price one, your wizard appears as below:
I can hide all optional keys by clicking the button
resulting in the following screen
The displayed values $VanOptMdl#1 and $Mkt#1 are handle names of objects created by Deriscope for my convenience. They contain default modeling assumptions and market data that I can peruse and edit right here if I want, as this video demonstrates.
It might be easier to keep the default values and click on the Go button in order to generate and paste the full set of formulas in the spreadsheet. Then you could edit the data and enter reasonable market values – or link them to live feeds – directly in the spreadsheet.
This is the result that shows the topmost formulas, including the one in cell D1 that returns the option price.
Of particular interest is the phrase “To display extra pricing data click here” in the Info Area of the wizard. Doing so it reveals the following:
You can instruct the Price Function to return any of these extra data in the spreadsheet by adding the optional input key/value pair Output= xyz, where xyz the key name of the desired data. For example:
or return all extra data by adding the optional input key/value pair Add Extras= TRUE:
You may easily build tables where certain objects act as base while a few properties are allowed to vary. The trick is to reuse the base objects and only modify one or more of their properties through the special Deriscope Clone Function.
The Clone Function is a local Function invoked by all objects, regardless of their Type. Although you may use the wizard to generate the respective formula for any given pre-selected object, the formula is really simple and intuitive:
=ds(“Clone”, OBJ, EDITED_KEY, EDITED_VALUE)
where OBJ is the handle name of the original object, EDITED_KEY is the key you want to modify and EDITED_VALUE is the new value for that key.
The following is a table of objects of Type Payoff, each with a different strike:
Using similar techniques you can easily build, for example, a table that shows how the price of an option varies with its strike. In order to avoid repeating existing ranges, you may also use the special Deriscope spreadsheet formulas dsMergeH, dsMergeV and dsReplace.
