FX Spots, Forwards, Swaps and Curves in Excel
Assume you possess Nd units of a currency DOM regarded as domestic currency. For example, you live in the US and hold 1,000 USD, ie. Nd = 1,000 and DOM = USD.
For whatever reasons, you want to replace this money with their equivalent number of units Nf of another currency FOR regarded as foreign currency. For concreteness think of FOR like if it were the currency EUR.
All you would have to do is going to a bank and exchange your 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. Nf = 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 Nd/Nf of the notional amounts Nd (number of DOM units) and Nf (number of FOR units) that are changing hands … as soon as possible after time 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 NEUR units of EUR and NUSD units of 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 rd and the EUR (foreign) interest rate of 1% with rf, we derive the following formula that relates the spot fx rate s and forward fx rate f with maturity T of a currency pair FOR/DOM:
f = s(1+ rd)/ (1+ rf)
where rd and rf are the non-annualized domestic and foreign, respectively, interest rates from 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 rd and in the opposite direction with the foreign interest rate rf. This is the reason why in our example the rd = 2% being higher than rf = 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+ rf)/ (1+ rd), 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 rd and rf 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.
- 3.As briefly noted above, the IRP relation is not exact, at least when rd, rf are interpreted as the actual domestic and foreign interest rates. An exact treatment does need an add-in.
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+ rd)/ (1+ rf) between the spot fx rate sand 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 fi, i = 1, …, n of n actively traded forward fx contracts with various maturities Ti as exogenous primitives. In other words, we do not try to compute the observed rates fi as the result of a mathematical formula.
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 Ti with the exact same uncertainty profile as the corresponding real-world domestic deposit contracts denominated in DOM. If we then denote with Rfi, 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:
fi = s(1+ rdi)/ (1+ Rfi) , i = 1, …, n
where rdi is the real-world domestic interest rate for the maturity Ti.
In the above formula, all except Rfi are market observables. Therefore, we can use this formula to derive Rfi for each i and refer to it as the fx-implied foreign interest rate for maturity Ti.
The collection of all Rfi 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 rf.
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 Ti by using the formula:
f = s(1+ rd)/ (1+ Rf)
where rd and Rf are found through the real-world domestic and fictitious 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 Rf 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+ rd)/ (1+ Rf), 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
