Interpolation and Extrapolation in Excel

Interpolation and extrapolation are two methods of estimating values based on existing data, but they differ in how they are used and the accuracy of their results.

Interpolation is the process of estimating values within a range of known data points, while extrapolation is the process of estimating values outside of that range. For example, if you have data on the interest rates of bonds with different maturities, interpolation can be used to estimate the interest rate of a bond with a maturity that falls within the range of the known data points. Extrapolation can be used to estimate the interest rate of a bond with a maturity that falls outside the range of the known data points.

Interpolation is typically more reliable than extrapolation, as it involves using data points that are close to the unknown value and follow a consistent trend. Extrapolation, on the other hand, can be less accurate and more uncertain, as it involves making predictions beyond the range of the known data points, which may not be representative of the unknown value.

Interpolation and extrapolation are useful for financial analysis and decision making, as they can help investors and analysts estimate the value or potential of a security, such as a bond, a stock, or an option. However, they should be used with caution and awareness of their limitations and assumptions.

Basic Theory:

• Interpolation: This method estimates values within the range of known data points. It
  assumes a smooth and continuous relationship between the existing data.
• Extrapolation: In contrast, extrapolation extends the known data to predict values
  outside the given range. It assumes that the established pattern continues beyond the observed data.

Procedures:

Let’s start with the basic steps for both interpolation and extrapolation:

1. Interpolation:
   • Select the known data points (X and Y values).
   • Use Excel’s built-in functions like LINEST or FORECAST to generate a
     linear regression equation.
   • Apply the equation to estimate the desired Y value for a given X within the range.
2. Extrapolation:
   • Similar to interpolation, select the known data points.
   • Use Excel’s functions to generate a regression equation.
   • Extend the equation beyond the existing range to predict Y values for new X values.

Comprehensive Explanation:

Consider a scenario where you have historical data on the revenue of a company over the past five years:

Scenario:

Suppose you want to estimate the revenue for the 6th year.

Calculation:

1. Interpolation:
   • Use the LINEST function to find the linear regression equation:
     =LINEST(B2:B6, A2:A6, TRUE, TRUE).
   • Apply the equation to estimate the revenue for the 6th year:
     =INDEX(LINEST(B2:B6, A2:A6, TRUE, TRUE), 1, 2) * 6.
2. Extrapolation:
   • Extend the regression equation to the 6th year:
     =INDEX(LINEST(B2:B6, A2:A6, TRUE, TRUE), 1, 2) * 6.

Result:

The estimated revenue for the 6th year using both interpolation and extrapolation would be the same.

Other Approaches:

• Polynomial Regression: For non-linear relationships, use higher-order polynomial
  regression equations.
• Data Tables: Create data tables with different input values to observe the varying output,
  facilitating a more comprehensive analysis.