Almost Corporate-Ready Unlimited-Currency FX Option Portfolio Pricing with and without Excel

The question whether Excel can be trusted for pricing and risk managing derivative transactions seems to be a never ending one.

If you are curious about my own opinion on this matter, you may jump to the end of this article. In the sequel, I will confront this issue through the real-world case of pricing and managing a book of FX options.

In my last week's post about FX Options Pricing and Value at Risk Calculation in Excel I demonstrated how one may use Deriscope to setup a spreadsheet for pricing a portfolio of three European FX options and calculating its VaR.

The spreadsheet was quite simple and yet capable of easily setting the underlying currencies among a set of predefined seven currencies without the need of formula adjusting.

Although undoubtedly a neat spreadsheet that could be used in production by a single professional, it still bears three shortcomings that may concern the average user:

• The input data to the main spreadsheet formulas that calculate the option prices and VaR are constructed by a quite intricate web of other Excel formulas.While this is common in these types of spreadsheets, it may raise concerns with Product Control due to the fact that Excel is a medium that sometimes reacts to certain user-initiated events in an unexpected manner.The latter represents for Excel a known weakness. It is the price one pays for Excel's biggest advantage, namely the ability to setup a set of formulas with complex inter-dependencies on the fly.But still the fact remains that a user of that spreadsheet, who does nothing else but editing the input data and observing the output prices, would feel much safer with an alternative non-Excel interface that would replicate the given input and output.

• The number of FX options is 3, and all possible underlying currencies are pre-specified and their number equals 7. Increasing the number of options and the universe of underlying currencies is possible but it requires non-negligible spreadsheet work in terms of adding several respective ranges and adjusting the various formulas.

• The user needs to enter by hand the correct vol for each custom option structure. Similarly, changing any one of the seven underlying currencies necessitates the manual entry of the respective interest rate (or more generally the respective yield curve object) and the associated pairwise correlations.

It is possible to substantially alleviate these concerns by separating the business of market data maintenance from the business of pricing.

As a matter of fact, it is also possible to conduct the whole business without using Excel at all.

Let me show you how. 

Recommended for Deriscope starters: The Overview and Quick Guide pages.

Building the Market Data Repository 

My job here is to assemble all market data that are linked to the same seven currencies that I used in my previous post (USD, GBP, EUR, JPY, AUD, CHF, BRL), but this time in a fashion that allows the easy addition of many more currencies.

One of these currencies (I choose the USD) must play a special role in the sense that it will be the quote currency for all specified fx rates.

The idea is to build a workbook where I dedicate one sheet to each currency.

The first sheet will be dedicated to the special quote currency USD and will contain the interest rate yield curve built out of USD-denominated instruments, typically deposits, futures and swaps.

The next sheet will be dedicated to the second chosen currency GBP and will contain the respective GBP-denominated yield curve plus the spot value of the fx rate GBP/USD and the vol curve of the fx rate GBP/USD.

The next sheet goes to the currency EUR, which must contain the respective EUR yield curve, the spot value of the fx rate EUR/USD and two vol curves: one for the fx rate EUR/USD and one for the fx rate EUR/GBP.

Continuing like that, the overall structure will look as follows: 

A quick observation is due here:

While only six spot fx rates suffice for implying all possible cross fx rates, I treat vol curves differently and I specify all possible pairs ignoring the order of the currencies in each pair specification.

The reason for ignoring the currency order is that the vol for ABC/XYZ is the same with the vol for XYZ/ABC.

I could go one step further and imply the vol for ABC/XYZ by relying on the vols of ABC/USD and XYZ/USD and their pairwise correlation, something that would allow me to specify only six vol curves.

While technically possible, this is not yet implemented by Deriscope due to the complexities that emerge in the case where the vol curves are defined through two-dimensional maturity-strike tables.

So, this is how my USD sheet looks like: 

The Yield Curve object &USD Yld Crv.1 is created in cell F5 out of deposit, futures and swap rates. Here these rates are entered manually, but in a real environment they must be supplied by real time feeds, such as Bloomberg.

