# 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.

### Table Of Contents

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

Get a free Deriscope activation code that enables you to run the accompanied spreadsheet!

### 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