Linear interpolation. retrieving the wrong data.

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.
Attachment 20507
So, I changed the code as below but it doesn't still correct the rate for the year 1.

Code:

---

'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?:bug: any help is greatly appreciated.

Regards,
Demian

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

Code:

---

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

---

Great! Big Thanks, Paul!