The cell B5 contains the final object &USD Market.1 of type Market Set that represents the collection of all Market objects built in this sheet, which here consists only of the single object &USD Yld Crv.1.

The next sheet is the GBP and looks like: 

The difference from the previous sheet here is the addition of two Market objects:

The object &GBP/USD Spot.1 of type FX Value in cell J5 and the object &GBP/USD Vol Crv.1 of type Vol Curve in cell M7.

The section allocated for these two objects is shown again below for better resolution: 

As you see, both the spot fx rate 1.3098949 and flat vols of 0.2 have been entered manually.

Deriscope provides a live feed for the fx rate that is normally displayed in cell K13, but for this demo I have switched it off through the Boolean flag in cell K2.

The volatility is specified through a maturity-strike table, the values of which should be linked to a live feeds provider during production.

Using a volatility surface rather than a single vol value is important because its task will be to supply the suitable vol for any option on GBP/USD (or on USD/GBP) regardless of expiry and strike.

All three Market objects are assembled in the final object &GBP Market.1 in cell B5.

Each remaining sheet simply adds an object of Vol Curve.

The formulas are so designed that a simple copy-paste of the whole range suffices for adding an extra vol curve.

The very last sheet named Market is inserted on the left and serves to assemble the Market Set objects defined in the cells B5 of all sheets into one single object that in effect becomes a collection of collections. 

As you see, here in cell B5 is created the most important object of this workbook!

It is named &All Market.1 and its contents become visible in the wizard when the cell B5 is selected.

It is of type Market Set, which means it is a collection of objects of type Market.

Note that each shown Market element, for example the second one called &GBP Market.1, is itself a collection of more primitive Market objects.

Now I have two options:

Option 1: Use this object as input to various pricing formulas in this or another workbook in my current Excel session.

Option 2: Export this object as a text file so that anyone with access to that text file will be able to use it either in some other Excel session or even as part of a non-Excel process.

I will continue with the Option 2, which also demonstrates how Deriscope's export utility bestows the Excel-created objects with persistence.

The image below shows how this is done with the help of the Deriscope function Export of type Object Tools.

The function Export takes two inputs: A Reference= being the handle name of any arbitrary object (in this case the &All Market.1) and a File= being the full path and name that specifies the location of the text file about to be created.

The result is a new file in my F:\temp\ folder called All Market.txt.

The file is in xml format, its uncompressed size is 100 kB and its top section looks as follows in my notepad: 

It contains all information needed to recreate its progenitor object in any environment, Excel or not Excel, as I will soon show.

To summarize, this workbook acts as the generator of persistent (i.e. independent of the lifetime of the Excel session) market data that can be refreshed at will, as often as this workbook is recalculated.

In this example, the produced persistent market data are in the form of a text file of xml format, but they can easily assume any other form, such as json or a database entity.

They can be even linked to sockets that stream these data to dedicated clients.

Click on FxOptionNoExcel-1-Market.xlsx to download the spreadsheet produced with the above steps. 

Front Office: Pricing the Options 

Given a single and perhaps regularly updated All Market.txt file stored in some server-administered accessible location, many users in different locations can use it as their common source of market data for the purpose of pricing instruments like FX options in their own Excel sessions.

Let me now pretend I am one of these users and I want to build a trade blotter where I can enter as many options as I wish and calculate their price and risk without worrying about the job of building the required market data.

Knowing that all market data are accessible as a text file in the "server" location F:\temp\All Market.txt, I will build my spreadsheet so that a Deriscope object is created within Excel by reading the contents of that text file.

The Deriscope function that does exactly that is called Import and is part of the Object Tools type. It expects the full filename of the source text file and produces the corresponding object that is part of the current Excel session and can be used like all other objects created by regular Deriscope formulas.

Here is the Import function in action:

The object has been created in cell B3 and is identified by the handle name &All Market@.1, with the @ suffix serving as a visual indicator that this object is imported and therefore not linked to any other data in this spreadsheet.

Since I have selected its containing cell B3, the contents of that object are displayed in the wizard on the right.

The user has control over how often the market data object in cell B3 is generated.

In automatic mode, the respective ds() formula runs only once because its input parameters never change.

