Spreadsheet Applications

UK Gilt zero-coupon yield curve

This spreadsheet demonstrates constructing a zero-coupon yield curve from market gilt yields, using the basis spline methodology and approach.

A technical description of the methodology appeared in the book "Capital Market Instruments" by Moorad Choudhry et al (FT Prentice Hall 2001). The spreadsheet is currently set as at June 1997. This is deliberate, as the spreadsheet is designed as an instructional tool.

The user will be required to adjust the worksheet to handle bonds of any market from today, as well as adjust the Visual Basic module to handle decimal, and not tick, prices. The user inputs the current price of the set of bonds in column C. Hitting the macro button constructs the curve. The second chart shows the spread of each bond in the table from the spot curve.

Note that the curve may also be constructed from a set of just five bonds, at the top of the chart. These would be the benchmark bonds of any market, and the curve is constructed from using the other macro button. The price data for these bonds is entered in the normal manner in the list below the "matrix".