While I was waiting I found the answer:

=FORECAST(100,TRANSPOSE(INDEX(RateRange,MATCH(INDE X(RateRange,MATCH(100,PriceRange,-1)),RateRange,-1),0):INDEX(RateRange,MATCH(INDEX(RateRange,MATCH( 100,PriceRange,-1)),RateRange,-1)+1,0)),TRANSPOSE(INDEX(PriceRange,MATCH(INDEX(Ra teRange,MATCH(100,PriceRange,-1)),RateRange,-1),0):INDEX(PriceRange,MATCH(INDEX(RateRange,MATCH (100,PriceRange,-1)),RateRange,-1)+1,0)))



"Don" wrote in message
...
Hi,

This UDF looks down the range of prices in PriceRange until it finds
MyPrice (100 in this case) and then interpolates the correct yield in
RateRange.

Function ParYield(MyPrice, PriceRange As Range, RateRange As Range)
For i = 1 To PriceRange.Rows.Count - 1
If MyPrice <= PriceRange.Cells(i) And MyPrice = PriceRange.Cells(i + 1)
Then
ParYield = (RateRange.Cells(i + 1) + (RateRange.Cells(i) -
RateRange.Cells(i + 1)) * (MyPrice - PriceRange.Cells(i + 1)) /
(PriceRange.Cells(i) - PriceRange.Cells(i + 1)))
Exit Function
End If
Next i
End Function

Example:

RateRange PriceRange
5.094 102.175
5.055 101.7138
5.016 101.2528
4.984 100.7545
4.955 100.2406
4.927 99.7221
4.898 99.2096
4.88 98.6397
4.867 98.0446
4.854 97.4511

For MyPrice = 100, ParYield = 4.942

Would someone show me the equivalent worksheet formula for this UDF?

Thanks
Don