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.

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).

Recommended for Deriscope starters: The Overview and Quick Guide pages.

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:B5), which takes one input argument and returns the text &GBPCrv_A1:1.1.

The prefix & indicates that &GBPCrv_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 overall structure is similar to that of a yield curve created out of deposit rates as explained in my related post.

As with the deposit rate case, the above formulas include only the mandatory arguments. The object contents displayed in the wizard include all arguments, including the optional.

The pair Ibor Index= %GBPLibor|3M supplies the conventions of the deposit rate that underlies the forward contracts, the rates of which are listed in the table at the bottom.

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 B10 containing the handle name %GBPLibor|3M: 

The top two rows in the grid in the wizard contain the Ibor Type and Tenor of the selected object.

The respective values are GBPLibor 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 the %3M to %6M within the wizard, which results to the generation of the new handle %GBPLibor|6M in cell B10, as this short video shows: 

The top row of the table of forward rates 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 1 month.

The second row holds the forward rate for a forward contract that starts in 4 months.

The underlying tenor of both contracts spans 3 months as specified in the conventions of the object %GBPLibor|3M in cell B10. 

Browsing through the Yield Curve Object's Contents

How to use the wizard to browse through the contents of the Yield Curve object is demonstrated by a video in the respective section of my post on curve creation from deposits, futures and swaps.  

Browsing through the Cash Flows

The pair _Cash Flows= $Set#9 shown in the wizard grid above can be very useful for diagnostic purposes, as it contains the details of the swap instruments used in the curve construction.

Detailed description is available at the cash flows section of my post on curve creation from deposits, futures and swaps. 

Dealing with a Curve Bootstrapping Failure

The steps you need to take in case of failure are exemplified in the diagnostics section of my post on curve creation from deposits, futures and swaps.  

Alternative construction of a Forward Rate table

Rather than supplying an object of type Ibor Rate in cell B10 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.

You may refer to this similar alternative deposit rates construction for a demonstration on how to use the wizard in this regard.

Below 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 15 containing the key 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

Click on YieldCurveFwd.xlsx to download the spreadsheet produced with the above steps.

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