# FX Spots, Forwards, Swaps and Curves in Excel

Assume you possess *N _{d}* units of a currency DOM regarded as domestic currency. For example, you live in the US and hold

**1,000**

**USD**, ie.

*N*=

_{d}**1,000**and DOM =

**USD**.

For whatever reasons, you want to replace this money with their equivalent number of units *N _{f}* of another currency FOR regarded as foreign currency. For concreteness think of FOR like if it were the currency

**EUR**.

**1,000**

**USD**with whatever amount the bank would be willing to give you at that time. If the bank gave you

**877**

**EUR**(i.e.

*N*=

_{f}**877**), you would have just entered into a

*spot fx*transaction with the bank as a counterparty on the currency pair

**EUR/USD**involving the

*fx rate*of

**1,000/877 = 1.14**. The exact same transaction may be also described in terms of the inverse currency pair

**USD/EUR**with the corresponding

*fx rate*of

**877/1,000 = 0.877**.

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

### Understanding Spot FX Transactions

When referring to a currency pair FOR/DOM, the numerator FOR is referred as the *base* currency, while the denominator DOM is referred as the *quote* or *account* currency.

The *spot fx rate* of the pair FOR/DOM at a given time *t* is simply the ratio *N _{d}/N_{f}* of the notional amounts

*N*(number of DOM units) and

_{d}*N*(number of FOR units) that are changing hands … as soon as possible after time

_{f}*t*. Contrary to the above example of consumer finance, the actual exchange of currencies between banks is never conducted immediately after the agreement to the exchange. In wholesale transactions a typical settlement delay of

**two business days**applies, which makes the

*spot*transaction being - in reality -

*forward*in nature. Nevertheless, it is still regarded as

*spot*and not as

*forward*, due to the fact that – contrary to a

*forward fx contract*– the

*spot fx*transaction cannot be replicated with a same-currency borrowing (lending) of funds followed by a future

*spot fx*transaction that converts the borrowed (lent) funds to the final desired currency. The net effect is that the

*spot fx rate*cannot be derived from the domestic and foreign

*interest rates*, even though the currency settlement occurs in the future. It forms an additional

*stochastic factor*– usually correlated with the

*interest rates*– that needs to be modelled on a fundamental level along the other

*risk factors*of the economy.

As we will see, *fx rates* related to currency exchanges that settle later than the usual two business days can be derived - to a certain degree – from the *spot fx rate* and the prevailing domestic and foreign *interest rates*.

Most currency transactions carry very little risk because they are instantly collateralized by the fact that the two involved currencies exchange hands at the same time. This fact allows the banks to transact in huge notional amounts that necessitate accurate treatment of the various settlement dates. While, when dealing with stock options, it may be sufficient to identify the *spot stock price* with a single number, we should not do the same with the *spot* *fx rate *in our dealings with fx products. The devil is in the detail and one needs to properly account for the settlement period associated with a given *fx rate*.

The Deriscope Excel add-in supports an accurate *fx rate* representation by employing an object of type *FXValue*, the structure of which is shown below:

In the image above, the *fx rate* is represented as an object of type *FX Value* that includes a few additional keys beyond the usual *Base Currency=*, *Quote Currency=* and *Spot Rate=*. The interpretation of these extra keys is as follows:

Under the assumption that two counterparties agree to exchange **EUR** for **USD** at some date **T** referred as trade date, notional amounts of **N _{EUR}** units of

**EUR**and

**N**units of

_{USD}**USD**will be credited (settled) in the respective bank accounts

**2**

**business days**later due to the entry

*Settlement Days=*

**2**. The identification of the settlement date requires knowledge of the applicable calendar(s), the date bump convention and a rule that specifies how all these are used. The choice

*Settlement Rule=*

**Joint**means that the two given calendars must be merged into one by merging the two holiday sets and then move

**T**forward by

**2**

**business days**according to the merged calendar. This ensures that the produced settlement date is a business date with respect to both calendars. The

*Cross Calendar=*entry in this case is empty. It could play a role in

*cross fx rates*between

**non-USD**currencies, in which case it would be normally set to the

