Excel Spreadsheet Function for Bicubic Interpolation
Suppose you have a grid of points, but you need to know the values somewhere between the points. Bicubic interpolation is a great way to do this. This spreadsheet with VBA source code gives a ready-to-use worksheet function that interpolates between any data laid out in a grid using bicubic Lagrange interpolation.
This example shows how to use the custom Excel function BicubicInterpolation() to calculate a value on a volatility surface, something commonly used in financial math for pricing derivatives.
The Worksheet Function Uses 16 Points for Bicubic Interpolation
The worksheet function selects the 4×4 grid of points that surround the interpolation point. It selects the best 16 grid points corresponding to the interpolation point and interpolates over those values.
The worksheet function is flexible. It automatically recognizes the row and column headers in the leftmost column and the topmost row. (In this case Row 2 and Column B) The convenience is that you can select the entire table without having to select the row and column headers as separate arguments in the worksheet function. In addition, the headers can be in forward or reverse order. The Excel function BicubicInterpolation() is compatible with both.
The first argument is the range containing the table of data with headers. This must be 5×5 or larger. The second and third arguments are the locations on the horizontal and vertical directions where you would like to interpolate.
Lagrange Interpolation with Uneven Grid Spacing
The grid can have uneven spacing like this. For example, the columns 2.1 and 2.89 (circled in brown) are closer together than 2.89 and 4.2 (circled in orange). The interpolation is carried out the same way no matter what kind of grid spacing you use for your dataset. This works the same way for the vertical axis and the horizontal axis.
Numerous articles explain the details of Lagrange interpolation and the Wikipedia article is very good. Here is an example Excel spreadsheet to go with those explanations.
Download the Excel Spreadsheet: Bicubic Interpolation Excel Function Example with Source Code.xlsm
