Valuation and Spreadsheet Schizophrenia

9 May 2011

After a long time in financial markets, I find myself somewhat schizophrenic over the continuing use of spreadsheets in trading, and in particular those Excel sheets managing the valuation of derivatives and fixed income securities.

Schizophrenic? Well, on the one hand, spreadsheets offer great flexibility in supporting some of the relatively complex data structures needed to price many illiquid assets. This flexibility can be very valuable if used formally as part of an institution's processes. Put another way, I like spreadsheets. On the other hand, the fact that such spreadsheet usage remains so prevalent is testament to some of issues that need to be resolved post-crisis. Again put another way, I worry about spreadsheets.

Hopefully we all "get it" that the people trading and investing in derivatives should not be the same people that report on the value of these securities. That having been said, it still surprises me that the issue of control in this respect just keeps rolling along, to be found out only at times of market stress and crisis. Why does this happen? I would suggest nobody questions a successful business division when it is growing fast and seemingly successful. Additionally, the complexity of the product being valued is again a barrier to having other people value it.

I think the usage of spreadsheets for valuation management also occurs for other reasons. One reason again results from the complexity of the products combined with the great ease of use of spreadsheets such as Excel. Need to support a yield curve? No problem, do it in Excel. Need to support several spread curves? No problem, add a new sheet and share between all the products being valued. Got a funny interpolation to do on the volatility surface? Again no problem, just get it in the worksheet and implement what you need.

So spreadsheets offer an infinitely flexible data model, combined with ease of use that means the business people who understand the product can also implement the logic to support it in a time frame that is counted in minutes rather than days or weeks. Trying getting that level of flexibility and responsiveness from many a "mainstream" trading and risk management system.

Derivative products and the data needed to price them also fall into a technology solutions "gap" in the market between reference data management vendors and the pricing model vendors. Those in reference data management are slowly realising that the context that these systems are being used in is changing, moving from the back office to support other areas, notably risk.

In supporting risk, you then find the segmentation between reference data and market data becoming blurred, as risk managers are focussed on simply the data they need rather than the industry's historical desire to segregate by data type. Data management vendors must move beyond reference and market data, towards the management of other more complex "model" data such as curves, spread curves, volatility surfaces/cubes etc.

Pricing model vendors also have their part to play, and to make the assumption that delivering a pricing calculator is the end of the job is no longer sufficient. Obviously, valuation service providers are filling a need in this regard, but even these services suffer from a current reputation of being too opaque to be trusted.

Anyway I will put this up on the TabbFORUM and let's see what happens over the next five years. My bet is that Excel remains as the world's most prevalent trading system...

by Brian Sentance

Become a bobsguide member to access the following

1. Unrestricted access to bobsguide
2. Send a proposal request
3. Insights delivered daily to your inbox
4. Career development