Bicubic Interpolation Excel Spreadsheet Function

Bicubic Interpolation on a Vol Surface in ExcelExcel 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.

 

 

Excel Bicubic Interpolation Vol Surface with HighlightingThe 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.
 

Excel Bicubic Interpolation with Uneven Grid SpacingLagrange 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.

About these ads

8 comments

  1. 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.

  2. 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.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s