**US (New York)**calendar.

### Understanding Forward FX Transactions

It is often the case that you do not need a foreign currency right now, but at some specified future date. For example, you may be a New Yorker who plans to visit Paris on a future date **T**, which falls for concreteness exactly one year from now. You currently have **1,000 USD** at your disposal, but you fear the euro might become so expensive that you will not be able to buy enough of it in a few months' time. What you really want is using your **1,000 USD** now to secure the possession of a fixed number of euros by the date **T**. You have two (almost) equivalent choices on how to proceed:

**Strategy 1: **Enter a *spot fx* transaction now with the *spot fx rate* **EUR/USD= 1.14** whereby you give your **1,000 USD** and receive **1,000/1.14 = 877 EUR**. Then you deposit the received **877 EUR** at a special EUR-denominated account for one year that earns the EUR interest rate of **1%**. At maturity, i.e. on date **T**, the deposited **877 EUR** will have grown to **877 + 1%*(1 year)*877 = 877 + 8.77 = 885.77 EUR**.

**Strategy 2:** Enter into a zero-cost agreement with a willing counterparty to rceive **885.77 EUR** on **T** by paying the fixed amount **885.77*x USD** also on **T**. This agreement is by definition a *forward fx* transaction now on a **EUR** notional of **885.77 **with maturity **T** and *forward fx rate* **EUR/USD= x**. This transaction guarantees you the receipt of **885.77 **EUR on **T** (just like strategy 1), but also obligates you to pay **885.77*x USD** on **T**. In order to feel safe that you will be able to meet this obligation on **T**, you deposit now your available amount of **1,000 USD** at a USD-denominated account for one year that happens to earn the USD interest rate of **2%**. At maturity, i.e. on date **T**, the deposited **1,000 USD** will have grown to **1,000 + 2%*(1 year)* 1,000 = 1,000 + 20 = 1,020 USD**. Since the *forward fx* contract obliges you to a payment of **885.77*x USD** on **T**, it turns out that: **885.77*x = 1,020 => x = 1,020/885.77 = 1.1515**

Generalizing the above argument by replacing the **USD** (domestic) interest rate of **2%** with *r _{d}* and the

**EUR**(foreign) interest rate of

**1%**with

*r*, we derive the following formula that relates the

_{f}*spot fx rate*

*s*and

*forward fx rate*

*f*with maturity

**T**of a currency pair FOR/DOM:

*f = s(1+ r _{d})/ (1+ r_{f})*

where *r _{d}* and

*r*are the non-annualized domestic and foreign, respectively, interest rates from

_{f}**0**to

**T**.

This formula is known as the *Interest Rate Parity (IRP)* relation and unfortunately is … wrong, in a certain sense at least! More on that below.

Note that both *s* and *f* are the rates of the currency pair FOR/DOM. Under this precaution, we infer that *f* moves in the same direction with the domestic *interest rate* *r _{d}* and in the opposite direction with the foreign

*interest rate*

*r*. This is the reason why in our example the

_{f}*r*=

_{d}**2%**being higher than

*r*=

_{f}**1%**results to a

*forward fx rate*of

**1.1515**that is higher than the

*spot fx rate*of

**1.14**.

If you sometimes see the different formula *f = s(1+ r _{f})/ (1+ r_{d})*, this is because the rates

*s*and

*f*are defined to refer to the inverse currency pair DOM/FOR.

### Creating FX Forwards in Excel

Deriscope has functions that create special objects representing *fx forward* contracts. It also has functions that calculate both the price and the fair *forward fx rate* of these contracts.

You may wonder what the need is for a sophisticated Excel add-in in a situation where the involved formulas are as simple as the IRP formula above.

Three reasons can be cited:

- 1.The incorporation of calendar and date bump conventions in determining the involved settlement dates cannot be easily carried out without an add-in support.
- 2.The required input interest rates
*r*and_{d}*r*are normally unknown. They depend on the maturity of the forward contract and must be derived out of a set of known market prices of interest rate instruments. The latter process is too complex to be properly done with simple Excel formulas or vb code._{f} - 3.As briefly noted above, the IRP relation is not exact, at least when
*r*,_{d}*r*are interpreted as the actual domestic and foreign interest rates. An exact treatment does need an add-in._{f}

