interpolating the interest rates in excel using VLOOK

[h1196844]

Interpolate.xlsxInterpolate.xlsxHi guys,

I spent couple days trying to write some formulas in excel that can help me to calculate the interpolation of interest rate.

1. In column L, I want to use VLOOKUP to find the interest rate for the lower bound.
Like in example 1, if the loan is 40 days, the vlookup should return a 31day interest rate 2.9% in this case.
2. In column M, I want write a formula that can automatically to get the difference in days between my loan and the upper bound. In example 1, because my loan is from 21st May to 30th June, so it is 21st July - 30th June = 21days.
3. In column N, this part will become very easy once step 1&2 is done. Basically what it does is interpolating the interest rate.

I have 500 loans that need to interpolate manually for their interest rates.... I am hoping some smart guy can help me out writing formulas that I can just drag the 500 loan list and come up with the correct rate.
Interpolate.xlsx
Thanks a lot ....