Simpler and More Convenient Linear Interpolation in Excel

This post presents a simpler and more convenient Excel formula for the linear interpolation without VBA Macro. This is particularly necessary in such a curve fitting as zero curve pricing, where zero rates are required at any points in maturity.

Problem

This linear interpolation probem is the same as in the following previous post.

• Simple Linear Interpolation without VBA Macro in Excel

Step 1 : Treatment of outside values

It is commonly assumed that the \(y\) values to be returned when input \(x\) values are less than \(x_1\) or greater than \(x_N\) are \(y_1\) or \(y_N\) respectively. As such, we can write an excel formula without main body which is filled with zero as follows.

=IF(E4 <= $B$4,$C$4,IF(E4 >= $B$11,$C$11,0))

The above Excel formula is dragged for the range which will be filled with interpolated values as follows.

Step 2 : Find two nearest points which include a target

Now we need to fill area of 0's with interpolated values. It can be done by finding two nearest \(x\) values (\(x_1, x_2\)) around a target \(x_t\) value. Between these two points a target \(x\) value is located.

For example, when \(x_t\) = 2021-08-30, each row of \(x_1\) = 2021-08-02 and \(x_2\) =2021-09-02 are 3 and 4 respectively. These relative location (row) can be found respectively by the following Excel formula with CTRL+SHIFT+ENTER.

=SUM(IF($B$4:$B$11 < E12,1)) =SUM(IF($B$4:$B$11 < E12,1))+1

Therefore, each values of \(x_1\), \(x_2\), \(y_1\), and \(y_2\) can be found respectively by using index() function.

=INDEX($B$4:$B$11,SUM(IF($B$4:$B$11 < E12,1))) =INDEX($B$4:$B$11,SUM(IF($B$4:$B$11 < E12,1))+1) =INDEX($C$4:$C$11,SUM(IF($B$4:$B$11 < E12,1))) =INDEX($C$4:$C$11,SUM(IF($B$4:$B$11 < E12,1))+1)

Step 3 : Calculate interpolated values

Given \(x_1\) and \(x_2\) which have the corresponding y values (\(y_1\) and \(y_2\)), we can find \(y_{t}\) as you have already known well.

\[\begin{align} y_{t} = \frac{y_1 \times (x_2-x_{t}) + (x_{t} - x_1) \times y_2}{x_2 - x_1} \end{align}\]
For example, when \(x_t\) = 2021-08-30, \(y_t\) can be found by the following Excel formula with CTRL+SHIFT+ENTER.

1 2 3 4 5 6 7 8 9 10

=(   (INDEX($B$4:$B$11,SUM(IF($B$4:$B$11<E12,1))+1)-E12)   *INDEX($C$4:$C$11,SUM(IF($B$4:$B$11<E12,1)))  +(E12-INDEX($B$4:$B$11,SUM(IF($B$4:$B$11<E12,1))))   *INDEX($C$4:$C$11,SUM(IF($B$4:$B$11<E12,1))+1) ) / (    INDEX($B$4:$B$11,SUM(IF($B$4:$B$11<E12,1))+1)   -INDEX($B$4:$B$11,SUM(IF($B$4:$B$11<E12,1))) )   Colored by Color Scripter

cs

The above Excel formula is dragged for the range which will be filled with interpolated values as follows.

Let's use it

The final Excel formula for the linear interpolation is as follows.

1 2 3 4 5 6 7 8 9 10 11 12

=IF(E12<=$B$4,$C$4,IF(E12>=$B$11,$C$11,  (   (INDEX($B$4:$B$11,SUM(IF($B$4:$B$11<E12,1))+1)-E12)   *INDEX($C$4:$C$11,SUM(IF($B$4:$B$11<E12,1)))  +(E12-INDEX($B$4:$B$11,SUM(IF($B$4:$B$11<E12,1))))   *INDEX($C$4:$C$11,SUM(IF($B$4:$B$11<E12,1))+1)  )/(    INDEX($B$4:$B$11,SUM(IF($B$4:$B$11<E12,1))+1)   -INDEX($B$4:$B$11,SUM(IF($B$4:$B$11<E12,1)))  ) ))   Colored by Color Scripter

cs

Of course, after the copy and paste of the above Excel formula, changes in range of X and Y are required as you know. \(\blacksquare\)