Building an In-house Solution to Cash-Flow-at-Risk

By Vincent Delort, Risk and Reporting Manager, JTI

While some companies will spend tens of thousands of dollars on a vendor solution to compute Cash-Flow-at-Risk, JTI (Japan Tobacco International) has implemented a low-cost, in-house solution using Excel. Vincent Delort, Risk and Reporting Manager at the company, shares the inside track on the project – including the precise Excel formulae used.

Foreign exchange (FX) risk is rather like the proverbial banana skin. You know it’s there and that if, or when, you fall on it, there will be painful consequences. But with FX, you’re not in a position to pick the banana skin up and remove it entirely; instead, you must look for ways to reduce the impact in case you fall on it. This means working out what injuries you might sustain and determining the best protective clothing to wear to minimise any damage.

My role at JTI centres around this concept, with the aim of reducing the risk that the company’s financial results are impacted by adverse currency movements. A good way of measuring this risk is using a metric called Cash-Flow-at-Risk (CFaR). This is a measure of the potential maximum loss in the value of expected cash flows resulting from an adverse market move, within a given confidence level for a given time horizon (see box 2 for more detail on inputs). The beauty of CFaR is that it results in just one number, which is relatively easy to understand and explain.

The journey begins 

JTI had previously looked backwards to see where currency exposures had come from, but, around five years ago, my team and I began seriously entertaining the possibility of using and reporting CFaR on a regular basis – to take a more prospective approach to risk management.

We started by looking at CFaR solutions from TMS vendors, as we assumed that a third-party tool would be viable and relatively easy to implement. After a few months of scoping, however, it became apparent that the vendors in question were unable to deliver a solution that could be sufficiently adapted to JTI’s specific needs. What’s more, the price tag attached to such a solution was significant.

As a result, we embarked on a project to build a low-cost, in-house solution for computing CFaR using Excel. So how did we do it?

[[[PAGE]]]

Honing the methodology

First, we had to decide which methodology would be most appropriate for us to use in calculating CFaR. Like VaR, there are three ways to compute CFaR:

Each of these has its pros and cons, as we discovered by testing them out in turn. 

We started with the historical method. As the name suggests, this looks at the past to forecast the future. As such, calculating the CFaR is simply case of plotting actual historical returns, over, say, a 10-year period, and identifying the worst 5% (using a 95% confidence level) – this figure is equal to the CFaR.

The challenge with this method is that it is based on a huge assumption: namely that history will repeat itself and that the risks of the future will exactly match the risks of the past. Conducting some tests with this method, we found that it often resulted in overestimating the CFaR – partly because 10 years of history includes the 2008 financial crisis. We quickly discounted this method.

Next, we moved on to testing the parametric method, also known as the variance-covariance method. This uses probability theory to compute a maximum loss – and involves a simple calculation, based on the assumption that the returns of the currency pair in question follow a normal distribution:

𝐶𝐹𝑎𝑅 = (𝜇 − 𝑧𝛼 × 𝜎) × 𝑃

This is easily broken down into its constituent parts:

 𝜇 = the drift, which is the average return (see box 3 for the formula JTI uses)

 𝑧𝛼  = the left tail a-percentile of a standard normal distribution (where 𝛼 varies according to the confidence level used)

 𝜎 = the standard deviation of returns, i.e. the volatility (see box 3 for the formula JTI uses)

 𝑃 = the portfolio, which is the value of your cash flows

To achieve the values for 𝜇 and 𝜎 JTI chose to calculate these using actual historical data. While it is possible obtain implied volatility, which is derived from swap pricing, it is only available for the most liquid currencies. Since JTI works with more than 25 currency pairs, a number of which are not terribly liquid, we felt it was better to take a consistent approach and use the historical data because it is available for all of the currencies in which we work – and we can calculate it ourselves. 

As the above formula illustrates, the parametric method is relatively simple to calculate, but arguably that is also its downfall. We felt that it was perhaps too simplistic and rigid for JTI’s needs. The formula cannot be tweaked and therefore it is not possible to factor in your own currency predictions – such as a devaluation happening on a specific date, for example. What’s more, the parametric method cannot simulate non-linear return scenarios, for instance, if you hedge your exposures with options. 

In addition, the method is ‘path independent’, which means that there is absolutely no link between two different calculations you perform. So, while you might perform a 5-month CFaR and a 6-month CFaR, the methodology cannot link the two results because it does not take into account anything that happens between now and the end point of the calculation. This means that it is only suitable for simple scenarios, and not powerful enough to meet JTI’s needs. 

The real deal 

Finally, we tested – and decided to use – the Monte Carlo simulation method. In a nutshell, this simulates market movements using random variables. The method relies on an algorithm that undertakes random sampling to obtain numerical results, and we assume for the calculation that the return and volatility of the currency pair in question follow a normal distribution.

Fig 1:  Monte Carlo method

The widely accepted mathematical model we chose to run the Monte Carlo simulation was Geometric Brownian Motion (a continuous-time stochastic process often used to calculate the evolution of an asset price). This resulted in the following formula in figure 1, A. For the sake of clarity around the expected return input used in that formula, see figure 1, B.

The formula enables us to calculate the spot of tomorrow based on the spot of today, the drift, the volatility and a random element. We assume that ∆t is one day. 

By doing this recursively, using the spot of tomorrow to calculate the spot of the day after tomorrow and so on, you can create a year of spots (or however long your desired timeline is). This full year of spots represents just one simulation. If you then start from the beginning again, the random element will ensure that each new path gives you a new result – and ideally, you need to be performing at least 10,000 simulations to get a reliable result. 

From those 10,000 scenarios, you then take the 5% (assuming a 95% confidence level) that give you the smallest result, and in turn, that gives you your CFaR.

Worth the effort

The Monte Carlo method of calculating CFaR is extremely powerful and can model very complex scenarios. On the flipside, however, it can be challenging to implement (certainly from scratch as we did) and it also has a tendency to be quite resource-heavy. We have built an Excel model to run the simulations (see box 5) but it takes one hour to run 5,000 simulations – and more than 16 hours to calculate the marginal contribution of each currency. 

It took around six months for us to fully implement the in-house CFaR solution, including asking two of our banks to verify our model. They confirmed it was correct and suggested only very minor tweaks to the methodology, which we were able to easily incorporate.

So, has it been worth the effort of implementing an in-house solution to CFaR? Absolutely. Using our own model has a number of benefits, aside from the obvious cost savings. For starters, we have been able to tailor the model to meet all of our needs, even adding ‘nice-to-have’ features such as being able to model specific scenarios (progressive or abrupt devaluation, change in volatility over time, using different accounting treatments on derivatives) or filter the results to see the risk on a single currency level. It is completely flexible.

By constructing the model ourselves, we have also gained a much better grasp of the theory behind it, which in turn makes it easier to explain the monthly CFaR output to executives, such as the CFO, in a clear and precise manner. This alone is worth the effort.