Valuing a Portfolio of Multi-Currency FX Options and Producing its Value at Risk in Excel using Deriscope
The topic here is not about simple option pricing but rather about dealing with the complexity introduced by the simultaneous existence of several different currencies in the context of calculating the Price and Value at Risk of a portfolio of European FX options.
If you are not familiar with the basics of European option pricing in Excel using Deriscope, you may visit this introductory article.
I will show you a spreadsheet with three European FX options, the currencies of which can be freely set to any custom combinations.
In the simplest and wholly trivial case, all currencies may be set to equal each other. For example, I may set the underlying currency pair of each option to be the pair USD/USD, which is a trivial pair of zero volatility and a spot FX rate that steadily equals 1. The ensuing options would then have a payoff equaling some fixed amount depending only on their strike and direction (call or put) and therefore would behave like zero bonds.
On the other side of the complexity spectrum, all currencies could be set to be unique and distinct from each other, which would give rise to six different currencies to cope with.
As a matter of fact, the number of currencies could increase to seven if I also require that the price of my portfolio is expressed in a still different seventh currency.
The same observation applies to the VaR (Value at Risk) report, which can be expressed in any desired currency, not necessarily in one of the currencies already referenced by the options.
The spreadsheet is built in an intelligent way so that the output immediately reflects my currency choices, without the need of any formula adjustments.
While it is not meant for production, I think its simple design – all data in a single sheet for easier view of the dependencies - can serve well the educational purpose of understanding the interplay of the currency markets in calculating the price and risk of the portfolio.
Recommended for Deriscope starters: The Overview and Quick Guide pages.
Input Data Section
This is the driver seat of the spreadsheet.
All blue color data can be edited.
I start with a set of seven currencies defined in range C6:C12 that provide the currency choices in the construction of the three FX options directly below in range C16:E20.
The column to the right (range D6:D12) contains the spot FX rates for the given currencies against USD. I could have used formulas here that return live feeds from a provider such as Bloomberg or even the Deriscope-supplied function =dsLiveGetSync("YF","ABCUSD=X") that outputs the real time spot rate of the currency pair ABC/USD as received from Yahoo Finance.
These rates are then used elsewhere in the spreadsheet to construct the applicable cross FX rate between any two currencies.
The third column (range E6:E12) contains the effective flat discount interest rates that are supposed to apply with respect to each currency. Here I have copied the short term "risk-free" rates from https://tradingeconomics.com.
This is obviously a simplification since the applicable rates will in generally depend on the respective option expiries.
The accurate treatment would have the shown entries replaced with objects of type Yield Curve that are produced elsewhere in the spreadsheet with respect to the shown currencies.
The range C16:E20 defines the static data of my three options – one column per option.
The Base Ccy and Quote Ccy refer to the numerator and denominator currencies respectively in the currency pair referenced by each option.
I may choose any currencies I want here, by using the in-cell dropdowns, as shown with the cell D17.
I have chosen to form three options with the currency pairs EUR/JPY, AUD/CHF, GBP/BRL in order to deal with the maximum complexity case where all currencies are different. I do not involve USD here because I plan to use it later as my P&L reporting currency.
The lowest three rows contain editable entries that define the expiry, direction and strike of the options.
Finally, the bottom row supplies the volatilities of the respective currency pairs.
Although manually entered here, optimally formulas should provide the vols that are appropriate for each pair and expiry.
Single Option Pricing Section
Still on top of the sheet and to the right of the INPUT DATA SECTION, I reserve an area for the calculation of any single option I choose to select out of the three already defined options.
I carry out my option selection in cell H5, where an in-cell dropdown allows me to select a number from 1 to 3.
Here I have selected 3, which refers to my third option, which is an option on the currency pair GBP/BRL.
I do not need to display the label "GBP/BRL" since the involved currency pair becomes immediately visible as part of the handle name &GBP/BRL Option.1 shown in cell I9.
This represents perhaps another great aspect of Deriscope, namely the ability to give custom names to objects that visually indicate the nature of these objects. Choosing well defined names can have a big impact on the production time and maintenance of a spreadsheet!
The option NPV is computed by the formula =ds(H9:I13) in cell H8 as 0.125525867.
The dependency arrows show the relationship between this formula and the other two formulas in cells H15 and H21 that produce the required Model and Market inputs.
At the very bottom, the required Market objects are shown in green color.
Their handle names reveal their identity without the need to look up the spreadsheet section where these objects are created.
So, the first object named &GBP Int Crv.1 refers to the interest rate yield curve associated with the currency GBP.
The object named &GBP/BRL Vol Crv.1 refers to the volatility curve associated with the currency pair GBP/BRL.
The object named &GBP/BRL Spot.1 refers to the spot FX rate associated with the currency pair GBP/BRL.
The spreadsheet is so designed that all these objects are automatically adjusted if I change either the options' underlying currencies or the currently treated option through the number in cell H5.
Portfolio Pricing Section
Further to the right comes the PORTFOLIO PRICING SECTION, where the object &My Pfolio.1 of type Portfolio is created in cell L5 with the formula =ds(L6:M8,L10:M13).
The pricing formula in cell L18 is designed to return the object &Variant_L18:1.1 thanks to its input Output= Full.
Alternatively, I could have set Output= Price to get the price directly as a numerical output, but the Full choice is here more powerful because it makes the returned &Variant_L18:1.1 object to also contain the breakdown of the portfolio value in its constituent single options, which I display below over the range L28:P31 with the help of the array formula
=ds("ObjectTools::Show","Reference=",$L$18,"Key=","Breakdown","ExpandFinal=",TRUE).
As usually, the later formula has been conveniently created with the wizard.
Both the portfolio price and the set of single option prices shown at the bottom are denominated in the report currency USD chosen with the help of an in-cell dropdown in cell M16.
If I click on cell M16 and choose another report currency, for example JPY, I get the following:
Value at Risk Section
The setup here is very similar to that used in the calculation of the portfolio price.
Given the already constructed Portfolio object &My Pfolio.1, its local function VaR is invoked by entering VaR in cell S8 next to the key named Function=.
Precise details over how this VaR formula can be created using the wizard and what the meaning of its various inputs and outputs is, may be found at the Value at Risk article at the Risk Management Guru site.
I am still using the same inputs for the keys Reference=, Models= and Markets=, as in the pricing case.
The only new input is the pair VaR Spec= &VaRSpec_R31:1.1 because it is required by the function VaR.
The object &VaRSpec_R31:1.1 is created further below in cell R31 and contains all specifications needed by the VaR algorithm. It includes the object &SimMdl_R14:1.1, which is created in cell R14 and describes how the Monte Carlo simulation is carried out.
My most important simulation setting is the usage of a Pseudo Random number generator in cell S18 and the number of 1,000 scenarios in cell S19.
In cell S38 I choose a slightly elevated Report Level setting of Level 1 so that the VaR output appears as the object &VaRRep_R6:1.1 of type VaR Rep in cell R6, instead of the usual single numerical result.
You can see the contents of the object &VaRRep_R6:1.1 inside the wizard, which are automatically produced as soon as the cell R6 gets selected.
The main result - the VaR itself – is shown as 0.019623385 just above the VaR Rep output object in cell R5 through the simple formula (not shown in the image):
=ds("ObjectTools::Show","Reference=",$R$6,"Key=","Report VaR")
Note I have used only 1,000 scenarios rather than the usual 10,000 in order to keep the calculation time short for the purpose of this demonstration.
As you see in the wizard, the calculation time here has reached 36 seconds, which means that 10,000 scenarios would have taken around 360 seconds, or 6 minutes.
One possible way of improving the efficiency of the VaR calculation could be to switch over to a Sobol quasi random number generator by setting Low Discrepancy in cell S18.
If you read my article on Pricing Derivatives using Quasi Monte Carlo and Parallel Processing, you will notice that by using Low Discrepancy one may achieves with only 1,000 scenarios comparable numerical accuracy as when one uses Pseudo Random with 10,000 scenarios.
Just like with the portfolio NPV, you can compute the VaR denominated in any other currency.
For example, setting JPY in the cell M16 that controls the chosen report currency of the portfolio NPV, the Reference currency in cell S9 changes to %JPY because that latter cell is linked to M16.
After recalculation, the result appears as below.
It is worth to note that although the numerical VaR changes as expected, the percentage expressions Report VaR%= and Actual VaR%= change only minimally from 0.0753 to 0.07653.
Controlling the Diffusion Parameters
The most critical aspect of the Monte Carlo-based VaR calculation is the assumption regarding the number and kind of risk factors that are assumed to be stochastic and therefore are responsible for the uncertain character of the future portfolio NPV as of the VaR horizon date and thus the commensurate emergence of the Value at Risk concept.
In Deriscope, this fundamental assumption is part of the input Var Spec object &VaRSpec_R31:1.1 and comprised by the two tables shown below:
It is up to my discretion on how to setup these two tables.
Usually – but not always - each row corresponds to one risk factor, identified by the green color object shown at the leftmost column.
Deriscope only requires the existence of at least one risk factor.
This means, I could have setup my two tables so that they consist of a one single row – excluding the top title row -, for example the existing shown top row linked to the Yield Curve object &EUR Int Crv.1.
If I had done that, the simulation would have run by diffusing only the EUR flat interest rate, while keeping all other market data constant.
Here I have instead chosen to treat all involved market data as being stochastic.
This brings the total number of risk factors to 16.
The handle names make the identity of these 16 risk factors quite transparent.
So, the top 7 rows clearly correspond to the interest rates associated with my 7 currencies.
The next 3 rows correspond to the volatility curves of my 3 currency pairs.
The bottom 6 rows correspond to the required FX spot rates, which obviously constitute the biggest determinants of the portfolio's risk.
In other words, all independent FX spot rates (6 in total), the 7 interest rates and the vols of the involved 3 currency pairs are assumed here to be stochastic and undergo diffusions with the specified volatilities and pairwise correlations.
The entry Parallel on the second column means that the respective market data is to be diffused in a parallel sense, which means that during diffusion the whole curve is shifted up or down in a parallel fashion, and thus controlled by a single risk factor.
Deriscope supports more complex diffusions where the curve is diffused in a manner that is maturity dependent.
If I had chosen a non-parallel type of diffusion, then I would have had more than one risk factors associated with the market data on the row where the non-parallel treatment would have applied. But it is better to keep things simple for now, so that all second column entries are set to Parallel.
What is really important here, is the #Vol column, where the volatility of the respective diffusion is defined.
For this demonstration I have entered 0.2 (i.e. 20%) everywhere, but in a real application I should enter the historical-based estimates of the respective volatilities.
Finally, the correlations among my 16 risk factors are specified in the square symmetrical matrix on the right.
Right now, this matrix is set as a unit diagonal square matrix because I am too lazy to search for the historical correlations among my factors. But again, in a real application the upper-diagonal part of this matrix should be populated with reasonable market values.
Probability Density of Portfolio Loss
As I have described in utmost detail in the links provided above, Deriscope is capable of producing various charts, such as that of the probability density of the portfolio loss realized at the horizon date. This latter chart is particularly important because it relates to the essence of the VaR definition as the loss amount that corresponds to a specific quantile of the loss distribution.
All I need to do to produce the chart is to set Report Distribution= TRUE in the construction of my Model[Simulation] object in order for the VaR output object to include the two-column array that contains the x and y coordinates that form the source data of the probability density chart.
Then I simply paste – using the wizard – this array on the spreadsheet in the form of a dynamic formula linked to the source VaR output object and finally I use Excel's native chart facilities to create the chart.
Below you see the very simple array formula
=ds("ObjectTools::Show","Reference=",$R$6,"Key=","LossDensity","ExpandFinal=",TRUE)
which links to only one cell, the cell R6 that contains the VaR output object.
This formula effectively extracts the two-column array associated with that object's key "LossDensity".
Even though this formula is self-explained and intuitive, I created it using the wizard, which is much simpler than typing the whole formula by hand.
The chart looks somewhat bumpy due to the low number (1,000) of scenarios.
It would have looked substantially smoother if I had used the industry standard of 10,000 scenarios.
Click on FxOptionAndVaR.xlsx to download the spreadsheet produced with the above steps.
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
