Fill points using interpolating closet two points

[NG7267]

I have following USD LIBOR rates for key maturities and want to fill LIBOR rates for all dates from day 1 to 360 days by interpolating available rates. Appreciate very much for help.

USD Days (x) Rate (y)
*USD LIBOR - overnight 1 0.39340*%
*USD LIBOR - 1 week 7 0.42095*%
*USD LIBOR - 2 weeks 14 -
*USD LIBOR - 1 month 30 0.44930*%
*USD LIBOR - 2 months 60 0.52440*%
*USD LIBOR - 3 months 90 0.62360*%
*USD LIBOR - 4 months 120 -
*USD LIBOR - 5 months 150 -
*USD LIBOR - 6 months 180 0.89410*%
*USD LIBOR - 7 months 210 -
*USD LIBOR - 8 months 240 -
*USD LIBOR - 9 months 270 -
*USD LIBOR - 10 months 300 -
*USD LIBOR - 11 months 330 -
*USD LIBOR - 12 months 360 1.20520*%

New table should be as follows.
Days(x) Rate (y)
1 0.39340*%
2 ?
3 ?
. ?
. ?
. ?
360 1.20520*%

Formula should be worked for negative rates since LIBOR EUR is currently negative.

[cbatrody]

Hi,

You would get better help if you attach a sample workbook with enough data to demonstrate your requirement. Make sure your desired results are shown, mock them up manually if necessary. Remember to desensitize the file by removing all confidential information before upload!

See the following URL for help on how to upload a file.
http://www.excelforum.com/members/da...ch-a-file.html

[NG7267]

Thank you for your feedback. Excel worksheet is attached herewith.I want to fill column H in both EUR LIBOR & USD LIBOR
sheets. ABC columns represent available data.

[MrShorty]

Unfortunately, Excel does not have a built in linear interpolation function (Quattro Pro does, which can make task like this easier, if that is an option for you.)

Here's how I would approach this situation:

1) Remove "blank" entries from the lookup tables in A:C. This will make the "lookups" below a lot easier. This can be accomplished by manually removing the blank rows, using a filter, using a pivot table, or any other method that you deem appropriate. Let us know if you need help with this step.
2) Add a couple of helper columns to compute slope and intercept for each consecutive pair of points to this table. If you have removed the blank rows, this should be a simple =LINEST() function or =SLOPE() and =INTERCEPT() functions. Help files for these functions here (https://support.office.com/en-us/art...rs=en-US&ad=US ) under statistical functions.
With the lookup table prepared in this way, the interpolation can be performed as follows:
3) Use a lookup function (such as VLOOKUP()) to return the slope and intercept corresponding to the number of days in column G (if days is still column G), and place into your y=mx+b formula.

With the task broken down into parts like that, which part do you get stuck on?

[NG7267]

Thank you MrShorty for your feedback. I find it difficult to write formula for step 2 and 3 both. I could not understand
the usage of LINEST() function from the help file. Appreciate if you can help me step by step as explained above. I can
understand the concept but I do not know how I can apply the formula. Thank you so much for taking your valuable time.

[MrShorty]

It might help to describe what you tried to do to use those formulas, so we could see where you went wrong.

One thing I notice is that your rates in column C are text strings that Excel does not want to see as numbers. I got a couple of them to convert to numbers by removing the space between the rate and the per cent symbol, so that Excel could see these as numbers. That may have been a big part of the problem with the LINEST() function.

In my file, I added a LINEST() function to D2:E2 to illustrate the use of LINEST() (after converting to numbers). I also added a lookup function to J2 to illustrate the use of the VLOOKUP() function. A similar function would go in I2 to return the intercept. Once slope and intercept are returned for a point, a simple =j2*g2+i2 in H2 should perform the linear interpolation.

[NG7267]

Hi MrShorty I have copied down formulas for columns D,E,J & K. Please check whether this is in order and also how can I
calculate yellow marked cells from available rates? I just filled yellow marked cells since rates are not available for
these tenors. File is attached.

[MrShorty]

If I follow what you have done, those yellow cells were previously "blank". As I suggested in step 1, I would have completely removed them from the lookup table and not tried to calculate them. If you feel that you "must" fill those blank rows in in the starting table, I would create a separate lookup table that has the blanks removed. Then, the formulas for filling in those cells become very similar to the formulas for filling in the final table.

[NG7267]

Thanks for the help