Introduction to Deriscope – Part 3: Pricing a Stock Option
In my Introduction to Deriscope – Part 2 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.
Now I will show you how to use the most important of these functions, the Price, which calculates the fair price of the calling object. Alternatively, you may watch my YouTube Stock Option Pricing video that includes voice narration.
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.
Table of Contents
The Price Function in the Wizard
Displaying and Hiding the Optional Parameters
The Price Function in the Wizard
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.
Displaying and Hiding the Optional Parameters
I can display and hide all optional keys by clicking the button
Below is the Browse Area when all optional keys are hidden. Only the two keys Models= and Markets= appear.
Viewing and Editing the Objects in the Browse Area
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.
Using the Go Button
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.
Editing the Data in the Spreadsheet
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
