# Excel Builder and Cash Flow Viewer for Non-Standard Interest Rates Swaps

Building, pricing and analyzing even *non-standard interest rate swaps* in Excel becomes a simple exercise when the Deriscope interface to the open source QuantLib analytics library is employed.

We have already encountered a simple *interest rate swap* contract in the Yield Curve Building in Excel using Swap Rates article, where vanilla swaps were used to build a yield curve.

Now I will extend my scope to more complicated *fixed-to-floating interest rate swaps* whereby an already created yield curve is assumed given.

This is indeed the setup facing real market professionals:

How to price non-standard, over-the-counter *interest rate swaps* when a yield curve has already been built out of liquid vanilla swap market rates.

Swap types

The following diagram visualizes the various types of *fixed-to-floating interest rate swaps* that can be priced by QuantLib and created in Excel through Deriscope by utilizing the special Deriscope type *Fixed Float Swap*.

In particular, the following attributes can be customized:

*Fixed leg time schedule *

*Floating leg time schedule*

*Non-flat fixed leg accrual rates*

*Non-flat notional reference amounts on both legs*

*Intermediate and final notional payments*

Several more customizations are still possible by employing other more advanced Deriscope types that I plan to cover in subsequent articles.

###
Creating an interest rate swap object in Excel

The Deriscope wizard can generate the spreadsheet formula that creates a full-fledged *fixed-to-floating interest rate swap* object in Excel*,* as the video below demonstrates.

A great wizard feature is its capacity of displaying all implied cash flow data for any given input selection before the final formulas are generated and pasted in the spreadsheet!

This is how the pasted formulas look like:

###
Understanding the main formula

As you see, cell **A1** contains the formula *=ds(A2:B12;A14:C16)*, which takes two input arguments and returns the text *&FxdFltIRS_A1:1.1*

The prefix *&* indicates that *&SimpleRt_A1:1.1 *is the handle name of some object. In fact it points to an object of type *Fixed Float IRS*.

The input range *A2:B12* contains the input for the keys described in the table below:

Key | Value | Description |

Index |
%EURLibor|6M | The interest rate of
which the fixing F at
the start of the _{i}i
floating leg period is used in the formula ^{th}N_{i}΄(gF_{i}+s)Δt_{i}΄ that produces the amount to be paid at the end
of that period.
N is the floating leg
notional at the start of the _{i}΄i
period, ^{th}g is the gearing, s is the spread and Δt is the length of the respective period._{i}΄ |

Gearing | 1 | The constant g in the formula N_{i}΄(gF_{i}+s)Δt_{i}΄ |

Spread | 0 | The constant s in the formula N_{i}΄(gF_{i}+s)Δt_{i}΄ |

Direction | Receiver | Receiver for receiving fixed and paying floating. Otherwise Payer |

Fixed Schedule | &Schedule_E1:1.1 | Sets the time schedule
of the fixed payments. It also determines the length Δt for each
fixed leg interval used in the
formula _{i}N_{i}R_{i}Δt_{i}N is the fixed leg notional at the start of the _{i}i period, ^{th}R is the applicable fixed rate and
_{i}Δt is the length of the respective period._{i} |

Float Schedule | &Schedule_H1:1.1 | Sets the time
schedule of the floating payments. It also determines the length Δt for each floating leg period used
in the formula _{i}΄N_{i}΄(gF_{i}+s)Δt_{i}΄ |

Payment Date Bump | %MF | Date bump convention applying to all payments on both legs |

Running Capital Exchange | FALSE | When TRUE, intermediate capital exchanges are allowed whenever the notional changes between successive accrual periods |

Final Capital Exchange | FALSE | When TRUE, terminal capital exchanges take place as well |

The second range *A14:C16 *contains the input for those keys that expect as value an array of data:

Fixed Rates |
0.03 , 0.04 | The rates R applying on the fixed leg
periods in sequence, with the last rate applying on all remaining periods_{i} |

Fixed Notionals |
1 , 1 | The reference
notionals Napplying on
the fixed leg periods in sequence, with the last notional applying on all
remaining periods_{i } |

Float Notionals | 1 , 1 | The reference
notionals N_{i}΄ applying on the floating
leg periods in sequence, with the last notional applying on all remaining periods |

I can always access detailed information on any *key-value pair *by selecting the respective cell as this video shows:

###
Browsing the contents of a created swap object

In my case, the object in cell **A1** is linked to input data located in adjacent areas so that I do not need the wizard to find out – for example – that the gearing value equals **1**.

But when I build more complex spreadsheets, handle names such as *&FxdFltIRS_A1:1.1* can be found at any cell far away from their original input data.

In such cases, the wizard becomes indispensable as it can give me an x-ray picture of the object's contents as soon as I select the cell containing the respective handle name.

The following picture shows what the wizard's *Browse Area* displays when cell **A1** is selected.

Looking inside the wizard, I cannot only verify that *gearing = 1* but I can also browse through deeper details by clicking on the respective lens symbols.

For example, if I click on the lens symbol of the cell next to *Float Cash Flows* I see the following:

As you see, only time information is included in this view. Information on future index fixings is not available because it is impossible to calculate forward fixings without a yield curve input.

###
Using the swap object

