How To Draw Yield Curve In Excel

With this article I want to show you how to create a bond yield curve in Excel, i.e. a yield curve out of bond prices, using the open source QuantLib analytics library. I will present both alternative spreadsheet interfaces to QuantLib, which are the QuantLibXL and Deriscope.

The main use of a yield curve is that it allows us to calculate the present value of a cash flow receivable at any future time T by simply multiplying the cash flow amount with the discount factor implied by the yield curve for that time T.

The catch is that every future cash flow – like anything scheduled to occur in the future – is not guaranteed to take place. There is always a chance that some exogenous event – a falling meteorite is always a sufficiently vivid example - will turn the lights off before the money gets paid.

So every cash flow is uncertain, but some cash flows are more uncertain than others.

For example, you may consider a promise from the US treasury for a cash payment of one million dollars in 10 years from now to be more certain than a similar promise from a New York drug dealer (or perhaps the other way around given the current US debt).

Obviously then you would pay more money today for the US treasury promise – let's say 900 thousand - than for the drug dealer's promise - let's say 500 thousand. In that case, the US treasury's yield curve would imply a 10-year discount factor of 0.9 whereas the drug dealer's yield curve would imply a mere 0.5.

If you were to strike an obviously not very legal deal with the drug dealer – who by the way should be quite adept in striking deals by being a … dealer – involving a stream of perhaps complex and contingent future payments, you would want to take into account his reduced creditworthiness by applying a matching yield curve rather than the US treasury yield curve.

This is the reason why we need sometimes to create a yield curve out of prices of bonds issued by a specific company rather than Libor or swap rates.

For example, it is sensible to use a yield curve derived from bonds issued by Microsoft when you evaluate the intrinsic Microsoft stock price based on estimated future Microsoft dividends.

In what follows, I will show you two different methods by which you can create a yield curve from bond prices in the spreadsheet using the analytics available by QuantLib.

If you are not familiar with Deriscope, visit the Quick Guide for an introductory tour to the most important Deriscope features.

Creating a Yield Curve from bond prices using QuantLibXL

For those of you who haven't heard of QuantLibXL before, it is the official open source Excel interface to QuantLib. It exports to Excel hundreds of functions that call internal QuantLib routines. A QuantLibXL user is expected to be overly familiar with the QuantLib C++ structure because the exported functions are in almost one-to-one correspondence with the corresponding C++ class methods. While this may prove frustrating to a QuantLib novice, it is perfect for people who have installed and customized the C++ source code to fit their special needs. The latter people can then easily apply the QuantLibXL wrappers on their altered source code and generate the respective Excel functions.

It is definitely possible to create a yield curve using QuantLibXL out of bond clean prices, but the process is quite long and perplexed. For a detailed treatment – including spreadsheet and screenshots - I refer you to the excellent article of Vasily Nekrasov, the site of whom I recommend for gaining a deep and intuitive picture of current market trends.

Below I summarize the most important steps of Vasily's guide.

Let's say you start with N different bonds, all from the same issuer.

Step 1: Use the qlSchedule() function to create N objects containing the time schedules of the respective coupon payments.

Step 2: Apply the qlLeg() function on the previously created Schedule objects to create N objects containing the respective cash flows, in effect adding the amount information to the previous pure date data. This step is tricky and involves an intermediate call of ohFilter() applied on the result of qlScheduleDates() and qlScheduleEndDate(), the latter acting on the Schedule objects created in Step 1.

Step 3: Use the qlBond() function to create the final bond objects from the previously created leg objects.

Step 4: Use the qlBondHelper() function to combine the bond objects with the respective clean prices into a new set of objects.

Step 5: Use the BondHelper objects as input to the qlPiecewiseYieldCurve() function to create the final yield curve object.

You may then use the created yield curve object as input to other QuantLibXL functions, such as the qlYieldTSDiscount () that calculates the discount factor for a given maturity date.

Creating a Yield Curve from bond prices using Deriscope

Using the Deriscope interface to QuantLib, a single ds() function suffices to create a yield curve object out of bond prices.

You may type the function by hand or – more conveniently – let the wizard generate it.

Simply search in Type Selector for a type called Yield Curve and then check the Use Bonds flag inside the input parameters screen, as this video demonstrates:

To keep things simple I have made no other selections or fine tunings, but I showed you the existence of the special key Use Clean Price that allows you to choose whether the supplied bond prices are clean or dirty.

After pressing the Go button, Deriscope generates the respective formulas – a total of 6 - and pastes them in the spreadsheet.

In the picture below, I have shifted the formulas around so that they are all visible.

The red color cells are those containing the ds formula.

The green color cells contain only a link to some other sell.

All other cells contain no formulas, while blue color indicates data to be input by the user.

The dependency arrows show the relationships clearly.

Yield Curve creation in Excel from bond prices using Deriscope and QuantLib

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 with obvious meanings.

More interesting is the last argument A10:B12 that contains the table of bond prices.

The top row contains the elements #Bond and #Price , 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 price for the bond labelled &FxdBnd_D1:1.1 created in cell D1 , which is a 2-year bond as the respective schedule underneath betrays.

The second row holds the price for the bond labelled &FxdBnd_G1:1.1 created in cell G1 , which is a 5-year bond.

Using Real Market Data

The Deriscope wizard only creates template data.

In order to use real market data, I need to overwrite the various entries with my own data that describe real bonds and prices.

As an example, I will try to replicate the work of Vasily mentioned above.

So I will use three bonds issued by the German government maturing in 2023, 2028 and 2044 and carrying a fixed coupon of 0%, 0.5% and 2.5% respectively.

Since these three bonds differ from each other only with regard to issue date, maturity and coupon, I only need to create the Schedule object once, in cell B8 in the picture below.

Then I produce the three Schedule objects corresponding to my three German bonds in column F by applying the Deriscope Clone function.

Similarly I create a single Bond object in cell E8 and produce the three Bond objects in column H using the Clone function as well.

Creation of German government bond objects in Excel using Deriscope and QuantLib

Creating the German Government Yield Curve

Now that I have my Bond objects created in column H, I can edit the wizard-generated yield curve creation formula so that the third argument references a table that contains these Bond objects and their actual traded market prices, as shown below:

Yield Curve creation in Excel from German government bond prices using Deriscope and QuantLib

Using the Yield Curve object

As I have already described in the respective section of the Yield Curve out of Deposit Rates article, I may paste in the spreadsheet any of the wizard-listed functions that apply to an object of type Yield Curve.

I choose here the function DF that returns the discount factor for a given maturity.

This function is capable of processing an array input of dates as well, so that I produce the sequence of discount factors as shown below:

Discount Factors of German Government yield curve in Excel using Deriscope and QuantLib

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