For the task described above, we usually have our front office system or tools provided by some third-party vendor, which are then performing all those required complex calculations quietly behind the scenes. Figures are popping up in the screen, finding their way into some strange reports and everyone are happy. Needless to say, we should be able to perform those calculations also by hand, if so required. This is only my personal opinion on the matter, of course. During this small project, we are creating a program, which takes in market data as input parameter. From this market data, the program is then performing all those complex calculations (bootstrapping, interpolations) and returning discount factors and simply-compounded Libor forward rates.
CURVOLOGYWhen constructing discount factors and forward rates from market data, one has to make a lot of different decisions. Just for an example
- How to construct short-end of the curve? For what maturities are we going to use cash securities?
- How to construct mid-part of the curve? Are we using futures contracts on Libor or Forward Rate Agreements?
- Are we going to make adjustments for convexity effect?
- How to construct long-end of the curve? For what maturity we start to use swap rates?
- What kind of interpolation methods are we going to use?
- Do we interpolate discount factors or spot rates?
MARKET DATAFor this project, I have been using dataset and curve creation procedures as presented in Richard Flavell's excellent book Swaps and Other Derivatives. In this book, Flavell is giving complete treatment, how to construct Libor zero curve (discount factors and forward rates). One of the things why I like so much this book is the fact, that Flavell is not assuming anything. Instead, he is always deriving everything from the beginning. Great thing is also, that the book has all the example calculations dissected in the attached Excel workbooks. Personally, these Excels have been sort of a goldmine for me, when I have been studying different valuation issues.
PROJECT OUTCOMEThe result of this project is XLL addin for Excel, which calculates discount factors and simply-compounded forward rates for USD Libor. We will use Excel-DNA for interfacing our C# program with Excel. The scheme what has been used for this project, has been fully explained in this blog post. So, carefully follow the instructions given in that post, when interfacing the program with Excel. Needless to say, one should be able to use the core classes of the program (the classes which are actually creating the curves) in any other C# project, without Excel interfacing.
PROGRAM DESIGNRough UML class diagram for this program is shown below. The purpose of this diagram is to get some conceptual overview, how the program is working and how the objects are related with each other. Since I am not UML expert, I am presenting my deepest apologies for its incompleteness and possible errors.
In the first stage, Client (CreateUSDLiborCurves, which will be presented in the section Excel Interfacing) is requesting ExcelBuilder (specialization of abstract MarketDataBuilder) to build market data and return it as a CurveData object. Then, Client is creating and using USDLiborZeroCurve object (implementation of ICurve interface). Inside USDLiborZeroCurve object, the curves (discount factors and forward rates) are going to be created in different types of sequential calculations (data checking, data selection, bootstrapping, interpolation). Finally, Client is requesting ExcelPrinter (specialization of abstract CurvePrinter) object to print the resulting dataset into Excel worksheet. During the calculation process, USDLiborZeroCurve is also using two static classes (DateConvention and Interpolation, not shown in UML diagram), which contains different types of date-related and interpolation-related methods.
THE PROGRAMThe part of the program, which is creating the outcome curves (discount factors and forward rates), is shown below. Create a new Class project and just copyPaste everything into a new cs-file.
EXCEL INTERFACINGFor interfacing the previous C# program with Excel, carefully follow all the instructions given in this blog post. Add another Class module (a new cs-file) into this project and copyPaste the following program into this file. This is the program (CreateUSDLiborCurves), which will be called from our Excel worksheet.
EXCEL WORKSHEET SETTINGSPrepare the following market data (Flavell's book and Excel workbook on chapter three) and named ranges (marked with yellow color) into Excel worksheet. The program will take settlement date and market data range (date, rate, rate type) as input parameters. Note, that some of the parameters needed to create the curves have been hard-coded in the program (interpolation method, date conventions, number of contracts for cash and futures). However, it should be fairly straightforward to include all these parameters to be read directly from Excel worksheet. Finally, insert a form control button into worksheet and assign a macro for it (CreateUSDLiborCurves).
Finally, a couple of notes concerning the market data setting. When creating Curve object, Client has to provide number of cash securities and number futures/FRA contracts as input parameters in constructor. Example program has been hard-coded to use one cash security and three futures contracts and it will hereby use Libor swap contracts starting on the year two. Now, for setting market data for this program, there are some general rules. First, the latest maturity for cash securities has to be later than the first maturity for futures/FRA contracts. Also, the last future/FRA maturity has to be later than the first swap contract maturity date minus one year.
After I press button in my Excel, I will get the following results (columns H to J) from the program. The range consists of dates (quarterly up to 8.2.2038), discount factors and simply-compounded Libor forward rates for USD. The first result row should be interpreted as follows : discount factor (0.99220) gives factor for discounting three-month cash flow. Simply-compounded Libor forward rate (3.1450 %) gives three-month forward rate for a period, which is starting on 6.2.2008 and ending 6.5.2008. Similarly, Libor forward rate (2.7837 %) gives three-month forward rate for a period, which is starting on 6.5.2008 and ending 6.8.2008.
After this, using generated curves (discount factors and forward rates) is straightforward. As an example, I have calculated PV for 2-year cap on 3-month USD Libor. After creating date schedule for this security, getting forward rates and discount factors can be requested by using familiar Excel worksheet functions.
AFTERTHOUGHTSThe procedure of creating discount factors and Libor forward rates programmatically in C#, has been fully opened in this blog post. Source market data and creation procedures are following examples taken from the book written by Richard Flavell. With the tools presented in this blog, one should also be able to interface this program with Excel, if so desired.
I would like to thank Govert Van Drimmelen again for his amazing Excel-DNA, what I am always using for interfacing my C# program with Excel. For learning more things about Excel-DNA, check out its homepage. Getting more information and examples with your problems, the main source is Excel-DNA google group. Remember also, that Excel-DNA is an open-source project, and we (the happy users) can invest its future development by making a donation.
Finally, Thanks for spending your precious time in here and reading my blog.