Skip to main content

Risk Management in Excel

Risk management is an extremely complicated field that demands access to market data – both real-time and historical –, a good understanding of the applicable valuation models and – above all – available implementations of at least a few of these models.

While this site is mainly committed to inform you about the best practices and latest standards in all issues facing risk management, it cannot turn a blind eye to the very real need of not just knowing about a problem and its possible solutions, but also implementing and realizing these solutions on the field.

In order to address this need, Risk Management Guru has decided to embrace Deriscope, a venture aiming at solving real-world problems relating to risk management in a manner that is both affordable and accessible to everyone. The first is made possible by Deriscope’s outsourcing of its pricing algorithms to QuantLib, the well-known open source analytics library that continues to evolve since its inception year of 2000. The second is realized by the fact that Deriscope is operated through Excel, the familiar spreadsheet medium installed on practically all desktops.

Deriscope stands on two pillars

The first pillar deals with the acquisition of market data, which include:

    1. Real-time prices of stocks, indices, mutual funds, futures, options, bonds, fiat and cryptocurrencies
    2. Historical time series of asset prices and technical indicators that may be intra-day with a frequency of one minute or span a period of over 30 years
    3. Company data, such as quarterly earnings

The second pillar deals with the exact valuation of cash and derivative products, such as bonds, interest rate swaps, options, swaptions, inflation-linked swaps, credit default swaps etc. (please place a link here to the Derivatives Pricing internal page)

The Value at Risk (VaR) of any portfolio can be also computed, provided that all its constituent products can be independently priced by Deriscope. (please place a link here to the Value at Risk internal page)

The world of derivative products is notoriously complex and not easily amenable to unified treatment. As a result, all commercial Excel-based derivatives pricing libraries come with a huge list of specialized formulas. Each formula expects its own input parameter set and produces its own specialized output. For example, a Black Scholes pricing formula would expect the five input variables: strike price, current stock price, time to expiration, risk-free rate, and volatility. It would then produce either a single number (the option price) or an array of numbers that would also include the risk sensitivities (greeks). A swaption pricing formula would require a significantly greater number of input variables and would possibly return much more complex data than just a price and a few greeks.

The video below demonstrates the prevailing chaos by using the official Excel Add-In of QuantLib as a typical example of how all pricing libraries behave in this respect.


 
Deriscope deals with this complexity by adopting a … single spreadsheet formula, named ds, which has only minimal a-priori expectations with regard to its input arguments. All ds expects, is one or more ranges that specify three things that actually mimic the syntax of an English sentence. Just like an English sentence consists of a subject from which a specific action emanates, a verb that carries the action and an object on which the subject applies the verb’s action, so should the input to the ds formula define a Type from which the action emanates, a Function that carries the action and an Input Data Collection on which the Type applies the Function’s action.

To Recapitulate

The ds formula expects as input one or more ranges, which must convey the Type, Function and Input Data Collection needed by ds to fulfil the requested task.

That’s the syntax. What about the grammar?

Well, the grammar is still simpler. Everything must be specified in terms of key/value pairs.

Each key is just a text label that identifies a property. It should end with the equal sign = so that ds knows it is indeed a key and not normal text.

Each key must have an associated value, which can be anything: a single number, text, date, boolean or an array of such things.

There are two reserved keys called Type= and Function=, the associated values of which supply the universally required Type and Function mentioned above.

As an example, imagine that your task is to create an object that is supposed to represent a specific zero bond contract. First you need a Type and a Function. In this case the Type must be Zero Bond and the Function must be Create. Then a bunch of input data is also needed that define the properties of the specific zero bond contract. The whole set of key/value pairs fits in a single range, which then is fed as input argument to the ds formula in cell A1, as shown in the image below:

DsInput Formula

You may observe that the ds formula in cell A1 has returned the text &ZeroBnd_A1:1.1, which is a so-called handle name, effectively a text label that identifies in a unique way the respective object held in Excel memory.

You are not supposed to type the various key/value pairs involved in the creation of a zero bond by hand. Deriscope comes up with an integrated wizard, which can generate and paste in the spreadsheet any desired formula.

In the image below the wizard is shown as a taskpane and consists of three areas:

Input, Browse and Info.

The Input Area is where you define the Type and Function.

The Browse Area is where you define the Input Data Collection. In the picture it appears collapsed because no function is yet selected.

The Info Area at the bottom serves as an information billboard. It displays text related to your current spreadsheet or wizard selections.

 

More specifically, the Input Area contains the three selectors shown in the next image.

The Type Selector is the most important as it is used for the Type selection.

As soon as a Type is selected, the Function Selector will contain only those Functions that are compatible with the selected Type.

 

The following video shows how one may generate the zero bond creation formula using the wizard:


 
Apart from generating spreadsheet formulas, the wizard is also useful for displaying the contents of the object associated with any handle name. This actually occurs automatically as soon as a cell that contains a handle name is selected. The next image shows how the wizard presents the contents of the object &ZeroBnd_A1:1.1 when cell A1 is selected:

 

The wizard can also supply information on any Key. All you need to do is select a cell that contains a Key, as the next image demonstrates:

 

The next and final video shows how the wizard may generate the formulas required for the pricing of the zero bond contract:

 

 

The next image shows the complete set of three formulas – all generated by the wizard – that calculates the price of the zero bond as 83.3333.

Please note that in big spreadsheets it is likely that the wizard slows down your interaction with the spreadsheet. This is due to its efforts to interpret the contents of the selected cells and provide you with related information. The easiest remedy is to simply minimize or hide it for as long as its existence is not needed.

For more detailed information on the several Deriscope features not outlined in this section, you may visit the article series at Introduction to Deriscope.