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
* Note: This spreadsheet code has a newer version. See Bicubic Interpolation – VBA Function Compatible with Both Arrays Ranges.
Do you happen to have the bicubic interpolation function written such that it can accept an array as a parameter instead of a range?
At this time it only supports the worksheet range, but it can be adapted to accept an array. If it is, it can accept a single array like the Excel version here or it can take the axial values separately from the data surface. Do you have a preference?
We usually focus on spreadsheet-ready VBA functions because they are the most accessible to the most people, but we also recognize that many people use VBA a lot and see value in something that can be used there directly. For example, the regularization scripts include both.
I have ivol surface data over time. It doesn’t exist in a 2d table format that your existing function can accept though. Each historical date is a record and the corresponding ivols for each expiry and strike are spread out over columns with no convenient organization and with redundant strike values and expiry values.
I wrote some simple code to read the data & organize it into a 2d array with unique expiries and strikes as the first row and column respectively, so I think simply modifying the existing function to accept an array for the Table parameter is all I need. Maybe i’m mistaken in that regard though
Are you offering to modify the function for me? Would there be a cost to doing this?
Generally speaking, I think users that seek to use bicubic interpolation for ivol data would have a use for a function that accepts arrays as parameters. Anyone looking at historical ivol data should have a problem like what I’m experiencing.
I think I get it now. You already arranged the numbers in the layout on the spreadsheet, but they are stored in an array in VBA. Since the data layout is the same, it should be a straightforward change to the code to accept both formats. I’ll make the update and post an article for it… in about a week. It’s free of charge because we will make it available to everyone. Thank you for pointing out this area for improvement.
Thank you. Am looking forward to using your new function.
Thanks, using the new function now. Works great!
Is it ok if I e-mail you to ask a question?
yep, send it to me
Thanks for this! I have a chart with a x-axis of length’s and y-axis with width’s. The chart is the labor rate based on both dimensions. I need to use this chart so that when someone generates a estimate the labor rate is based on the dimensions. So I would have a estimate page with Length and Width and based on the entry the labor rate is calculated. Thanks again
This is absolutely amazing. I got my work cut out by using this VBA code in my file. Tans