Download (direct link):
This section focuses on the issues that any strategy for building such curves needs to address. The assumption is that an application in Excel needs to be able to value future cashflows consistent with a set of market prices of various financial instruments (the input prices). There are several questions to address before deciding how best to implement this in Excel:
• Where do the input prices come from? Are they manually input or sourced from a live feed or a combination of both?
• Are the input prices changing in real-time?
• Does the user’s spreadsheet have access to the input prices or is the discount curve constructed centrally? If constructed centrally, how is Excel informed of changes and how does it retrieve the tabulated values and associated information?
• Is the discount curve intended to be a best fit or exact fit to the input prices?
• How is the curve interpolated? What is modelled over time - the instantaneous forward rate, the continuously compounded rate, the discount factor, or something else?
• How is the curve’s data structure maintained? Is there a need for many instances of similar curves?
• How is the curve used? What information does the user need to get from the curve?
There is little about building curves that can’t be accomplished in an Excel worksheet, although this may become very complex and unwieldy, especially if not supported by an add-in with appropriate date and interpolation functions. The following discussion assumes that this is not a practical approach and that there is a need to create an encapsulated and fast solution. There is nothing about the construction of such curves that can’t be done in VBA either: the assumption is that C/C++ has been chosen.
Excel Add-in Development in C/C++
The possibility that the curve is calculated and maintained centrally is not discussed in any detail, although it is worth noting the following two points:
• The remote server would need a means to inform the spreadsheet or the add-in that the curve has changed so that dependent cells can be recalculated. One approach would be for the server to provide a curve sequence number to the worksheet, which can then be used as a trigger argument.
• The server could communicate via a background thread which would initiate recalculation of volatile curve-dependent functions when the curve had changed.
In any case, delays that might arise in communicating with a remote server would make this a strong candidate for use of one or more background threads. It is almost certain that a worksheet would make a large number of references to various parts of a curve, meaning that such a strategy would ideally involve the communication of an entire curve from server to Excel, or to the DLL, to minimise communication overhead.
The discussion that follows focuses on the design of function interfaces that reflect the following assumptions:
1. Input prices are fed into worksheet cells automatically under the control of some external process, causing Excel to recalculate when new data arrive.
2. The user can also manually enter input price data, to augment or override.
3. The user will want to make many references to the same curve.
Assumptions (1) and (2) necessitate that a local copy of the curve be generated. Assumption (3) then dictates that the curve be calculated once and a reference to that curve be used as a trigger to functions that use the curve.
The first issue to address is how to prepare the input data for passing to the curve building function. The most flexible approach is the creation of a table of information in a worksheet along the following lines:
Instrument type Start date End date Price or Rate Instrument-specific data... (multiple columns)
The format, size and contents of this table would be governed by the variety of instruments used to construct the curves and by the implementation of the curve builder function. Doing this leads to a very simple interface function when compared to one alternative of, say, an input range for each type of instrument. The addition of new instrument types, with perhaps more columns, can be accommodated with full backwards compatibility - an important consideration. For this discussion, it is assumed that the day basis, coupon amount and frequency, etc., of input instruments are all contained in the instrument-specific data columns at the right of the table. (Clearly, there is little to stop the above table being in columns instead of rows. Even where a function is designed to accept row input, use of the TRANSPOSE() function is all that’s required.)
Example Add-ins and Financial Applications
Description Takes a range of input instruments, sorts and verifies the contents as required, creates and constructs a persistent discount curve object associated with the calling cell, based on the type of interpolation or fitting encoded in a method argument. Returns a two-cell array of (1) a label containing a sequence number that can be used as a trigger and reference for curve-dependent functions, and (2) a time-of-last-update timestamp.