Simple Linear Interpolation without VBA Macro in Excel

This post presents a simple but useful Excel formula for the linear interpolation without VBA Macro. This is most frequently used especially when dealing with repeated zero curve interpolations. Instead of making a VBA macro, we can use the built-in Excel function for linear regression quickly .

Problem

The linear interpolation is easy in R but annoying in Excel since there is no built-in interpolation function.

Our problem is to find a set of interpolated zero rates from the input zero curve (yellow points in the figure below) in Excel. It is typical that the set of maturities or dates are smaller than the set of cash flow dates of trades. This situation requires interpolations. As we will see later, we add additional three columns for this purpose.

Solution

To the best of our knowledge, Excel does not provide a built-in interpolation function. Since we do not make a VBA macro function for this purpose, the linear regression built-in Excel function (LINEST) is used.

LINEST function takes four input arguments. The first and second parameter are Y and X. The third parameter is the flag of a constant coefficient, which is set to TRUE. The fourth parameter is set to FALSE since we do not use statistics in this job.

1

LINEST(known_y's, [known_x's], [const], [stats])

cs

We estimate coefficients of linear regressions using two adjacent data successively. This iteration results in a set of coefficients (a, b). This output consists of a set of \(1 \times 2\) vector that this formula should be carried out by using CTRL+SHIFT+ENTER and dragging sequentially.

It is worth noting that the first slope and constant are set to a zero(0) and the first zero rate because it does not need any interpolation.

Using the above estimated coefficients, we can easily calculate fitted values for any dates. But we should determine which coefficients are used for specific date in advance. This is accomplished by finding a relative location of specific date from the input dates table. These two operations are illustrated with their Excel formula in the following figure.

In other words, after determining a relative location or row number by using COUNTIF function, the corresponding coefficients are selected by using OFFSET function. Using these selected coefficients, interpolated zero rate at any time (\(z_t\)) is calculated in the following way.

\[\begin{align} & z_{t} = \alpha_i + \beta_i \times t \\ \\ & t_{i-1} \lt t \le t_i \\ & i = 1, ..., n \end{align}\]
The following figure draws the line of interpolated zero curve with input zero rates.

In this post, we generate interpolated zero rates using LINEST Excel built-in function without making a VBA macro. These formula are copied and pasted easily for other cases.

In particular, this is adequate for pricing of FX trades such as FX Forward, FX Swap and so on in Excel.\(\blacksquare\)