Perfect Bloomberg Price Match of an Interest Rate Swap in Excel by using Dual Bootstrapping

Up until the financial crisis of 2008, the price calculation of an interest rate swap involved only the so-called Libor curve. The latter was essentially the discount factors (or equivalently zero rates or forward rates) implied by market-traded instruments, such as deposits, futures, forwards and swaps. The Libor curve was used to derive everything required in carrying out the swap pricing: a) Forecasted Libor rates (i.e. forward rates) needed in the calculation of the regular floating leg payments and b) discount factors needed in the calculation of the present value of all payments.

After 2008, the Libor curve methodology started producing numbers that were in disarray with the actual market prices. The reason was that the following major assumption lying at the kernel of the Libor curve algorithm ceased to be valid: All cash flows – both in the swap being priced and the market instruments – are risk-free. Indeed after 2008, financial institutions stopped trusting each other and as a result started demanding extra compensation for their assumed counterparty risk. They even went further, requiring collateral posting as a means of securing the promised honoring of their counterparties' obligations in the event the latter went bust.

The current industry swap valuation practice is based on dual bootstrapping and involves three steps:

• 1.Build a risk-free yield curve out of instruments deemed to be default-free, such as Fed Funds swaps in the US or Eonia swaps in Europe. The construction of this yield curve follows the conventional methodology that was used for all curves until 2008.
• 2.Build a separate forecasting yield curve out of market instruments actively traded between banks, eg deposits, futures, forwards and swaps. The construction of this yield curve needs to take into account the facts that a) these instruments are collateralized, which effectively guarantees the cash flows between the counterparties and b) the floating cash flows are linked on a Libor index, the future level of which is governed by non-collateralized inter-bank obligations and therefore is risky in nature. The correct methodology is to treat all discounting rates as exogenously given from the curve already constructed in step 1 and solve for so called "forecasting rates" that imply the given market prices. Note the output forecasting rates are purely theoretical and by no means represent the risky Libor rates. They serve only one technical purpose: To forecast the floating rates that may appear in a collateralized interest rate instrument, such as a collateralized interest rate swap. They cannot be used, for example, in forecasting the floating rates in a non-collateralized swap!
• 3.Calculate the swap's present value by a) computing the floating payments by forecasting the future Libor fixings using the forecasting yield curve built in step 2 and b) discounting all payments using the risk-free yield curve built in step 1.

In the following I will examine in detail the pricing of an actual swap undertaken by one of my clients, with its notional set to 10,000,000 EUR for simplicity. Its actual notional was somewhat higher.

Bloomberg priced this swap on Nov 30, 2018 at 388,147 EUR.

Instead the old fashioned, single curve approach delivers a price of 382,849 EUR, which differs from Bloomberg by 5,298 EUR, or 5.3 basis points.

Perhaps not a huge difference, but the more correct dual curve calculation delivers a price of 388,668 EUR, which differs from Bloomberg merely by 520 EUR, or 0.5 basis points.

On practical terms, I would risk calling this second result a "perfect match"! 

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

Swap Definition and Construction of the Respective Object

Our swap is a long dated fixed vs Euribor interest rate swap that runs until Dec 27, 2067. The exact details are shown below, along with its Bloomberg valuation of 388,147.49 EUR as of Nov 30, 2018:  

Since I want to calculate its price using Deriscope, I must first create a Deriscope object of type Vanilla IRS using the wizard as described in detail in my article about interest rate swaps. Below you see that object &VanIRS_K2:3.1 created in cell K2 through the formula =ds(K3:L12), which relies on the two previously created objects &Sched_E2:3.1 and &Sched_E2:3.1, both of type Schedule, created in cells E2 and H2 respectively. 

Inspecting the Swap Cash Flows 

You have obviously noticed that in the construction of the Vanilla IRS object &VanIRS_K2:3.1 several conventions are used that are not visible in the original swap data.

For example, the Rule= Backward entry in row 15 leads to a certain cash flow date generation that may or may not be in accordance with the swap that this object pertains to represent.

Before going any further, it is important to verify that the cash flows implied by &VanIRS_K2:3.1 fully agree with those in Bloomberg.

Below you see the formula pasted by the wizard in cell N2. Its syntax (visible inside the formula bar) is quite reasonable. It "shows" the value associated with the key "_CashFlows" of the referenced object in cell $K$2. The returned object &Set_N2:3.2 represents a generic collection of diverse data that become visible inside the wizard as soon as the cell N2 is selected.

While this method of viewing the cash flows inside the wizard is perhaps "fancy" and even useful when one is only interested in an overview or the identification of a single flow, is not conducive to performing a thorough comparison of all cash flows against those in Bloomberg. The latter can only be done efficiently if both sets of data are pasted side by side in the spreadsheet.

I can obviously extract the Bloomberg cash flows and paste them as text in my spreadsheet.

I can also do the same with my wizard data. If I right-click with my mouse while over the wizard's data I see the following options that allow me to copy all or part of the data in the clipboard.