If it is desirable that the market data object is never altered, the additional optional Locked= input parameter may be utilized, which guarantees that the object is never modified or deleted after its initial creation. This mechanism can provide an additional layer of security with respect to the integrity of the used market data.

On the other hand, if it is desirable that the market data object gets updated with the latest server information when the spreadsheet is calculated, the function ds() should be replaced with its volatile equivalent dsv().

Now that the most complex object of all is in place, the construction of the trade blotter is literally a piece of cake and looks as follows: 

The structure is very simple:

Starting with row 12, every row corresponds to one FX option, with the blue color entries being entered by the user and used to specify the characteristics of the respective option contract.

Then certain formulas on the right, but still on the same row, create the respective object of type FX Option.

Finally, the formula shown in cell I12 calculates the price of that option.

It is straightforward to add columns that also report various risk elements such as greeks.

Adding additional options is as simple as copying and pasting a complete row to the empty area underneath. 

Front Office: Building and Exporting the Portfolio of all Options 

Next, I am facing the issue of dealing with the middle and back office controls on my consolidated positions.

Rather than sending my trade blotter spreadsheet to the various risk control groups, I can simply send them my whole portfolio as a text file through the same process I used earlier to create the text file for the market data.

As a matter of fact, there is no need for "sending" anything.

I can choose to add an Export function in my trade blotter spreadsheet so that my whole portfolio is stored as a text file in some accessible server location in real time.

My first job is to add a spreadsheet formula that creates an object of type Portfolio that contains all options in my blotter.

The image below shows this function: 

The Portfolio object is created in cell F3 as &Pfolio.1 and its contents are visible in the taskpane on the right because the cell F3 is selected.

The next image shows how I use the Export function to store this object's contents in a safe location in the form of a text file: 

The Export function creates the text file Pfolio.txt in the designated path F:\temp\ and returns the object shown as &Info_I3:1.1 in cell I3.

The latter object is of type Info and contains information about the outcome of the export operation. The contained information can be read on the taskpane as soon as the containing cell is selected. As you see, it consists of the sentence:

Contents of object [Prtfl] stored as xml file [Pfolio.txt] in folder [FPath].

As usual, the blue color labels represent clickable hyperlinks.

For example, clicking on [Pfolio.txt] opens and displays the created text file with my default text file reader (eg notepad), while clicking on [FPath] takes me with my default windows browser to the folder location where the created file has been stored.

Click on FxOptionNoExcel-2-TradeBlotter.xlsx to download the spreadsheet produced with the above steps. 

Risk Control: Accessing and Pricing the Portfolio of all Options 

The user who functions within the mandate of Risk Control, has the task of independently calculating the price and risk of the booked transactions.

He or she must be given access to both market data and book positions in order to fulfil the task of calculating the total NPV and all sorts of risk measures set forth by the company's internal and external regulations.

Pretending that I play that role, I will start building my spreadsheet so that both critical elements, the overall portfolio and the current or historical market data, are generated as Excel Deriscope objects within my Excel session so that I can analyze them afterwards with various functions.

To that intent, I use twice the same Import function I used earlier to import the market data in my trade blotter spreadsheet.

The image below is self-explanatory: 

Having created these two critical objects, I may add the formula that produces the price of the whole portfolio in cell B5, as shown below:  

This formula's output is the object &Variant_B5:1.1 in cell B5, which is of type Variant and contains several data that are all displayed in the taskpane because the cell B5 is selected.

The reason that such an object is produced rather than a single number is the entry Output= Full in row 11.

As I have described in fast all my previous posts, selected pieces of that Variant object can be easily displayed on the spreadsheet by simple formulas generated by the wizard.

This is exactly what I have done in cell B4, where a formula returns the element of the Variant output object that corresponds to the total portfolio price.

Further below and in a similar fashion, another array formula over the range B20:F23 returns the breakdown of the portfolio price into the prices of its constituent products.

Click on FxOptionNoExcel-3-Pfolio.xlsx to download the spreadsheet produced with the above steps. 

Pricing and Risk Management without Excel 

