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.
Recommended for Deriscope starters: The Overview and Quick Guide pages.
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 Fi at the start of the ith floating leg period is used in the formula Ni΄(gFi+s)Δti΄ that produces the amount to be paid at the end of that period. Ni΄ is the floating leg notional at the start of the ith period, g is the gearing, s is the spread and Δti΄ is the length of the respective period. |
| Gearing | 1 | The constant g in the formula Ni΄(gFi+s)Δti΄ |
| Spread | 0 | The constant s in the formula Ni΄(gFi+s)Δti΄ |
| 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 Δti for each fixed leg interval used in the formula NiRiΔti Ni is the fixed leg notional at the start of the ith period, Ri is the applicable fixed rate and Δti is the length of the respective period. |
| Float Schedule | &Schedule_H1:1.1 | Sets the time schedule of the floating payments. It also determines the length Δti΄ for each floating leg period used in the formula Ni΄(gFi+s)Δti΄ |
| 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 Ri applying on the fixed leg periods in sequence, with the last rate applying on all remaining periods |
| Fixed Notionals | 1 , 1 | The reference notionals Ni applying on the fixed leg periods in sequence, with the last notional applying on all remaining periods |
| Float Notionals | 1 , 1 | The reference notionals Ni΄ 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.
Below is the array function {=ds("ObjectTools::Show","Reference=",Swap!$H$30)} pasted by the wizard over the range A1:T16 that returns the cash flows.
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 third cell from the top, I can see the index details of the respective floating coupon, as below:
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