It is even possible to ask the wizard to paste a special array formula that returns the cash flows dynamically linked to the source &VanIRS_K2:3.1 object, as the following video shows:

Below you see a portion of the array formula pasted by the wizard over the range B2:P152:  

Swap Pricing Using a Single Libor Curve 

My first attempt will be to price the swap using the old-fashioned single curve approach.

I will be using the following curve data from Bloomberg:

As I did with the swap data, I will need a Deriscope object of type YieldCurve that is built out of these market rates, which involve one deposit rate, 12 forward rates and 19 swap rates.

You may consult my post on YieldCurve creation out of deposits, futures and swaps on the details about how one may use the wizard to paste the required formulas in the spreadsheet. Afterwards I must obviously edit the pasted formulas so that they contain the actual rates and conventions listed above. The end result is shown below, where the YieldCurve object &EuriborCrv.1 is created by the formula =ds(V3:W12,V14:V17) in cell V2:

 If I had attempted to price the swap using this curve, Deriscope would have returned an error due to missing historical Euribor fixings.

Therefore, I need to setup an object of type Market Set that bundles together the &EuriborCrv.1 object with an object of type Historical Values that holds the required past fixings. Conveniently this bundling is done by the wizard when I ask it to generate the pricing formulas for the swap.

The image below shows the object &EuriborMkt.1 created by the formula =ds(E3:F5,E7:E9) in cell E2:

The final pricing result is shown below, with the swap price of 0.0382849 calculated by the formula =ds($B3:$B8,C3:C8) in cell C9.

As you see, the input to this formula includes the previously created objects &VanIRS_K2:3.1 and &EuriborMkt.1.

This price corresponds to a notional of 1. Therefore, the swap NPV equals the product of 0.0382849 with the given notional of 10,000,000, which is the number 382,849 in cell C10.

When compared to the Bloomberg price of 388,147, we observe a discrepancy of -5,298 EUR or -5.3 basis points resulting by dividing 5,298 with the swap notional of 10,000,000.

Swap Pricing Using a Discounting Curve and a Dually Bootstrapped Forecasting Curve

My second attempt will be to price the swap using the currently established approach of using two curves: A riskless curve for discounting the swap cash flows and a dually bootstrapped curve for implying the forward rates that in effect "forecast" the future Euribor fixings. The word "forecast" is used in a loose sense, since the no-arbitrage theory, on which the swap pricing relies, involves no "forecasting" but only needs the knowledge of spot settled appropriately defined forward rates.

With regard to the riskless discounting curve, I will be using the following Eonia curve data from Bloomberg:

As I did with the Euribor curve, I will first need a Deriscope object of type YieldCurve that is built out of these market rates, which involve one deposit rate and 33 OIS (Overnight Index Swap) rates.

You may consult my post on YieldCurve creation out of OIS rates on the details about how one may use the wizard to paste the required formulas in the spreadsheet. Afterwards I must obviously edit the pasted formulas so that they contain the actual rates and conventions listed above. The end result is shown below, where the YieldCurve object &EoniaCrv.1 is created by the formula =ds(S3:T12,S14:S16) in cell S2: 

 Next, I will need to create a second Deriscope object of type YieldCurve that will represent the dually bootstrapped forecasting curve.

The following image shows the formula =ds(E3:F12,E14:E17) in cell E2 that creates the object &DualCrv.1.

As explained in the shown comments, dual bootstrapping is achieved by building the block of OIS rates using the same input OIS rates as I did earlier with the &EoniaCrv.1, but this time with the Discounting Curve entry filled with the &EoniaCrv.1 object.

I do all this very efficiently by re-utilizing the previously created YldCrvSwp_S2:5.1 object. I have simple cloned it using the Clone function and tweaked its Discounting Curve entry as shown.

The reason why I define the Issuer of my &DualCrv.1 as shown in cell F6 has to do with the need to identify this curve as the one to be used for forecasting purposes. As such, no special Issuer object is needed here. Any arbitrary Issuer definition would do the job of uniquely identifying this curve as my forecasting curve.

As I did before, I need to setup an object of type Market Set that bundles together this curve with the &EoniaCrv.1 and the past fixings.

The image below shows the object &DualMkt.1 created by the formula =ds(H3:I5,H7:H10) in cell H2:

The last component required in carrying out the correct pricing of the swap is an object of type Model that will make the pricing routine aware of my desire to use two different curves, one for discounting and one for forecasting.

Without this extra Model object, the pricing routine would proceed in the default fashion of applying the same curve for both discounting and forecasting.

The wizard creates this special object for me, as shown below. Its exact type is Model[FixedFloat IRS], which inherits from Model. 

Putting all pieces together, the final pricing result is shown below, with the swap price of 0.0388668 calculated by the formula =ds($B3:$B8,D3:D8) in cell D9.

As you see, the input to this formula includes the previously created objects &VanIRS_K2:3.1, &DualMkt.1 and &DualMdl.1.

This price corresponds to a notional of 1. Therefore, the swap NPV equals the product of 0.0388668 with the given notional of 10,000,000, which equals the number 388,668 in cell D10.

