# USD Interest Rate Swap: Cash Flows and DV01 in Excel using Bloomberg Market Data

In this post I will make use of the realistic yield curves I built in Excel out of Bloomberg *OIS, deposit, futures* and *Libor swap rates* as of **May 22, 2019** towards calculating the price and producing the detailed *cash flows*, *flat DV01* and *maturity-dependent DV01s *of a bespoke *forward starting 3-year swap*.

You may want to visit the respective article, where I show how both the risk-free discounting and forecasting curves are built using a semi-dual, exogenous discounting curve approach.

For reference, below are the two sections of my spreadsheet where the discounting and forecasting curves are respectively created. The handle names with the red color are output of Deriscope formulas and reference the respective created objects.

### Creation of the Bespoke Forward Swap Object

If you are not familiar with using the wizard to generate the spreadsheet formulas that create an interest rate swap, you may visit my post about *non-standard interest rate swaps*.

Below you see the three spreadsheet formulas required to create a *forward-starting* **3-year** *swap*. As with the curves above, handle names of objects created by Deriscope formulas are displayed with red color.

The swap object is created in **cell H4** and is displayed with the handle name **&MySwap.1** because I decided to call it this way through my entry **Handle= MySwap**. The numerical suffix is **1** and will increment if the contents of the swap ever change.

The trade date – i.e. the date of the curve creation – is **May 22, 2019**.

This is a forward starting swap that starts accruing on **July 24, 2019** according to the entry **Start Date= 24/07/2019**.

This date is exactly two months after the settlement date of **May 24** (two business days after **May 22**).

The *swap* extends until **July 24, 2022** according to the entry **End Date= 24/07/2022**.

It therefore represents a *swap* with a **3-year** tenor that starts in **two months** from today.

The fixed leg pays **2%** on a notional of **1,000,000 USD** every **6 months** according to the entries **Fixed Rate(%)= 2**, **Notional= 1,000,000** and **Step= %6M**.

The prefix **%** in front of some text (eg in **%6M**) indicates the text is a handle name of a so-called trivial object, i.e. an object of which the contents are fully prescribed by the handle name.

The floating leg pays the **3-month USD Libor** on a notional of **1,000,000 USD** every **3 months** according to the entries **Index= %USDLibor|3M**, **Notional= 1,000,000** and **Step= %3M**.

The holder of this *swap* – i.e. the counterparty that maintains a long position on it – receives the *fixed rate* and pays Libor, according to the entry **Direction= Receiver**.

### Inspecting the Swap Cash Flows

Before I attempt to price this *swap*, I would like to check the *cash flows* agree with the *swap's* specification.

If I select the **cell H4**, the wizard immediately displays the contents of the **&MySwap.1** object:

The dimmed keys, such as **Gearing=**, are optional keys that assume the indicated default values – for example **Gearing=** **1** - if they are not explicitly entered in the spreadsheet.

If I click on the lens sign to the right of the **_CashFlows=** key at the bottom, I see the following:

The **N/A** values mean that the corresponding quantities cannot be inferred from the given data. Such quantities are, for example, the *floating leg* amounts and the *forward fixings* of the *floating index* (**USD 3M Libor**), which cannot be estimated on the absence of a *yield curve*.

I can easily paste all these data in my spreadsheet in the form of the output of a special array formula by clicking on the **Go button**, as indicated below:

This is the array formula and its output:

The original **N/A** values appear now as empty cells.

###
Calculating the swap price

I can use the wizard to generate the formula that calculates the price of my *interest rate swap*.

As explained in my previous post, I must supply two input curves and also set up my *Model* object so that there is no ambiguity as to which curve is used for *discounting* and *forecasting*.

Below are the two formulas, one for the creation of the *Model* object and one for the calculation of the *swap* price:

The price comes out as **-5,588.91 USD**.

Its negative sign indicates that the swap's fixed rate of **2%** is less than the fair rate implied by the given curves, since this is a **Receiver** swap.

### Inspecting the Full Set of Cash Flows including Curve-Implied Data

Deriscope provides a very easy way to extract useful information relating to this pricing outcome.

By selecting the **cell H4** containing the price, the wizard displays the following message:

If I click on the blue word **here** inside the phrase **To display extra pricing data click here**, I see the following useful data produced during the pricing of the swap:

For example, my earlier guess that the fair rate ought to be higher than the swap's fixed rate of **2%** is confirmed by the shown entry **Fair Rate= 0.02194**.

The last entry **Proc Time= 0.048** means that the *Processing Time* for this price calculation has been 0.048 seconds.

The entry **CashFlows= $Set#12** is of particular interest as it allows me to inspect the full set of cash flows, including data linked to my *floating index* as inferred by the supplied curves.

If I click on the lens sign, I see the following:

Unfortunately, I cannot use the **Go button** to transfer these data as an array formula output to the spreadsheet because they have been created on the fly by the wizard without being linked to any specific object.

What I can do though, is running the **Price** function with the **Output** value set to **CashFlows**.

In fact, if I select the **cell I10** containing the value **Price** associated with the key **Output=**, I see a dropdown with several choices, one of which is the **CashFlows**.

This choice would cause the pricing formula to return an object in **cell H4** that would contain the cash flows.

I could then use the **Go button** in the usual fashion to transfer that object's contents over a spreadsheet range.

Apart from the **CashFlows**, there are also other choices that correspond to the extra pricing data seen previously in the wizard, such as **Fair Rate**, **Fair Spread** etc.

### Flat DV01

The Deriscope **Price** function by default returns the NPV of the referenced *tradable*.

But it can do much more than that.

By specifying certain arguments, it can produce various other quantities of interest. One of them is the so called *Flat DV01*, which is defined as the change in price caused by a parallel shift of the market rates by one basis point.

In fact, due to the involvement of two distinct curves, there will exist two distinct *Flat DV01* numbers, one linked to a parallel shift of the *OIS rates* used in the *discounting *curve and one due to a parallel shift of the – primarily – *Libor swap rates* used in the *forecasting* curve.

I will use the wizard to create the formula with the correct input arguments that is capable of calculating a *Flat DV01*. The next video shows the details:

Below is what the wizard has pasted in my spreadsheet:

The risk output is inside the object **&VanIRSVal_C4:4.1** created in **cell C4** by the formula *=ds(C5:D10)*.

The wizard created a default *Yield Curve* object **&USDCrv_C12:4.1** in **cell C12** with a flat rate of **4%** because it was unaware of my interest in the existing two curves.

This is only a minor nuisance as I can easily substitute this default curve with my actual curves.

The previous Price formula I used for the NPV calculation had the form:

If I remove the two optional inputs **Reference=** and **Output=** that are not needed since they are associated with values that equal the default values, the layout is simplified to:

Let me now compare the layout of this formula against the one used for the *DV01* calculation by placing them side-by-side:

The DV01 formula on the right has **Add Risk= TRUE**, which instructs the **Price** function to also calculate the risk along with the NPV.

The difference with regard to the **Markets=** input has been already discussed.

Alone the **Add Risk= TRUE** would not have sufficed for the risk generation because Deriscope would need to know the specific market element relative to which the risk is required.

The latter is supplied with the input pair **Risk Ref= &USDCrv_C12:4.1**, which tells Deriscope to calculate the risk with respect to the *Yield Curve* object **&USDCrv_C12:4.1**.

The next pair **Risk Models= &YldCrvMdl_C18:4.1** provides the technical specifications of how the risk should be calculated. More on this in a moment.

It should be noted that the keys **Risk Ref= **and** Risk Models=** accept as input an array of objects, which allows the risk valuation with respect to multiple market data.

Below, I will make use of this possibility by calculating the *Flat DV01* against both the *discounting* and *forecasting* curve.

A very important omission from the *DV01* formula on the right is the pair **Models= &Model.1**. This entry will be absolutely necessary when the actual curves are used, as it tells Deriscope the exact role (*discounting* vs *forecasting*) of the two input curves.

Before presenting the final form of the *DV01* formula, here is the content of the *Model* object **&YldCrvMdl_C18:4.1**.

This tells me that the currently used risk model object instructs Deriscope to use a rate shift of **0.0001**, i.e. **one basis point**.

Also, the *DV01* is defined by the pair **Delta Def= NPV Change**. The description of this setting is displayed when I select the **NPV Change** cell:

