Linear interpolation. retrieving the wrong data.

[demian]

Hi VBA experts!

I'm trying to build a linea interpolation model using VBA that accepts a matrix of data and a frequency to split years by: = interpolate(data array, freq)

The given data ranges are from year 1 to 30 and each year has its own rate. And, the missing rate needs to be calculated as the frequency changes.
So to speak, if the freq is 2, each year needs be split by 2 pieces and to be filled with the calculated rate.

My model looks little rusty, but working, except that the rate I retrieve from the old array into the new array for a year such as year 1,2,3.

For example, for given data, the yield for the year is 1.24% but in the frequency 2,3,4, table, yield for year 1 is still 1.46%. I want to get 1.24% for year in all the frequency tables.
Capture.jpg
So, I changed the code as below but it doesn't still correct the rate for the year 1.

'copies over known rates using the step.
For i=1 to Maturity * freq+1 step freq
   mat(i*freq,2) = oldrates(i,2)
next i

Why this is not working? any help is greatly appreciated.

Regards,
Demian

[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

[demian]

Great! Big Thanks, Paul!