3 minutes reading time (643 words)

Introduction to Deriscope – Part 3: Pricing a Stock Option


In my previous article I showed you how to create a Stock Option object in Excel and how to access the list of functions that apply to that object.

As I pointed out, the most important of these functions is the Price, which calculates the fair price of the calling object.

The Price function is a so called Local function, in the sense that it is invoked by an already existing object. It is always available provided the object's type derives from Tradable, as is the case with the Stock Option I am dealing with here.

Picking up where I left off, I go ahead and choose the function Price from the list of functions displayed in the Function Selector.

Deriscope fills up the grid in Browse Area as shown here.

These are the 9 input parameters to the Price function.

The first column holds the parameter names – also known as Keys – and the second column holds the associated values.

Only the two keys Models and Markets are mandatory.

The rest are optional as indicated by their dim appearance and are described in the above picture.

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 modelling assumptions and market data that I can peruse and edit right here if I want, as this video demonstrates.

But I can just keep the default values and click on the Go button in order to generate and paste the full set of formulas in the spreadsheet. If I do that, the following menu of options appear​:

 If I select Create Demo Workbook, Deriscope creates a new workbook called Book2 consisting of 5 sheets.

Cell B2 of the last Output sheet contains the formula =ds(B3:C6) that calculates the option price as 9.925053717

The green color cells contain links to formulas in other sheets.

The Info Area at the bottom of the taskpane display suggestions for further exploration of the produced results.

 I can now edit the various data in the spreadsheet.

For example, I can go to sheet Other and select the cell B2 containing the handle name of the Payoff object.

The wizard then reacts by displaying the object's contents in a read-only fashion. 

I may change any value here, for example the value Call for the key Direction in cell C6.

As soon as I select that cell, a dropdown with allowed values appears.

I choose Put and then go to sheet Output to see that the option price has changed to 6.003997633

Not all values can be changed in spreadsheet.

To demonstrate, I go back to sheet Other and select the cell C5 containing the value Vanilla for the key Payoff Type.

A dropdown with allowed values appears.

 If I select SuperShare, the formula in cell B2 returns an Error object, of which the message can be read in the Info Area of the taskpane.

I am being informed that a key named "Second Strike" is missing from the input data.

I am also provided with a hyperlink to the description of that key.

 I am now having two options:

  • 1.In the input range I insert an extra row containing the missing key Second Strike and the respective value, which value I figure out with the help of the given key description.
  • 2.I ask the wizard to create a new Payoff object, to which I then link the cell C5 as this video shows:

Feel free to contact me if you want to share any thoughts with regard to this product or if you want to request any particular features. Contact info and social media links are available at my web site https://www.deriscope.com

Introduction to Deriscope – Part 4: Spreadsheet
Introduction to Deriscope – Part 2: Creating a Sto...