Deriscope supplies through QuantLib the following two main functions that can be called from a given *Fixed Float IRS* object:

** Cash Flows**: Returns the implied cash flows for a given yield curve input.

** Price**: Returns primarily the swap's price for a given yield curve input.

Note that the *Price* function may also return several other values beyond the price of the swap by setting the value for the *Output* key accordingly. More on that below.

The Cash Flows function

The following video shows how I use the wizard to paste the *Cash Flows* function in the spreadsheet:

Below is the spreadsheet image with the pasted functions after I have shifted a few blocks in order to make them all visible:

The wizard pasted the main formula *=ds(A20:B23)* in cell **A19,** which then produced the object of type *Variant* named *&Variant_A19:2.1.*

The produced object contains the various cash flows that are visible inside the wizard because cell **A19** is selected.

The input range *A20:B23* includes two objects:

**1.** The *Model* object named &FxdFltIRSMdl_A25:2.1 created in cell **A25**

**2.** The *Market* object named &Mkt_A32:2.1 created in cell **A32**

The *Model* object includes a key called *Forecasting Curve Issuer* in cell **A30**, the value of which is left empty. Because of this, the cash flows are projected by relying on the yield curve supplied in the market data.

The *Market* object contains two objects:

**1.** The *Yield Curve *object named &YldCrv_E19:2.1 created in cell **E19**

**2. **The *Historical Values *object named &HistVals_E29:2.1 created in cell **E29**

Deriscope has created the *Historical Values *object because it recognized that a historical Libor fixing as of **09 Mar 2018** is required, which cannot be implied by the given yield curve that starts two days later on **11 Mar 2018**.

It therefore creates the necessary formula with a dummy fixing of **4%**, which I must replace manually with the actual historical Libor fixing of that date.

Displaying in the spreadsheet the Cash Flows data

We saw that the *Cash Flows* function returns the object *&Variant_A19:2.1* in cell **A19** and that I can peruse its contents through the wizard.

It is also possible to generate a spreadsheet formula that returns all or a portion of these data directly in the spreadsheet.

The following video shows how I use the wizard to output the cash flows of the floating leg on a separate sheet.

The Price function

The following video shows how I use the wizard to paste the *Price* function in the spreadsheet:

Below is the spreadsheet image with the pasted function in cell **H19** that returns a swap price of **0,002498617**:

###
Additional output data

During the price calculation, QuantLib also computes certain values that can be of interest.

When cell **H19** is selected, the wizard displays the following in its *Info Area*:

If I click on the *here* hyperlink of the top sentence "*To display extra pricing data click here"*, I see the following:

As you see, beyond the cash flows per each leg I can also see the *NPV* and *BPS* as well.

I may now paste any of these data in the spreadsheet as explained here or alternatively use the *Output* key as this video demonstrates:

Below is the spreadsheet image with the pasted function in cell **H24** that returns a *Fixed Leg NPV* of **0,169377129**:

###
Advanced Topic: Following the pricing algorithm step-by-step

What I am now going to show you, is perhaps superfluous in the context of the very simple "derivative" that we currently consider, but can be indeed useful in more complex situations.

Deriscope makes the actual steps taken throughout the price calculation transparent to the end user!

It also makes transparent the various underlying QuantLib structures, methods and intermediate results that are accessed and produced during the run of the calculation.

I start by selecting cell **H19** containing the swap price**,** whereupon the wizard displays the sentence "*To display the pricing steps click here"*.

If I click on *here* I see the following:

As you see, the algorithm that has been executed in order to produce the price of **0,002498617** is described in terms of TASKS and SUBTASKS.

All blue color elements act as hyperlinks that either supply object details or take you to a next screen displaying a deeper, more refined level of information.

The most useful hyperlink is the *here* at the top sentence "*Click* *here* *to jump straight to the FixedFloat IRS pricing algorithm*."

This acts as a shortcut that skips all intermediate standard workflow and takes me straight to the real stuff, namely the QuantLib pricing routine.

This is the screen I get after I click on *here*:

In the above picture, I have intentionally highlighted the section telling me that the actually used QuantLib instrument is of type *NonstandardSwap*, the pricing engine is *DiscountingSwapEngine* and the function is called *NPV*.

I can go one step further and click on the hyperlink [Variant] to see the contents of the QuantLib *NonstandardSwap* object inside the *Browse Area *as shown below:

Here I can see how the *NonstandardSwap* object looks like at the moment when it is actually priced!

For example, I notice that the row corresponding to the *fixedRate* key (referred as *fixedRate* and not *Fixed Rate* because this is how it is called in QuantLib) contains **5** rates even though I have supplied only **2** rates as input in my spreadsheet.

What happens is that QuantLib carries out some internal processing whereby the original last input rate is replicated enough times in order to cover all fixed leg intervals that are not already covered by the previous input rates.

It is wonderful that I can see this degree of detail, especially if I have trouble reconciling my QuantLib pricing output with that obtained by other sources such as Bloomberg!

I can peek even deeper within the displayed data.

For example, if I click on **$Variant#8** which is the second cash flow of the second leg (the floating leg), I see the following details about the QuantLib structure that holds this particular floating cash flow:

You may download the spreadsheet produced with the above steps here

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