When compared to the Bloomberg price of 388,147, we observe a discrepancy of only 520 EUR or 0.5 basis points resulting by dividing 520 with the swap notional of 10,000,000.

This result represents a 10-fold improvement over the discrepancy of -5.3 basis points associated with the earlier single curve method! 

Inspecting All Cash Flows

The formula in cell D9 that returns the swap price can also return the projected cash flows implied by the model being used and the supplied yield curves.

In the current case, due to the supplied &DualMdl.1 object, the projected floating payments will be based on the dually bootstrapped forward rates.

In order to create an object that holds these cash flows, I need to enhance the pricing formula with the additional optional key-value pair Output= CashFlows, as shown below, where the object &Set_D17:4.1 is created in cell D17:

Using the exact same steps as before, I can use the wizard to create an array formula that returns all these cash flows in the spreadsheet for a more convenient analysis.

The top portion of the resulting array formula is shown below:

The last column displays handle names of objects that contain more details about the index involved in the respective coupon.

These details can be viewed within the wizard by selecting the cell with the handle name.

For example, when I select the fourth cell from the top, I can see the index details of the respective floating coupon, as below: 

Understanding the Price Difference Between the Two Methods

It is interesting to understand where the 5 basis points difference in the pricing result between the two methods comes from.

If I select the cell C9 containing the first method's price of 0.0382849, I see at the bottom of the wizard the sentence: To display extra pricing data click here.

When I click on the word here, I see the following:

If I then repeat the same steps after selecting the cell D9 containing the second method's price of 0.0388668, I see the following: 

These two tables show that the NPV of both legs increases in absolute value as one moves from method 1 to method 2. This is expected since the second method discounts all cash flows using the riskless Eonia curve. Both legs though, show an increase in their NPV by about 0.013 (remember the notional in this pricing output is 1), so that the NPV of the swap, being the algebraic sum of the two legs' NPVs, does not practically change.

The observed swap NPV change of about 5 bps comes from the difference in the projected Euribor forward rates.

Rather than comparing the projected cash flows implied by the two pricing methods, it is more instructing to plot the 6-month forward rates that are implied by the curves that are used for Euribor forecasting purposes in the two methods.

The first method uses the singly bootstrapped curve (also known as risky curve) &EuriborCrv.1, whereas the second method uses the dually bootstrapped curve &DualCrv.1.

So, I go back to my sheet where I first created the &EuriborCrv.1 curve and use the wizard to paste the appropriate formula that returns a series of implied 6-month forward rates in array form. Since I am not interested in exact dates, I use the Forward Rate At Time function, which treats the time as a number in annual units.

The following video shows how the wizard pastes a prototype array formula in the spreadsheet, which I can afterwards easily extend to cover the whole 50-year range, I am interested in. 

Below you see the top section of the final array formula: 

Next, I repeat the same process with the curve object &DualCrv.1 in sheet DualCrv and I get the following: 

Bringing both results together, I observe that the forward rates implied by the curve created with dual bootstrapping tend to be lower than the forward rates stemming from single bootstrapping.

The following chart shows the implied forward rates for maturities between 9 and 24 years:

You may wonder about the apparent unsmooth behavior of the forward rates. This is due to the entry Interp Method= Linear in the curve construction. It is perfectly possible to change Linear with Cubic, which would lead to smooth forward rates. I have kept the Linear entry only in order to match the Bloomberg results, since it seems that Bloomberg uses linear interpolation.

The following chart shows that the difference approaches one basis point in about 14 years, but it also assumes slight negative values at maturities beyond 30 years.

The latter chart explains where the 5 bp swap price difference comes from.

Since floating payments occur every 6 months, each forward Euribor fixing is multiplied by 0.5, which is the length of the 6-month accrual period in annual units. Therefore, a 1 bp difference in a projected forward rate will contribute only a 0.5 bp difference in the non-discounted swap npv. Since one year contains two floating payments, it turns out that the bp contribution over one year from the floating payments to the non-discounted swap npv would almost equal the difference of the implied forwards in bp observed over that year. In other words, we would need a period of 5 years with a sustained implied forward rate difference of 1 bp in order to get an undiscounted npv difference of 5 bp.

This observation is in line with the implied forward rate differences depicted in the above chart and justify the observed discounted npv difference of 5 bp. 

Working with Several Swap Products

I have so far focused on pricing a single swap.

If I need to calculate the prices of hundreds of swaps, I can easily create a table where a separate swap object is created in each row.

To that end, I can use the Deriscope Clone function to create each swap object in a single step by cloning some initial swap object and modifying a few of its elements, as described in detail here.

Other techniques involve the Revalue function as describe here.

I may also bundle together all swaps in one single object of type Portfolio and use a single pricing formula to calculate the price of that portfolio. More on working with portfolios can be found in my recent post on structured products.

In the above link – and also here – is also explained how Deriscope can solve for the fair value of any given input parameter that leads to a user-defined target pricing outcome.

Click on IntRateSwapDualCrv.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