While the demonstrated portfolio pricing makes minimal use of Excel, it still runs inside an Excel session, a fact that makes it susceptible to side-effects that may lead to relatively unpredictable results.

For example, Excel generally reacts to user-initiated events, such as mouse movements. It is also often loaded with several third-party DLLs that may interfere with the Deriscope process.

Further on, the typical ultimate purpose is to feed the portfolio price and risk calculation to other IT systems. In that case, it makes little sense to use Excel as an intermediary that produces the risk figures and transports them to the final consumer. It is much more reasonable to ditch Excel completely, link the market, portfolio and modelling data to a stand-alone Deriscope executable and transfer the output of that executable to the final IT consumer system.

Next, I will demonstrate how the Deriscope executable is capable of pricing without Excel the exact same portfolio and produce the exact same results as in the case treated above.

All I need is a file called Deriscope_Xml.exe and a second supportive DLL file called Deriscope_KernelBase.dll in some folder. I have chosen to paste these two files in my F:\temp\ folder.

Then in order to replicate outside of Excel the portfolio pricing result, I need first to have all input information in xml format that can be parsed by the Deriscope executable.

Here comes handy a wizard utility that is capable of transforming the input to any Deriscope Excel formula to a corresponding xml file that can be subsequently used as input to the Deriscope executable in an Excel-free environment.

This utility is accessed when I select the cell that contains the target Deriscope formula, click on the Go button and choose the menu item Export Excel Formula as Exe Input.

In the image below, I am doing so while I have selected the cell B5 that contains the formula =ds(B6:C11), which produces the full pricing result of my portfolio. 

This action results to the creation of a text file named B5.txt (the name reflects the address of the cell that contains the exported formula) stored in my F:\temp\ folder.

This a 120 kB file that contains in xml language everything needed to carry out the portfolio pricing.

The syntax expected by the Deriscope_Xml command is very simple:

Deriscope_Xml <InputFile> <OutputFile>

The last argument may be omitted, in which case all produced output is diverted to my command prompt screen.

So, I open a command prompt at that folder location and enter the following command:

Deriscope_Xml F:\temp\B5.txt

as shown: 

and here is the result after I press the ENTER key: 

Unfortunately, the full extent of the output cannot fit the limited window of the DOS prompt, but you may still see at the bottom the following xml part:  

The <p> tag stands for pair, the <k> tag for key and the <v> tag for value.

It is now obvious that here I see the following two key-value pairs:

Value= 0.25874

Proc Time= 0.025

The first number equals (within a pre-specified 6-decimals precision) the total portfolio value (NPV) shown earlier in the spreadsheet.

The second number indicates that the calculation took 0.025 seconds.

Scrolling up to the top section of the output, I see the breakdown into the prices of the constituent options, which also agrees with my earlier spreadsheet result: 

It goes without saying that the shown text output can be utilized by a non-Excel IT system to either feed with numbers other processes or even display the portfolio pricing results on its own monitor or dedicated human interface window.

The same observation applies with the input data in file B5.txt.

These may have now been produced by some Excel utility, but they could have been also produced by an alternative IT system by either other processes or by manual input in that system's dedicated human interface.

In other words, the whole process can be run without Excel, from beginning to end! 

Bidirectional Dynamic Link between Excel and external IT System 

Here comes probably the most interesting part.

The last screenshots do not simply represent in text form the output of a non-Excel IT system.

They represent in text form Deriscope objects that carry a meaning within the Deriscope execution thread regardless of the involved interfaces.

These objects can then be casted to any desired interface!

This means, the produced text can be used to create proper Deriscope objects within Excel as well.

Here is the demonstration of this claim:

First, I go back to my command prompt – which represents my non-Excel IT system – and rerun the last command, but this time with an extra third argument typed in as out.txt.

I also leave the path part out of the parameters since the executable and all files are in the same folder.

Here is the setup and the result: 

This time nothing shows up because all text output was directed to the file out.txt.

Now I will start an Excel session and attempt to load the object represented by the out.txt file.

I have two options of doing that:

Option 1: Through a spreadsheet ds formula that involves the Import function as I have done before. Convenient when the imported object needs to be loaded more than once, in either the current or any other Excel session.

