(from Derivatives Strategy, July 1997)

The novelist Tom Robbins has said "There are two kinds of people in the world: those that think there are two kinds of people, and those that know the other ones are wrong." Although he didn't realize it at the time, he was writing about the derivatives software industry. That's because if you look across the landscape of derivatives software, you might feel that products fall into two categories. They could be deemed high end and low end, expensive and bargains, big and small, fully functional and toolkits, or back office systems versus front office analytics. Upon closer inspection, however, we discover that many packages have functionality that is shared by their brethren at the other end of the spectrum, though perhaps implemented in a harder to use and less comprehensive manner. But with certain other packages, closer inspection reveals shared functionalities which can only be described as elegant in their implementation.

This article will discuss some ways that a certain class of high end spreadsheet analytics can be extended relatively easily to offer capabilities often associated only with full blown systems. In particular, we'll examine how a certain spreadsheet Add-In can be used to create fairly sophisticated portfolio and hedging systems. Although we are not recommending our methodologies as a solution for everyone, depending on your requirements and budgetary constraints, the solutions presented here could be highly desirable.

As a rule of thumb, fully functional high end systems might go for as much as a million dollars (some will let you lease for five figures a year, but in the end buying versus leasing are probably about the same), while less expensive packages like spreadsheet Add-Ins can be had for just a few thousand dollars. The large systems can offer features like client server architectures, multi-user capabilities, mission critical performance and more. Whether you want to pay up for these features is simply a function of whether or not you need them: if your business depends on your having a particular software feature, chances are a million dollars is not a high price to pay to stay in business. The only risk free advice we can really give is that it's not worth paying a consultant a million dollars just so they can tell you to save your money and get an inexpensive package.

An example system

To make the ideas in this article more concrete and easier to implement, we'll consider a high-end derivatives software Add-In program, chosen completely at random: TOPS, Tanenbaum Option Pricing Software. We'll discuss the version that runs as an Add-In to Microsoft Excel. Other derivatives packages used in conjunction with Excel or other spreadsheets may also employ the same methods presented here. However, the actual implementation will vary from product to product. That said, let's start by quickly defining the key features of the program pertinent to the discussion at hand.

TOPS for Excel is a set of derivatives models or functions that become an integral part of a spreadsheet program. Once it's loaded, 40 or so functions may be called and used in the same manner as Excel's built-in functions, like SUM, NPV, and IRR. Each function will allow the user to price and obtain the sensitivities of virtually any derivatives structure once the specifics of the trade are provided. For example, if you were to enter in a spreadsheet cell or on the command line:

=Swaption("6/28/97",100,B4,C17:D34,…)

then you would be valuing a swaption as of June 28, 1997 on a swap with a notional balance of 100, a strike provided by spreadsheet cell B4, a yield curve represented by cells C17:D34, and so on. When entered into a particular spreadsheet cell, the Add-In will return the value of the swaption, or any sensitivity such as delta, theta, gamma, etc., depending on what you've instructed it to value. Note that in this case, the cell address B4 is an argument to the function. Note also that the yield curve, which is defined by time and discount factors, gets passed in as just one argument, or a range. In our example, the yield curve range consists of two columns and 18 rows.

Basic Applications

Given this framework, the most obvious use for a system like this is to use it to create a calculator template, setting up some cells as input fields, and another cell(s) as the output field(s) with the relevant valuation function that references the input fields. The natural extension to this is to create tables of values, with say a strike in each row and an expiration in each column. Many traders will set up their spreadsheets in precisely this way, ready to bring up a calculator on a moment's notice and provide a quote (often, real time feeds from a data vendor will go directly into a cell being referenced by the formula, allowing the trader to provide a price instantly). Then it's pretty straightforward to create two and even three dimensional graphs of option value versus strike and expiration from the table. Users may also take advantage of the Solver to back out values, such as solving for the coupon rate on a callable bond that makes the non-callable bond price minus the call option price be equal to par. However, as we are about to see, high end spreadsheet Add-Ins can also be easily used for more sophisticated applications such as managing portfolios of exotic derivatives, as well as determining proper dynamic hedges for a portfolio.

Portfolios

The usual ways of using spreadsheets often deal with single deal structures, which is fine for traders contemplating a new position, or auditors double checking valuations. But the larger systems often distinguish themselves by their ability to handle multiple transactions, or portfolios of trades. It turns out the spreadsheet metaphor, when combined with high end Add-Ins, like the one described here, is very well suited to valuing portfolios. Simply put, think of each row in a spreadsheet as a single trade, and each column in a spreadsheet as a distinguishing characteristic of the trade. For example, if a bond was part of our portfolio, then we may enter the bond's trade date in column A, the bond's notional balance in column B, the bond's maturity in column C, the bond's coupon rate in column D and so forth. Then in the last column, we could call the appropriate Add-In model using as inputs the spreadsheet's referenced columns and rows which contain the bond's attributes.

Since portfolios will often have different types of securities requiring different input parameters, with certain securities requiring more inputs than other securities (for instance, the defining characteristics of a bond differ from the defining characteristics of a bond option), then in order to handle all the different types of securities that might be in a portfolio, each type of security will have its own dedicated spreadsheet.

