Котировки Форекс В 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,USD, ie. N_d = 1, 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.

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

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_d (number of DOM units) and N_f (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 spotfx 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_USD units of USD will be credited (settled) in the respective bank accounts 2business 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 2business 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. 

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, 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, 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 rateEUR/USD= whereby you give your 1, USD and receive 1,/ = EUR. Then you deposit the received 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 EUR will have grown to + 1%*(1 year)* = + = EUR. 

Strategy 2: Enter into a zero-cost agreement with a willing counterparty to rceive EUR on T by paying the fixed amount *x USD also on T. This agreement is by definition a forward fx transaction now on a EUR notional of with maturity T and forward fx rateEUR/USD= x. This transaction guarantees you the receipt of EUR on T (just like strategy 1), but also obligates you to pay *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, 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, USD will have grown to 1, + 2%*(1 year)* 1, = 1, + 20 = 1, USD. Since the forward fx contract obliges you to a payment of *x USD on T, it turns out that: *x = 1, => x = 1,/ =  

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_f, we derive the following formula that relates the spot fx rates and forward fx ratef with maturity T of a currency pair FOR/DOM:

f = s(1+ r_d)/ (1+ r_f)

where r_d and r_f 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 rater_d and in the opposite direction with the foreign interest rater_f. This is the reason why in our example the r_d = 2% being higher than r_f = 1% results to a forward fx rate of that is higher than the spot fx rate of .

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. 

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:

• eunic-brussels.eu incorporation of calendar and date bump conventions in determining the involved settlement dates cannot be easily carried out without an add-in support.
• eunic-brussels.eu required input interest rates r_d and r_f 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.
• eunic-brussels.eu briefly noted above, the IRP relation is not exact, at least when r_d, r_f 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_A 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 keysDom 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_A 

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

I mentioned earlier that the IRP relation f = s(1+ r_d)/ (1+ r_f) between the spot fx ratesand the forward fx ratef 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 , 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 USDdeposit 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. 

Since we cannot use the no-arbitrage argument between two equivalent strategies to derive the forward fx ratef out of a given spot fx rates, we accept the observed rates f_i, i = 1, …, n of n actively traded forward fx contracts with various maturities T_i as exogenous primitives. In other words, we do not try to compute the observed rates f_i 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 T_iwith 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 % 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_fi for each 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 ratef 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_f are found through the real-world domestic and fictitious fx-implied foreign yield curve respectively, using common interpolation techniques. 

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_G 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_G is the most important element.

Its value &YldCrvFxf_G 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_G and FX Value object &FXVal_G 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 , i.e. 20basis 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_G 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:  

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 keyIn 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_J 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 E, 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 rateEUR/USD of in cell N11

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 herehyperlink, I see the following: 

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 eunic-brussels.eu 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 eunic-brussels.eu