Below is my final formula that calculates the *Flat DV01* with respect to both the *discounting* **(&USD-OIS.1**) and *forecasting* (**USD-3M.1**) curves:

The output in **cell C4** is the object **&FlatDv01.1**, conveniently named so through the entry **Handle= FlatDV01**.

It is an object of type *Valuation*, as inferred from the pair **TYPE= Valuation** in the object's contents displayed by the wizard on the right.

This object contains the *swap's* NPV through the pair **Value= -5,588.91**.

But it also contains the pair **Risk= $Variant#1**.

The latter is a special object with so called *System* *access rights*, as inferred by its **$** prefix, which simply means its contents and lifetime are controlled directly by Deriscope and not by the user. This is the object that contains the calculated *Flat DV01* values.

If I click on the lens sign of that object, I see its contents:

The two keys on the left column **Yield Curve Risk=** and **Yield Curve1 Risk=** correspond to the *Flat DV01* with respect to the **first** and **second curve** respectively.

Their associated values are the numbers **1.205** and **-288.33**.

By clicking on the **Go button**, I can transfer these two numbers to my spreadsheet as the output of a single array formula, with the following final spreadsheet result:

The interpretation of the first number **1.20** is as follows:

If I raise all market rates involved in the construction of the *discounting* curve (*OIS rates*) by **one bp**, but keep the market *Libor swap rates* fixed, the *swap* NPV will increase by **1.20 USD**. Note that this does not mean the *forecasting* curve stays fixed, since the latter is recreated based on the shifted *discounting* curve.

The interpretation of the second number **-288.33** is as follows:

If I raise all market rates involved in the construction of the *forecasting* curve (*Futures *and* Libor swap rates*) by **one bp**, but keep the *OIS rates* fixed, the *swap* NPV will increase by **-288.33 USD**.

The accuracy of these results can be easily verified by manually changing the market rates as described above and observing the corresponding impact on the *swap* price.

### Maturity-Dependent DV01 Array

While *Flat DV01* is theoretically interesting, traders are mostly interested in a *maturity-dependent DV01 *array, of which each element corresponds to one of the market rates used in the curve and represents the change in the swap price attributed to a one basis point bump in that market rate, while all other market rates stay fixed.

Each maturity used in the yield curve construction is also referred as "bucket" and the *maturity-dependent DV01* is also referred as "*By Bucket DV01*".

When the trader knows the *DV01* for a specific bucket, eg the **3-year** maturity, he also knows the amount of the corresponding market *swap* to be held in order to *delta-neutralize* his swap position from any changes in the respective market rate.

So far, my **Price** function has returned the *Flat DV01* only because the supplied object **&FlatMdl.1** of type **Model[Yield Curve]** – as shown in the above image - specifies so in an implicit way through the default setting of the non-supplied pair **Delta Mode= Flat**.

This fact can be seen in that image above, where the wizard-displayed contents of the *Model* object **&YldCrvMdl_C18:4.1** are shown to include the pair **Delta Mode= Flat**.

In effect, all I need to do is supply as explicit input the key **Delta Mode=** together with the appropriate text value that instructs Deriscope to produce the *By Bucket DV01*.

But how do I know what that text value should be?

I can handle this problem by two alternative methods:

__Method 1:____ Manual key-value insertion__

I start by adding one additional row at the bottom of the range dedicated to the creation of the *Model[Yield Curve]* object, typing in the known key **Delta** **Mode=** and editing the formula in **cell C17** so that the new enlarged range is referenced, as shown below:

After hitting **RETURN**, the following object is created in **cell C17**:

The object **&ByBucketMdl.1** has been successfully created, even with an empty **cell D21**, because the key **Delta Mode=** is optional and therefore assumes its default **Flat** value if not explicitly specified.

Here comes the trick!

I select the **cell C21** containing the key **Delta Mode=** in order to display its description in the wizard:

Then I click on the blue text **Model[Yield Curve]::Delta Mode** in order to display the description of the referred list:

I can now read that the **Delta Mode** list consists of the two values **By Bucket** and **Flat**.

Therefore, I now know that the appropriate text is **By Bucket** and I may also read the detailed description of that choice.

Below is the completed formula and its object output after I have manually entered the text **By Bucket** in **cell D21**:

The numerical suffix of the produced handle name **&ByBucketMdl.2** has now increased to **2** because the contents of the referenced object have changed through the new setting **Delta Mode= By Bucket**.

__Method 2:____ Wizard-assisted formula generation__

The following video shows how I use the wizard in order to generate and paste in the spreadsheet the correct formula with the desired **Delta Mode** setting:

The final *By Bucket DV01* formula and its output is shown below:

The pair **Value= -5,588.91** is same as before and represents the *swap's* NPV.

The interesting part is the pair **Risk= $Variant#1** which now contains the *maturity-dependent DV01*.

If I click on the lens sign, I see the contents of the **$Variant#1** object:

Compare this picture with the one below that was acquired at the same step in the *Flat DV01* case:

The two keys on the left column of the *By Bucket DV01* are still the same, but their corresponding values are not simple numbers anymore.

They are instead the objects **$_Yield Curve Risk_kv** and **$_Yield Curve1 Risk_kv** respectively.

The first object **$_Yield Curve Risk_kv** contains the *By Bucket DV01* array with respect to the **first** (*discounting*) **curve**, while the second object **$_Yield Curve1 Risk_kv** contains the *By Bucket DV01* with respect to the **second** (*forecasting*) **curve**.

Let's look inside the first of these two objects by clicking on its lens sign:

Doing the same on the second object yields:

I observe that both objects contain a number equaling the corresponding *Flat DV01* (referred here as **Flat Delta**=) plus an object labelled by the key **By Bucket Delta=**.

Clicking on the lens sign of the first **By Bucket Delta** object, I get:

The above picture indicates that the *By Bucket DV01* with respect to the *discounting* curve is further broken down into **two sets**:

One set contained in the object **$Set#1** that attributes the risk on shifts of the *deposit rates* and another set contained in the object **$Set#2** that attributes the risk on shifts of the *OIS rates*.

Clicking on the lens sign of the second **By Bucket Delta** object, I get:

The above picture indicates that the *By Bucket DV01* with respect to the *forecasting* curve is further broken down into **three sets**:

One set contained in the object **$Set#3** that attributes the risk on shifts of the *deposit rates*, a second set contained in the object **$Set#4** that attributes the risk on shifts of the *Futures prices* and a final set contained in the object **$Set#5** that attributes the risk on shifts of the *swap rates*.

I can see the actual numbers by clicking on any of these objects.

I can, for example, see my *OIS sensitivities* by clicking on the **$Set#2** object:

Not bad at all!

My swap was a **2-month** forward starting swap with a tenor of **3 years**.

Quite expectedly then, all deltas with respect to maturities of **5 years** and beyond appear vanished.

The **4-year** delta of **-0.09735** is non-zero because the swap starts in **2 months** from today and therefore its maturity lies between the **3Y** and **4Y** time pegs.

It is nonetheless much smaller than the **3-year** delta of **0.5928**.

I can next see my *Libor swap rate* *sensitivities* by clicking on the **$Set#5** object:

Similar comments apply here.

We observe though that the *Libor swap deltas* are much higher than the *OIS deltas*, just like in the case of the **Flat DV01s**.

The obvious explanation is that the *Libor swap rates* determine the projected amounts of the *index-linked* payments of the *floating* leg, which means their **one bp** shift effect on the *present value *of the *floating* leg is not counterbalanced by an opposite direction effect coming from the *fixed* leg.

On the contrary, the *OIS rates* affect the *discount factors* and therefore the *present values* of both legs. So, the induced *present value* changes on the two legs cancel each other, at least to a large degree.

By clicking on the **Go button**, I can transfer these two arrays to my spreadsheet in the form of two array formulas, with the following final spreadsheet result:

In the above image, the **cell I4** is selected.

This cell is part of the output of the array formula that returns the *OIS sensitivities* by referencing the risk output object **&ByBucketDV01.1** in **cell C4**.

The formula is shown in the formula bar and also repeated below:

*=ds("ObjectTools::Show","Reference=",$C$4,"Key=","Risk","Yield Curve Risk","By Bucket Delta","OIS Delta","ExpandFinal=",TRUE)*

Its syntax is actually simple, but there is no point to try to understand it since it can be generated by the wizard if and when needed.

A similar array formula applies in the range where the *Libor Swap sensitivities* are displayed.

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