11. Excel For Finance Tips - Linear Interpolation
Interpolation is a methodology of guessing what the value would be in between 2 points.
Linear interpolation assumes that between the 2 points is a straight line.
Lets have a look at an example:
Suppose we know the Zero Rate for 1 year: 3% We also know the 4y Zero Rate: 8% ... how do we calculate the 2 year Zero Rate ?
Here's how our data looks in Excel
The formula we use is: =C4+(C5-C4)*(E4-B4)/(B5-B4)
Which translates to: Take the first rate, and add a proportion of the difference of the first rate and the second rate.
- That proportion is calculated by how close we are to the second rate. It's often good to view Interpolation on a graph so that it becomes clearer what we're doing:
- So clearly, all we're doing is drawing a straight line between 2 points, and Linear Interpolation gives us the value for a point in between the 2 points.
Training Video on Linear Interpolation: