Linear interpolation

[Paul_Hossler]

I think you were wiping out your data

I added the Exit For to get out of your j loop once the gap is calculated and now everything looks reasonable

(although my math is a little rusty)

Option Explicit

Function Interpolate(oldRates As Variant) As Variant
Dim i As Long, j As Long, Maturity As Integer
' (8,1) in old rates data array highlighted in yellow, so maturity became 10
Maturity = oldRates(oldRates.Rows.Count, 1)
Dim Mat() As Double
ReDim Mat(1 To Maturity, 1 To 2)   ' 1 to 10, 1 to 2:  the new array set up.
'Fills in the years
For i = 1 To Maturity
    Mat(i, 1) = i
    Mat(i, 2) = 0#
Next i
'copies over known Rates.
For i = 1 To oldRates.Rows.Count    ' 1 to 8
   j = oldRates(i, 1).Value
  Mat(j, 2) = oldRates(i, 2)
Next i
For i = 2 To Maturity
    'find if missing
    If Mat(i, 2) = 0# Then
       For j = i + 1 To Maturity     'find the next Filled Rate to interpolate with
          If Mat(j, 2) <> 0 Then       'if statement
             Mat(i, 2) = Mat(i - 1, 2) + (Mat(j, 2) - Mat(i - 1, 2)) / (Mat(j, 1) - Mat(i - 1, 1))
             Exit For    '   <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
          End If
      Next j
   End If
Next i
Interpolate = Mat
End Function