The following short video shows how I can use the Deriscope wizard to create an Excel object of type *FX Forward*:

The wizard pasted the formula **=***ds(A2:B7)* in cell **A1** that takes as input the single range **A2:B7** and returns the text &FxFwd_A1:1.1 which is the handle name of a newly created object of type *FX Forward*.

The input data are arranged as *key/value* pairs, where the *keys* are on the first column and carry the = suffix and the *values* are on the second column. The two top *keys*

*Type=* and *Function=* are universal (apply in all contexts), whereas the remaining four *keys* *Dom Ccy*=, *For Ccy*=, *Maturity*= and *Strike*= pertain only to objects of type *FX Forward*.

The wizard has generated the defaults *Dom Ccy*= %USD and *For Ccy*= %EUR because my pc is set with the US locale, but I can obviously change them to any other currencies I want.

The image below shows both this spreadsheet formula and the wizard on the right, which displays the in-memory-held contents of the object referenced by the handle name &FxFwd_A1:1.1.

### Simple Pricing

Creating a simple formula that calculates the price of this product is straightforward, as the next video demonstrates:

Below you see the two formulas as pasted by the wizard in cells **D1** and **D6**. All market data are grouped together in a separate object of type *FX Forward Mkt* created in cell **D6**. This object is then fed into the formula in cell **D1** that returns the price of 0, as it should, given the fact that our *fx forward* product in cell **A1** has been constructed with a strike of **1.1396** that happens to match exactly the *forward fx rate* implied by the input interest rates in cells **D9** and **G10**. You may also have noticed that the wizard has created flat yield curves with the same rate of **4%**. (Don't worry about the unrealistically high value of EUR rate. It may soon reach such levels!) It is up to me now to substitute each **4%** with the actual interest rate that applies to the maturity and currencies I am dealing with. I can even replace these two flat curves with other more realistic curves that bootstrap market data as described in my articles about yield curves.

###
Setting up a Rate-Price Dependency Table

Deriscope's *Price Simple* function may also be used to calculate the price after one or more of the inputs have changed. This can be done in one single step without the need to reconstruct the input block ranges. For example, I could be interested in making a plot of the fx forward price versus the EUR interest rate. I would then start with a column of assumed EUR rates, as below:

My next step would be to enter a formula in cell **B15** that would return the fx forward price taking into account the EUR Rate supplied in cell **A15**. The next video shows how I may use the wizard to create such a formula:

Finally I copy the formula in **B15** and paste it to the cells below to get the price dependence on the EUR rate:

### Why is the Interest Rate Parity Relation Wrong?

I mentioned earlier that the IRP relation *f = s(1+ r _{d})/ (1+ r_{f})* between the

*spot fx rate*

*s*and the

*forward fx rate*

*f*with maturity

**T**of a currency pair FOR/DOM is wrong.

It is very simple to see why this is the case.

The IRP relation is derived from the assumption that the two mentioned strategies that end up with the possession of a certain fixed amount of foreign currency FOR at maturity **T** are equivalent. __It turns out this assumption is wrong__. Here is why:

The first strategy involved the immediate acquisition of FOR through a *spot fx* transaction, followed by a FOR-denominated *deposit* with maturity **T** of the acquired FOR amount.

The implicit assumption that the FOR-denominated *deposit* will survive all the way until **T** is clearly not vindicated by the recent credit deterioration of several banking and sovereign institutions. For example, if FOR equaled **EUR** and the deposit was with a Greek bank shortly before Sunday the 28th of June 2015, the day Greece introduced capital controls, you might have faced serious problems with getting access to your money at time **T**.

The second strategy involved an initial DOM-denominated *deposit*, followed by an fx conversion from DOM to FOR at time **T**, with the applicable currency conversion rate fixed at time **0**.

Here the risks are completely different:

The first obvious risk is similar in nature with the one mentioned at the first strategy, namely the risk that the DOM-denominated *deposit* will not survive to a happy end by time **T**. Being "similar in nature" does not mean the two risks are equal. For example, if DOM were **USD**, one could perhaps think that the US bank where the **USD** *deposit* were to be held would be more trustworthy than the European bank where the **EUR** deposit would normally sit.

Then there is also the risk that the final conversion between DOM and FOR at **T** would not be possible, due to whatever reasons. For example, President Trump could ban, shortly before **T,** all **USD-EUR** conversions carried out by US-based commercial banks to protect the US dollar. Utterly unlikely of course, but it serves to make the point.

In summary, both strategies lead to a final FOR-denominated **uncertain** amount, but with a **different** risk profile. __Therefore, the two strategies cannot be considered as being equivalent__.

### The Correct Pricing of FX Forwards

Since we cannot use the no-arbitrage argument between two equivalent strategies to derive the *forward fx rate* *f* out of a given *spot fx rate* *s*, we accept the observed rates *f _{i}*,

*i*= 1, …,

**n**of

**actively traded**

*n**forward fx*contracts with various maturities

*T*as exogenous primitives. In other words, we do not try to compute the observed rates

_{i}*f*as the result of a mathematical formula.

_{i}Our next step is to assume the existence of a fictitious foreign interest rate market that allows us to enter into deposit contracts denominated in FOR that pay the deposited amount at their respective maturity *T _{i}*

__with the exact same uncertainty profile__as the corresponding real-world domestic deposit contracts denominated in DOM. If we then denote with

*R*,

_{fi}*i*= 1, …,

**n**the non-annualized interest rates of these fictitious FOR-denominated deposits, then the two strategies would be 100% equivalent and the ensuing no-arbitrage argument would lead to the relation:

*f _{i} = s(1+ r_{di})/ (1+ R_{fi}) *,

*i*= 1, …,

**n**

where *r _{di}* is the real-world domestic interest rate for the maturity

*T*.

_{i}In the above formula, all except *R _{fi}* are market observables. Therefore, we can use this formula to derive

*R*for each

_{fi}*i*and refer to it as the

*fx-implied foreign interest rate*for maturity

*T*.

_{i}The collection of all *R _{fi}* for

*i*= 1, …,

**n**constitutes a yield curve of fictitious foreign interest rates – referred as the

*fx-implied foreign yield curve*- that is different than the

*yield curve*of real-world observed foreign rates

*r*.

_{f}We may now calculate the *fx forward rate* *f* associated with any maturity **T** that does not necessarily coincide with one of the market maturities *T _{i}* by using the formula:

*f = s(1+ r _{d})/ (1+ R_{f})*

where *r _{d}* and

*R*are found through the real-world domestic and fictitious

_{f}*fx-implied foreign yield curve*respectively, using common interpolation techniques.

### Creating the FX-Implied Foreign Yield Curve out of FX Forwards

The next video shows how I can use the wizard to paste in the spreadsheet the formula that creates an object of type *Yield Curve* out of market *fx forward rates*. The interest rates associated with the produced curve will be exactly the rates *R _{f}* that must be used as input wherever foreign interest rates are needed in the price calculation of various fx products, such as

*fx forwards*.

The following image shows the formula =*ds(G2:K5)* pasted by the wizard in cell **G1**, which returns the handle name **&YldCrv_G1:1.1** that represents an object of type *Yield Curve*.

The denomination currency is set by the *key-value* pair *Currency=* **%EUR**.

The *key-value* pair *Market Data=* **&YldCrvFxf_G7:1.1** is the most important element.

Its value **&YldCrvFxf_G7:1.1** is the handle name of an object of type *Yield Curve Fxf* created below in cell **G7**.

The latter object is constructed out of the domestic *Yield Curve* object **&YldCrv_G19:1.1** and *FX Value* object **&FXVal_G25:1.1** that have already been created earlier in cells **G19** and **G25** respectively.

It is also set with *Quoting*= **Spread**, which means the *forward fx rates* in the table further below are entered as differences *f*-*s* (forward – spot).

The wizard generates a simple table of input *fx forward rates* consisting of only two tenors - **%1M** and **%2M** - with a flat spread of **0.002**, i.e. **20** **basis points**. I can obviously insert additional rows and edit the **#Quote** column so that the actual market rates are included.

I can see the exact dates of the included forwards by selecting the cell **G7** that contains the handle **&YldCrvFxf_G7:1.1.** Then the contents of the respective object are displayed in the wizard as follows:

Next, I click on the lens sign next to the key _*Details*= and I can see the exact start and end dates of each forward contract as well as the *fx forward rate* in natural quotation:

###
FX Swaps

As betrayed by the IRP relation *f = s(1+ r _{d})/ (1+ R_{f})*, the possession of an

*fx forward*contract exposes its holder to three different sources of risk: Domestic interest rates, foreign interest rates and fx rate.

It is possible to remove the currency risk and limit the exposure to interest rates alone by adding a *spot fx* transaction with reverse movement of currency funds. The portfolio consisting of an *fx forward *contract together with a spot currency exchange is termed *fx swap*.

You should not confuse an *fx swap* with a *currency swap* – also termed as *cross currency swap* -, which is a completely different product explained in detail in my Currency Swaps and Basis Curves post.

An *fx swap* can be also regarded as a simultaneous borrowing and lending transaction, whereby one of the two swap participants, borrows in one currency and lends in another currency.

Deriscope allows you to create an object of type *FX Swap* as the next video shows. By default, the wizard generates a formula that creates an *FX Swap* of the *Borrower* type, which is a swap, where the foreign currency is borrowed and the domestic currency is lent. In other words, a *Borrower FX Swap* with maturity *T* entails a *spot fx* transaction whereby FOR is received today against DOM and a reverse *forward fx* contract whereby FOR is scheduled to be paid at *T* against DOM.

Below is the spreadsheet formula created by the wizard:

Due to the cell **J1** being selected, the contents of the referred object are displayed by the wizard on the right.

The exact definitions of the various *key-value* pairs can be displayed by selecting the respective *key* cells.

The special *key* *In parent: Multi Leg Swap=* appears because the type *FX Swap* inherits from the more general type *Multi Leg Swap*. Putting it simply, the created object with handle name **&FxSwp_J1:1.1** is of type *FX Swap*, but is also of type *Multi Leg Swap*.

In fact, an *FX Swap* is modelled as a *Multi Leg Swap* consisting of **2 legs**, whereby the first leg contains all domestic currency cash flows and the second leg contains all foreign currency cash flows. I will soon post an article that describes the *Multi Leg Swap* in more detail.

For now, an interesting *read-only key* is the _*CashFlows*= appearing at the bottom of the wizard. If I click on its lens sign, the wizard displays all cash flows in chronological order, as shown below:

Each row corresponds to a separate cash flow.

The meaning of each column is self-evident from the respective title.

The cell in last column contains an object that describes the associated cash flow in full detail.

I have already explained here how I may transfer these data in the spreadsheet.

I can easily price my *fx swap* as the next video demonstrates:

The produced spreadsheet formula is pasted in cell **M1** as shown below:

The green-colored cells in rows **2** and **4** contain simple spreadsheet links to the cells where the respective objects are created. This is because the wizard notices that the objects already exist somewhere in the spreadsheet, so it does not bother to recreate them!

The price of the *fx swap* is calculated as **3.10826E-15**, which is practically equal to **0**. This is because the notionals in cells **K5, K6, K8** and **K9** have been set by the wizard in accordance with the *forward fx rate* **EUR/USD** of **1.1396** in cell **N11**

### Additional output data

During the price calculation, QuantLib/ORE also computes certain values that can be of interest.

The *Info Area* of the wizard in the image above displays the sentence "*To display extra pricing data click here". *If I click on the *here* hyperlink, I see the following:

### Advanced Topic: Following the pricing algorithm step-by-step

It is also possible to track the pricing algorithm that has been run in order to produce the observed output and also see the actually used intermediate QuantLib and ORE structures and numerical outputs as described in the respective section of my Interest Rate Swap article.

Click on **FxFwdSwapCrv.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