Option 2: Through the special Deriscope utility accessed through the Go button and its menu item Load Object from Text File. Convenient when the object needs to be loaded only once.

I will use the second option, as shown in the screenshot below: 

The result is the text $Variant@ pasted by the wizard in my selected cell B2.

There is no formula involved, just this text, which is the handle name of an object living in memory.

The fact that $Variant@ represents a valid object is confirmed by the fact that when its cell is selected, the wizard displays the respective object's contents, as in the screenshot below: 

Let me stress out the significance of this!

I have started with a text file out.txt that was created by some process in some IT system that had nothing to do with Excel.

Then Excel was able to make sense of this file and generate an object referred by the handle $Variant@.

In this demo, the object $Variant@ only contains the portfolio structure and price.

But it could easily represent something far more complex, such as the ensemble of portfolios, models and market data, all originally produced by some non-Excel IT system in its own valuation processing.

Then the fact that this ensemble could be also instantiated inside Excel would make it possible to replicate the external IT system's valuation processing in Excel as well, in a fully dynamic fashion that could run and produce identical results.

This is not just "exporting of data", but truly "exporting of dynamic processes", in the sense that any process that can run in the external IT system could produce an exported process that could then run in Excel and produce the same results. 

Verdict

So, what is better? Excel or non-Excel?

The usual argument that Excel poses a significant operation risk hides the fact that non-Excel poses a huge "timely implementation" risk.

In other words, what is preferable? Having an Excel-based solution with all its associated risks in a few days or aiming for a non-Excel-based solution with an expected but unknown delivery time of several weeks or months?

The issue of upgrades and bug fixes is also crucial.

While a spreadsheet can be fixed or modified almost instantly, doing the same with a non-Excel-based procedure amounts to a "project"!

An important consideration factor is the intended usage.

In dealing with standardized flow products such as stocks, bonds or vanilla swaps hardly arises the need for doublechecking the accuracy of the numerical results or setting up a custom analysis.

The story is very different with non-standardized products where the existing models cannot – and should not - be necessarily trusted.

Intelligent traders always check their numbers by independent "back-of-the-envelope" calculations, which can be easily carried out in Excel, but never in a non-Excel environment.

In the treated example above, the non-Excel Deriscope_Xml executable is a powerful alternative to the Deriscope Excel Addin in situations, where Excel interference should be preferably avoided and the primary purpose remains the interaction with other IT systems.

The Deriscope_Xml executable may be also used as the analytical engine to non-Excel user interfaces, such as specialized GUIs – even online based – where users do not need to conduct data analysis of the type usually done in Excel. For example, an online IRS swap pricer may be designed to link to the Deriscope executable with the important advantage that the whole setup can be out-of-the-box linkable to Excel in a dynamic and bi-directional fashion!

In terms of efficiency, the Excel-free pricing carries the drawback of being stateless. All Deriscope internal curve objects, for example, need to be created from scratch. Program-wide initializations also need to be carried out in full after each invocation.

In Excel instead, certain objects of which the creation involves heavy calculation (eg bootstrapping or calibration), may be kept unaltered in memory after their initial creation and provide their services to other areas of the spreadsheet as needed.

On the other hand, the stateless nature of Excel-free pricing makes the distributed execution of cpu intensive processes (such as VaR or XVA) over a network of connected machines a ridiculously simple task, since all invoked processes are completely independent from each other and no locking mechanism is necessary.

Putting everything together, I would argue that having a system – like Deriscope - that can be accessed both through Excel and outside Excel, is clearly the best approach that can serve all needs arising in practice. 

Click on FxOptionNoExcel-1-Market.xlsx to download the market data spreadsheet.

Click on FxOptionNoExcel-2-TradeBlotter.xlsx to download the trade blotter spreadsheet.

Click on FxOptionNoExcel-3-Pfolio.xlsx to download the remote portfolio pricing spreadsheet.

Feel free to contact me if you want to share any thoughts with regard to this product or if you want to request any particular features. Contact info and social media links are available at my web site https://www.deriscope.com