Black-Scholes Options Pricing: Creating Matrix Calculators with Xcelsius

By Loren Abdulezer

Introduction

This article will show you how to use Xcelsius to turn a spreadsheet of the Black-Scholes options pricing model into a powerful matrix-style calculator.

You will also learn how you can: use Alerts to color-code ranges of numbers, and use Sliders to set, and easily vary, a sequence of input values in order to perform multiple what-if scenarios, quickly.

Try it, it's interactive

Fig. #1

A spreadsheet is good, but...

We all work with spreadsheets and other software, and often struggle with looking at the implications of relatively complicated formulas. For example, try visualizing:

=(B6*(NORMDIST(((LN(B6/B7))+((B8+(B13*B13/2))*C12))/(B13*(SQRT(C12))),0,1,TRUE)))- (B7*(EXP(B8*C12*(-1)))*(NORMDIST(((LN(B6/B7))+((B8+(B13*B13/2))*C12))/(B13*(SQRT(C12)))- (B13*SQRT(C12)),0,1,TRUE)))

Basically, you can't. You can simplify the formula by giving names to cell coordinates, and the resulting formula might look something like:

=(s*(NORMDIST(((LN(s/x))+((rr+(sigma*sigma/2))*t))/(sigma*(SQRT(t))),0,1,TRUE)))-(x*(EXP(rr*t*(- 1)))*(NORMDIST(((LN(s/x))+((rr+(sigma*sigma/2))*t))/(sigma*(SQRT(t)))-(sigma*SQRT(t)),0,1,TRUE)))

Naming helps clarify the formula, but not enough to really understand how the formula behaves.

Clearly, formulas by themselves are not enough. Now, pull back and look at how this formula might be laid out in a typical spreadsheet (Figure 2). It is at this level that most people are used to working with spreadsheets.

Fig. #2

Even here, your only way of understanding its behavior is to enter a number of different values for the stock price, strike or exercise price, risk free rate of interest, time till expiration, and the volatility. Notice there are quite a few variables you have to specify.

It is easy just to type in a new value. For example, if the Time Till Expiration is 7 months instead of 6, your call value would be 4.646 instead of 4.554. This is good, but slow and tedious. Deep insight will not come quickly if you only can adjust values one at a time.

A Better Approach

Step 1: From a single data point to a spectrum of values

It would be helpful to see the calculation results over a range of values. This is accomplished in a spreadsheet by creating a table-like structure. You can easily set up a table as in Figure 3.

Fig. #3

Now, this is starting to get useful. Since it is table data, it might seem that creating an Excel chart -- whether 3D or stacked -- should work well to better understand the behavior. A quick look at a 3D rendering (see Figure 4), makes it obvious that there is a lot of data and interpreting the data visually within Excel will be a challenge.

Fig. #4

You might think you’ll have better luck with a stacked chart, but it actually doesn't work so well either.

Step 2: Organize your data visually

Perhaps, we're approaching things the wrong way. Look at the data. The spreadsheet is computing the call value option price, and that's what you're interested in. You want to understand what range of input values will give rise to a price of 4.58 vs. 5.23. So, it is best to organize your spreadsheet table of Figure 3 into bands of different colors. This is nicely done using the Alert feature of Xcelsius (see Figure 5).

Fig. #5

Set the Alert Values to be a range of cells, as shown in Figure 5. In this case, I picked the smallest and largest values in the table, and defined the range of values in cells H3:H7.

Step 3: Using Xcelsius Sliders to set parameter sequence and range

The spreadsheet table (Figure 3) is nice, but the range of values you might want to view could be different than the ones shown in the table. Take a look at the top row of the table (Time Till Expiration). It starts with the value of 6 months, followed by 7, 8, 9, etc. But, what if you are interested in the pattern 4, 6, 8...?

In principle, you can figure out the whole sequence with just the first two numbers. This is exactly what is done with the Xcelsius Dual Slider. (If the Dual Slider is not visible in your Matrix Calculator, make sure to check the checkbox.) Very conveniently, you can use the Dual Slider to set the first two values in the sequence of months, which drives all the other values.

Or, you might want to use a slightly different approach to setting the values for your table. The volatility (the percentages running vertically along the left side of the table) makes use of another way to set parameter values. In this approach, you use a single slider to set the starting value of the sequence. Directly below the table, there is an Xcelsius Value component called Volatility Increment. You use this to set the value increment. The combined effect of these two components, allows you to adjust the start of the sequence and keep the interval constant. So, if your sequence is 3%, 4%, 5%...; you only need to adjust the slider to get the sequence to be something like 3.75%, 4.75%, 5.75%...

Closing Thoughts

The concept of a matrix style calculator, even with plain spreadsheets is powerful. Xcelsius takes this capability and amplifies it. Perhaps, it's time to dust off some of your spreadsheets and turn them into power tools.

Loren Abdulezer, CEO, Evolving Technologies Corporation http://www.xcelsiusbestpractices.com

©2005 Evolving Technologies Corporation - all rights reserved.