Some examples will be listed here to show the results of the API. Most of the examples will be google sheets
calling the API functions.
We will use the quantra google sheets functions for some examples, to follow them you can create a copy
of the spreadsheet and install the quantra google sheets functions to see the results and
do any modification you want. Check
this to see how to start using quantra sheets functions,
but basically you can either install the quantra add on or do it from the source code.
I've found some issues with google sheets when large amounts of cells are needed, which is the case when receiving all the points of a
curve that goes up to 30 years like the one we will create here. Chopping the curve out to 5 or 10 years would work as it does not seem to affect
to the calculations even working with non-local interpolations.
If you want to keep working with the whole curve some errors raise randomly as it seems it reaches some maximum quotas of google,
to solve the problem is needed to force the spreadsheet to call again the service from quantra, so modifying one value and setting it back
to the value you want does the trick. Try it several times if it does not work at first glance :(
Below a list of Google sheets using the API:
Turn of year effect
It is known that rates suffer jumps due to some reasons like high liquidity demand on certain dates and this
affecting to the yield curve shape. It typically happens the end of the year although it could also happen at
some other dates like end of quarter. Some publications and information about it:
Most of the examples from Ametrano publications are calculated with Quantlib and reproduced in
QuantLib Python Cookbook from Luigi Ballabio (which I strongly recommend),
so what we will do here is translate the Python examples to quantra API and see we obtain same results.
To solve the problem we need to apply the jump to just the last day of the year leaving the rest the curve
as it should be without it and to do so we need to know the size of it.
And we need to calculate the the size with the existing qutoed instruments.
Lets first see how a jump affects to a Libor 3M curve (we have taken the curve from
Everything You Always Wanted to Know About Multiple Interest Rate Curve Bootstrapping But Were Afraid To Ask).
Let's plot the overnight flat forward curve:
We have plotted the same curve with and without the jump on last day of the year and what can be seen is that
the curve with the jump (red line) has decreased with the excepion of December 31st which contains the jump.
This happens because the discrete forward rate is the average of the instantaneous forward rates,
and to keep the value for this section with the jump the rest needs to decrease.
Lets plot now the 3M forward rate to see ho it is affected:
Here we see that since 3 months before the end of the year till the end of the year there is a jump and
the rest of the curve remains smooth.
That is because any forward section going through the end of the year will be affected by the jump,
and the first one will be 3 months before end of year.
If we would plot a 6 months forward curve we would see a smaller jump starting 6 months before the end of
the year, the reason it would be smaller is because the jump has less weight in the average for 6 months.
Figure 24 of Everything You Always Wanted to Know About Multiple Interest Rate Curve Bootstrapping But Were Afraid To Ask plots ON, 1M and 3M curves to show this.
Turn of year jump size
We will try now to calculate the jump for end of 2013 for the Eonia curve from section 5.1 of
Everything You Always Wanted to Know About Multiple Interest Rate Curve Bootstrapping But Were Afraid To Ask paper from
F. M. Ametrano and M. Bianchetti. We will do it following the steps of the QuantLib Python Cookbook from Luigi Ballabio (which I strongly recommend).
As is stated in the paper the results are obtained with Quantlib so they should be close to the ones we will get here.
Any justification of the decisions taken can be found in the paper so it will not be explained here.
First step is creating a forward curve with the rates from the figure 25 of the paper.
These are three deposits that go from overnight to spot night, four OIS swaps, five OIS forwards and eighteen other OIS swaps till 30 years.
The deposit rates are the yellow cells that go from B5 to H7. OIS swaps are put together even they are separated in time as they have to be passed
together to the function. They are the B12:F33 ones, in blue up to one month and the rest of them in purple. OIS forwards are at B38:E42.
As you will see all of them are passed to functions like a spreadsheet matrix.
We will first create a cubic spline monotonic interpolated curve bootstrapped over log discount rates (why is done like this in the paper and further
information can be found at Methods for Constructing a Yield Curve Hagan and West).
We will call GENERATEFWDCURVE google sheets function with aforementioned rates and settings from J4:N8 cells. The function will be called from
Data tab of the spreadsheet and plotted at J10:N27 of first tab.
The resulting curve is shown below.
As can be seen a jump shows up at the end of 2012 and we will need to remove it. Let's follow the steps from Luigi Ballabio book.
We first change the settings of the curve to a backward flat interpolation over forward rates to see it more clearly and
determine the size of the jump.
Lets call the GENERATEFWDCURVE once more with P4:Q8 settings and plot the curve (shown at P10:T27).
Lets get now the node values from the interpolated curve with the CURVENODES function. Values can be found
We can clearly see that the seventh node (sixth position of the array and highlighted in red) is above the ones surrounding it.
To supress it we will calculate the average of these two and reassign the value to the seventh node (cells Z4:Z34):
Lets create the curve again with INTERPOLATEDFWDCURVE function with resulting nodes at cells Z4:Z34 and settings from AD5:AE8 and plot the curve again
along with the previous one. In red the original one and in blue the one with the modified rate.
Once we have the curve we need to calculate the size of the jump and assign it to the last day of the year
(like is epxplaned in the paper and further information about it in
From QuantLib Python Cookbook
we see that the forward rate over the two weeks around the end of the year will give us a value close to the
one from the paper.
All calculations are done at AJ5:AO7, and the value of the rate for the last day is at AK10.
This is 0,001008631315, which is close to the 0.101% of the paper. Lets get and add to the curve the corresponding discount value
to the last day of the year.
We just need to call the GENERATEFWDCURVE function again adding
the date and value of the jump at 8th parameter with same settings we had.
We can plot now the log discounting curves with and without the jump to see the difference. The red line shows
the original curve and blue is the one with the jump. We can clearly see that the curve is smoother with a jump located at the last day of the year.
Vanilla swap pricing
Lets price a vanilla interest rate swap with quantra and Google Sheets.
We will price a 3M libor swap with a multiple curve framework and data used for discount and forecasting curve
will be from
Everything You Always Wanted to Know About Multiple Interest Rate Curve Bootstrapping But Were Afraid To Ask
on how the the data is generated (like the synthetic deposits or the jumps) is explained in the paper so it will
not be covered in this section.
We will first create the discounting curve with the instruments from Figure 25 of the paper. That is:
- 3 deposits
- 4 OIS swaps
- 5 OIS forwards
- 18 OIS swaps
We need to configure the curve bootstrapping, found at L7:M9, and also add the two jumps for the curve (D47:E48) explained in the paper
To create the forecasting curve we will use the 3M instruments from figure 29:
- 4 deposits
- 8 3M futures
- 17 swaps
Note that the swaps will be discounted with the previous discount curve, that is set with the last parameter
of each swap set to TRUE
We need now to set the swap parameters.
- Generic data, like notional or payer/receiver swap
- Fixed leg parametrization: Rate and flows schedule
- Floating leg parametrization: Spread, floating flows schedule and underlying index
With the curves and the swap created we can call the PRICEVANILLASWAP (AY4 cell) google sheets function and get the results:
- Fair rate
- Fair spread
- Floating flows
- and fixed flows