We often use Excel templates to calculate the Call and Put option prices with required parameters and the relationship between the parameters are expressed in Excel Functions. One tricky problem with the Excel Function is that if you change the cell position, accidentally click on a wrong cell, or if you want to export the results and utilize the templates again, the embedded functions may change accordingly and generate wrong answers.

Spotfire really has solid support for the formula-based calculation using Custom Expression, and for parameter input, you can use Property Control. Once it’s done, you will enter the parameter values, the results will show up immediately. It will work as if you created a specific calculator for your purpose with perfect accuracy.

Let’s Look at the Formulas in Excel

Here are the formulas:

To calculate the price of call or put options, we need to have

  • Stock Price Now (S)
  • Exercise Price of Option (X)
  • Number of periods to Exercise in years (T)
  • Compounded Risk-Free Interest Rate (r)
  • Standard Deviation (annualized sigma)

Then use the fundamental formula of d1, d2, N(d1), N(d2), N(-d1) and N(-d2), then get the price of Call and Put in the end.

In Excel, we would need to click the cell position of each value we use in the formula, like this:

Sometimes it is kind of exhausting to repeat the procedure and click each cell position again and again, now let’s try using Spotfire to find the answer.

Show Magic in Spotfire

Above, you could see the function is rather abstract and complicated when expressed in the cell position in Excel. It is hard to locate each parameter value. However, in Spotfire you could use Custom Expression to generate new Calculated Columns and save them in a column or data table.

Set up Property Control

To generate the interface for users to input the parameters, first create an Input Field inside a text area and assign Property Control to them. Make sure the names of the Document Properties are self-explanatory.

Do the Math

Now we have all the input parameters settled as Property controls, then we can use the Custom Expression to generate the formula for the calculation.

Your Homework


Take a look at the template we’ve put up on this topic. Start exploring Spotfire by yourself. This is just a simple economic model which could be calculated in Spotfire, keep tuned to the following financial and economic series in Spotfire. You should buy me a drink for all these tutorials on Excel to Spotfire.

Jane Zhou
Big fan of Spotfire

Written by Jane Zhou
Master of Statistics candidate of Rice University with undergraduate degree in Financial Statistics and Risk Management. Strong background in R, SAS, SQL, finance, economics. Career interests in: Data Scientist, Consulting, Investment Banking. Energetic, enthusiastic, quick learner.