Using Spreadsheets for Netting: What Could Possibly go Wrong?
7 reasons to AVOID THE RISKS OF spreadsheetS
Using Spreadsheets for Netting: What Could Possibly go Wrong? 7 reasons to avoid the risks of using spreadsheets for multilateral netting. December 21, 2020 | Author: Nigel Cripps
One of EuroNetting’s earliest clients from the 1990s is a large multinational manufacturer whose name is among the best-known brands worldwide; we are proud to have this client still on our books, running a netting process that has naturally evolved over the years to incorporate custom data interfaces in and out of the netting platform.
This client’s monthly netting cycle currency positions after netting are still well in excess of $1 billion, and sometimes go over $2 billion. Quite sensibly these net positions are not being traded directly with banks, and instead they are laid off to the company’s internal trading desk’s currency positions and incorporated into their other hedging activities which are very substantial.
But the story of how this client entrusted their netting process to EuroNetting is very interesting.
The Spreadsheet Gamble Realizing there had to be a better way to settle their massive intercompany cashflows, this company decided they could design their own internal netting process on a spreadsheet: after all, the netting calculation from one currency to another is essentially a very simple concept, just repeat it a couple of thousand times with a set of predetermined exchange rates and you have the result.
And so, they tasked a couple of very bright young associates to design a spreadsheet-based netting solution. 6 months, and many aborted attempts, later, they emerged to say they just couldn’t get it working safely to handle a quite small set of 100 entities and 12 currencies, and moreover they couldn’t find a foolproof method to handle future extensibility in entities and currencies that was sure to come.
Through their bank, we gave them a free copy of the EuroNetting software (this was in the 90s, so it was a copy of our Windows client-server software back then) for evaluation. Two months later they bought the software, and subsequently migrated onto the web-based EuroNetting service in 2003.
Why the Spreadsheet didn’t Work All netting calculations rely on a set of currency rates and it actually is possible to design a netting calculation in a spreadsheet using macros, but only for a very limited and static set of entities and a small number of currencies and balances to be netted. Having seen many examples that worked and then failed, here are some reasons you might not want to go down the spreadsheet route:
The classic netting calculation is iterative: A traditional netting process ends up recalculating everything using exchange rates that have been locked in with your FX bank; however, you don’t know the exact positions that must be traded until those rates have been locked in. To zero in on those final positions, you must run a preliminary calculation using approximate rates and then trade approximate positions; after locking in the rates, you will then run another calculation and update the trade positions.
Where did those cross rates come from? Spreadsheet and macro developers often misunderstand what netting really is, and make the mistake of deriving large sets of cross-currency trade calculations representing what are actually just the bilateral positions between pairs of entities. The resulting large sets of FX trades are scarcely better than settling everything without a netting system at all. Another mistake is to assume that a cross rate table has to be calculated and then used in the netting conversion process. Absolutely not: this will result in rounding differences due to inevitable rounding in the cross rates.
Extensibility risk: An initial Excel macro approach to a netting calculation will likely use lookup functions on separate sheets containing lists of entities (and their base currencies), currencies, exchange rates, etc. How easily can these lists be extended or adapted? Is the process properly documented, or as is usually the case with spreadsheets, has it been put together with minimal instructions?
Calculation risk: Broken links are a risk in a complex spreadsheet following any modification, leading to incorrect calculations or incomplete results. Unlike some other spreadsheet analysis you use in treasury, incomplete or inaccurate netting calculations can have an immediate and very significant financial impact. Consider a spreadsheet which instructs you to buy EUR 5 million vs. the US Dollar at the current spot rate of 1.2175: it costs $6,087,500. And then you realize the spreadsheet put something the wrong way around, or maybe it just wasn’t indicated clearly enough if the trade is a buy or sell, and you should have sold the EUR; meanwhile the EUR has moved to 1.2142. Not only do you have to unwind the buy trade at the new spot rate yielding an instant $16,500 loss, but you are now selling the EUR at a worse rate. If this happens, do you have recourse to the developer? If the developer was an outside entity, does that provider offer a Service Level Agreement (SLA) for the tool, and indemnification against loss?
Missing features: Without even more complexity, a spreadsheet/macro tool is limited to a very flat netting model: hard to include FX hedge contracts in the calculations, difficult to generate offsets such as is typical in regional treasury centers running a cash concentration process as an adjunct to the netting system.
Change in netting administrator: A commercial netting provider will have full documentation and support for your netting system, and will have assisted in writing your in-house netting procedure. Even so, when a new netting administrator starts at a client, we know this person will often need some hand-holding through the first one or two netting cycles. Would you have the same depth of knowledge and support for a one-off system developed in-house, or worse, by an outside contractor? Having immediate access to a knowledgeable resource able to examine and explain your netting calculations can be very important.
The original developer departs or no longer supports the tool: This is actually the most common cause for a spreadsheet/macro tool to fall apart: everything goes fine for the first few netting cycles, and then the time comes to make some changes, or maybe someone has simply broken some links accidentally and you need to restore them. With a one-off tool you will be totally dependent on whoever created it, and if that person is no longer available it may have to be abandoned. To mitigate this risk you would have to maintain a support agreement with the original developer, which may cost just as much as licensing a commercial tool.
Overall, the 7 reasons above offer practical insights learned from real-world experience into the caution multinational corporations should take when deploying multilateral netting using spreadsheets. Our belief is that treasury management best practices entail the utilization of solutions that are stable, secure, and scalable. To learn more about the benefits a dedicated netting service can provide your global organization, please contact us to schedule a complementary consultation.