Suppose there are three different types of securities in our portfolio: bonds, bond options, and caps. To value this portfolio, we would set up three spreadsheets with a spreadsheet dedicated to each security. For bonds, the bond spreadsheet would have the required inputs for bonds going across the top of the spreadsheet. For bond options, its spreadsheet would also have its required inputs going across the top of spreadsheet. Likewise for the caps spreadsheet. Then for every new trade that comes in, we will add a new row to the spreadsheet that's been dedicated to that trade's structure. To add a trade, we can enter the trade's attributes into each column of the trade's spreadsheet, or if it makes things easier, copy the previous row down and edit the new row to reflect the new trade. Better yet, we could also create an Excel dialog box that will prompt the user for required inputs, and then copy them into the next row in the spreadsheet (an audit trail worksheet could then be automatically updated). We have done just this for some of our customers, and with each customer it has been done in a manner customized to their liking, thanks to the flexibility of spreadsheets. And while conventional wisdom says only small portfolios can be handled like this, one customer we know is able to keep track of 3000 caps this way.

Since we will want the portfolio to be analyzed using the market's implied yield curves, and volatility curves, we can set up one spreadsheet dedicated to just yield curves, and another one dedicated to just volatility curves. We can also have multiple curves in a spreadsheet, such as one yield curve for yen, one for the dollar, another for the pound, etc. Because we are working with spreadsheets, we can have another spreadsheet that summarizes information from other sheets, such as the total value of the portfolio or the average duration.

Hedging: a Eurodollar example

There are a number of acceptable methods for hedging the risks of a portfolio. We will discuss how an Add-In can be used to implement one method: the Bump and Run. The term Bump and Run comes from the fact that this method involves "bumping" one of the inputs to the pricing model by a small amount, and rerunning the model to see the effect of the change (Bump and Rerun doesn't sound as catchy).

The general idea is to calculate the change in the value of the portfolio caused by the bump in the input (this is done by valuing the portfolio once using the current market level of the input, and then again by the bumped value of the input). A hedge instrument is chosen, and it too is valued before and after the bump. The ratio of the change in the portfolio to the change in the hedge instrument is the amount of the instrument that is needed to hedge the portfolio.

For a portfolio of interest rate instruments, the most natural input to bump is the yield curve. This is by no means the only source of profits and losses, and a full hedge analysis would be extended to bumping the volatility curve, among other things. But the basic idea is the same. However, we will limit our discussion here to yield curve shifts.

As stated earlier, the yield curve is made up of a set of dates and discount factors accompanying those dates. Let's suppose our current yield curve consists of quarterly dates for the next ten years, or 40 points in total. To bump the yield curve, we want to bump the 40 points. Rather than bumping all 40 at once, a more accurate and useful hedge can be obtained by bumping each point successively, one at a time.

Inside the spreadsheet, this can be accomplished by putting the current yield curve (our base curve) in columns A and B. Then, we'll copy the same curve over to columns C and D. Now, change the first discount factor by a small amount, such as .001, and leave the rest of them unchanged. In columns E and F we'll make another copy of the base curve, and change only the second discount factor by .001. We'll continue like this until we have 40 new yield curves, each one the same as the base yield curve, except for one particular point which has been bumped by .001.

We next revalue the entire portfolio using the first bumped yield curve, and note how much profit or loss there is relative to the value of the portfolio using the base yield curve. We do the same thing for the other 39 bumped curves, so we have a profit and loss for each of the 40 curves. For this example, the simplest hedge instruments to use are 40 Eurodollar futures contracts. The first contract will be used to hedge the change in the first point on the yield curve, the second contract will hedge the change in the second point on the curve, and so on. Again, by taking the ratio of the portfolio P & L to the first Eurodollar contract P & L we will arrive at the amount of the first futures contract needed to hedge the portfolio against changes in the first point on the curve. The P & L from the fortieth curve will allow us to calculate how much of the fortieth Eurodollar futures to use to hedge the position.

With this sophisticated hedge of 40 instruments, the portfolio is immunized against any odd type of twist in the curve, not just parallel shifts. That is what makes this type of hedging very popular. And thanks to the magic of macros, this whole process can be automated, so that it works right out of the box.

A variant of this is performed by some dealers. What they do is go to the spreadsheet where the yield curves are defined, and create new yield curve scenarios. For instance, if columns A and B hold the base case yield curve, then C and D might be a small parallel shift up, and E and F a small parallel shift down. Columns G, H, I and J might have large parallel shifts up and down, while columns K and L have a steeper curve and columns M and N a flatter curve. With these six scenarios (and probably a few more) they revalue the portfolio under each case, and calculate the profits and losses. This gives them a fairly sophisticated VaR analysis, because they can look at any type of shift they want. Of course, hedges may be created based on the profits and losses calculated this way, but using more complex techniques than there is time to present here.

Summary

The old adage "You get what you pay for" is often cited by vendors of large scale derivatives systems to justify their pricing. But as we all know, you don't always use every feature you paid for. We've demonstrated how products which, at first blush, appear to be too underpowered to handle "real world" problems can not only be up to the task, but might offer more flexibility and customizability than a standalone front middle and back office system. Of course, this isn't for everyone, and the big iron systems have their place. In which case, it's nice to know that some of the less expensive derivatives systems are also available in the convenient "integrated with the big boys" size, as well. In fact, this can give you the best of both worlds: a system that meets your needs in a spreadsheet, and yet, one that can be integrated, pricing models and all, into a larger system as your business grows. In the end, while there may be two kinds of systems in the world, big and small, perhaps it is best when the most appropriate big and the most appropriate small systems are integrated and become, functionally, one and the same.