interpolating the interest rates in excel using VLOOK

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

One idea: as much as people on an Excel forum might hate me for it, but Quattro Pro (and maybe other spreadsheets) has a built in linear interpolation function that would make short work of this kind of problem. Excel does not. If you are not stuck with Excel so that other spreadsheets are an option, that might be worth considering.

If you search this forum, there have been a few VBA UDF's suggested for linear interpolation problems. If you are allowed to use UDF's, then these can be good solutions to this kind of problem.

When I can only use worksheet functions for linear interpolation, I will usually use multiple cells. One cell has a =MATCH() function to locate the interval containing x and y =MATCH(J4,$B$3:$B$19,1)
Four =INDEX() functions to return the lower and upper bounds for x and y =INDEX($B$3:$B$19,$P4) =INDEX($B$3:$B$19,$P4+1) etc. assuming match() function is in P4,
and, finally, a cell with the interpolation formula in it to find the new y at new x. (newy-y1)/(newx-x1)=(y2-y1)/(x2-x1) solved for newy or newx depending on need.

An observation: when you upload three files into one post, all with the same name, it is difficult to tell which one is the "latest" work.

I'm not certain what your question is -- maybe because it is starting to feel more like a database type question, and I'm not good with databases. Instead of Match and INdex to extract the needed data points, you might look at the built in database functions (like DGET() http://office.microsoft.com/en-us/ma...620.aspx?CTT=1)