Yield Curve Building in Excel using Forward Rates
With this article I want to show you how to create a yield curve in Excel using the open source QuantLib analytics library, when the input market data are forward rates.
My previous article focused on building a yield curve in Excel out of deposit rates in general and Libor rates in particular.
These rates cover the short range of the maturity spectrum, are readily available in the market, but are not the only ones. Forward rates are another candidate that also span short-dated maturities, albeit much less used than deposit rates. An exception is Futures prices that are very liquid and will be the focus of my next post.
Forward rate is the rate associated with a Forward contract.
The latter is an agreement between two counterparties A and B to exchange cash at two future times T1 and T2 as follows:
Counterparty A (the lender) agrees to pay an amount N (the principal) to counterparty B (the borrower) at some future time T1 (the forward's maturity).
On the other hand, counterparty B undertakes to pay back to A a slightly higher amount N + ΔN at a still later time T2 (the maturity of the forward's underlying).
The extra amount ΔN represents the interest that B must pay A for the benefit of having access to cash from T1 until T2.Similar to how I defined the deposit rate in my previous article with regard to spot deposit contracts, one may associate with a forward contract a rate that is called FORWARD RATE and defined as:
The important underlying assumption in this definition is that the contract is fair, which means that both A and B are satisfied with the contract's terms and require no additional compensation. This holds necessarily at the time when the contract is signed but generally fails to hold a second later, as market interest rates move!
Indeed if rates within the next second go up, the lender A will regret having lent money at the lower rate prevailing as of the previous second, but will be unable to cancel the contract, which will continue to exist albeit with a non-zero value, meaning that the lender could sell the contract to a new lender C, but would need to compensate C with the amount –V, where V is the current negative value of the contract (negative from the perspective of the lender A, but positive from the perspective of the borrower B).
Creating the Yield Curve
After this little digression on forward contracts and forward rates, let's assume that I know what the forward rates are for a set of maturities.
As usually, I will ask Deriscope to generate the formula for me.
After I select the Yield Curve type in Type Selector I must check the Use FRAs flag inside the input parameters screen and finally hit the Go button, as this video demonstrates:
This is the result:
Understanding the formula
As you see, cell A1 contains the formula =ds(A2:B7;A9;A10:B12), which takes three input arguments and returns the text &YldCrv_A1:1.1
The prefix & indicates that &YldCrv_A1:1.1 is the handle name of some object. In fact it points to an object of type Yield Curve and can be used in any context where a yield curve is needed, such as in pricing of options.
The first input argument is the range A2:B7 that consists of 6 key-value pairs.
The top 5 pairs have obvious meanings.
The last pair FRA Ibor Index= %EURLibor|3M supplies the conventions of the deposit rate that underlies the forward contracts, the rates of which are listed in the table of the third argument A10:B12.
The prefix % indicates that this is a so-called trivial object, meaning that its contents are fully derived from the handle name so that they do not need to be created by some formula elsewhere in the spreadsheet.
I can see the index conventions within the Browse Area of the wizard by selecting the cell B7 containing the handle name %EURLibor|3M:
The top two rows contain the Ibor Type and Tenor of the selected object.
The respective values are EURLibor and %3M and their green color indicates that even though they are the values of an object the handle name of which is at the currently selected cell, they can still be edited within the wizard.For example, I may change %3M to %6M within the wizard, which results to the generation of the new handle %EURLibor|6M in cell B7, as this short video shows:
The last argument A10:B12 contains the table of forward rates.
The top row contains the elements #Expiry and #Rate, where the prefix # indicates that they are only column labels and not part of the table's values.
The actual values occupy two rows.
The first row holds the forward rate for a forward contract that starts in one month.
The second row holds the forward rate for a forward contract that starts in four months.
The underlying tenor of both contracts spans 3 months as specified in the conventions of the object %EURLibor|3M in cell B7.
Alternative construction of a Forward Rate table
Rather than supplying an object of type Ibor Rate in cell B7 that supplies a commonly shared underlying tenor for all forward rates, I can use a more flexible setup that allows individual forward rates to have different underlying tenors.
This is how the wizard can help me do that:
and this is the result:
Notice that the table now contains an additional column where the final maturity (in months) of each underlying contract is separately specified. If I forget the meaning of each column, I only need to select the cell containing the table's respective key (FRA Set=) and have the wizard display the associated information as shown below:
Using the Yield Curve object
For this I would refer you to the respective section of the Yield Curve out of Deposit Rates article
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