Business Functions Library for Excel

 Interpolation family Interpolation Description: Introduction: This family just deals with interpolation, where the number you are looking up may fall inbetween two values, and you want to in a sense get an average. Technically, of course, it is about putting the ranges you are looking up against on a graph (x=input, y=output) and returning the appropriate y value for the input x. This procedure is very distinct from curve-fitting, which you will see elsewhere in the library, because whereas a curve-fit fits a mathematical equation to some data where there is no guarantee that the equation goes through all the points, with interpolation the resultant curve must go through all the data points. In this sense Interpolation is perhaps more useful to financial analysts as it has less uncertainty involved, and who are not so concerned with the mathematical equation of the data, even if one actually exists.The are two kinds of function here, one that deals with plain numbers, and one that deals with a special kind of number that optionally uses extra inputs, dates. Within the two kinds of number and date functions, there are two ?flavours?. There is a short-form function that tries to keep the number of inputs to a minimum, and an ?all-singing? function that has the full range of inputs.The simple number interpolations are Interp (the short-form) and Interpolate (the all-singer). The date interpolation functions return a number given an InputDateValue, so this is where you might want to read an index or a price from a range governed by dates.Types of InterpolationMany of us think of interpolation as being just an exercise in linear or straight-line interpolation, or, when the input value is outside the data, extrapolation. In truth, very often when you think something cleverer than plain linear interpolation ought to work well, linear interpolations is actually the best thing to use. It has the benefit that people understand it and it is readily auditable and there is sometimes a resistance to doing something smarter.The default for our interpolation functions is always linear interpolation, but we do aim to cover the standard forms of mathematical interpolation in its wider sense, and have put in one method, exponential, that we ?borrowed? from techniques used in the petroleum industry, would you believe.The basic curve types available are cosine and cubic. Cosine is very good for producing a flowing, continuous curve, as is cubic, and they do not require any other inputs other than you specify the correct InterpType. Hermite is similar to Cubic, but you can optionally specify two extra variables that can further determine its shape, Tension and Bias.Exponential interpolation as implemented here has been specially developed by the Business Functions programmers, although the idea has been borrowed from the logic used in oil reservoirs where there is a constant rate of production decline. This logic has a particular applicability and theoretical relationship with any circumstance where there is a constant and continuous rate of decline or indeed growth, so inflation and continuous interest also fit this model. You only need to set InterpType (to 4) to model exponential interpolation, but you can also set a ?hyperbolic? exponent in the Tension variable, which in the special case of 1 becomes harmonic (growth or decline). Note that what exponential interpolation can?t do is guarantee a smooth curve, and if your data is more than 2 points and is not exponential in the underlying sense, there will be sharp changes of gradient (discontinuities), at the data points themselves. Functions in the Interpolation family (5)Interp InterpolateInterpolate Do a lookup that interpolates between valuesInterpDate Interpolate for Date inputs.InterpolateDate Do a lookup that interpolates between date valuesIntersection Find the intersection of 2 lines

Business Functions Ltd, London, UK Website Design: Webpure