Linear interpolation. retrieving the wrong data.

[Paul_Hossler]

"'copies over known Rates using the step" was wrong

Option Explicit

Function Interpolate(oldRates As Variant, freq As Integer) As Variant
    Dim i As Long, j As Long, Maturity As Integer
    
    Maturity = oldRates(oldRates.Rows.Count, 1)
    
    
    Dim Mat() As Double
    ReDim Mat(1 To Maturity * freq + 1, 1 To 2)
    
    'Fills in the years
    For i = 2 To Maturity * freq + 1
        Mat(i, 1) = Mat(i - 1, 1) + 1 / freq
        Mat(i, 2) = 0
    Next i
    
'-------------------------------------------------------------------------------
    'copies over known Rates using the step
    For i = 1 To Maturity
        Mat(i * freq + 1, 2) = oldRates(i, 2)
    Next i
'-------------------------------------------------------------------------------
     
     'find if missing rates
     Mat(1, 2) = 0
     For i = 2 To Maturity * freq + 1
        If Mat(i, 2) = 0 Then
                                     
            For j = i + 1 To Maturity * freq + 1  '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)) * (Mat(i, 1) - Mat(i - 1, 1))
                    Exit For
               End If
            Next j
        End If
    Next i
    
    Interpolate = Mat
               
End Function