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 is created in some of the examples. 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 :(
This is a web application developed with the API. Take a look to the help section to see how it works.
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 Cookbooks 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.
Let’s see first 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). All the steps are in End of year effect spreadhseet.
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.
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.