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.
Download Spreadsheet to look at Interpolation in Excel
Training Video on Linear Interpolation:
| Attachment | Size |
|---|---|
| Interpolation-Excel.xls | 28.5 KB |
- Nick's blog
- Login or register to post comments
- 43297 reads
Interpolation
Hi Nick,
Wondering if you can provide some insight on how to use interpolation to figure out a problem. We have a range of rates, 3.125, 3.25, 3.375, 3.5 and 3.625. While the only data points we know are 3.4 equals 103.25. We were told to use interpolation to figure out what the other rates corresponding figures would be. Any help you could provide would be great.
Thanks,
jrs