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.
Recommended for Deriscope starters: The Overview and Quick Guide pages.
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
