I appreciate your sheet. It seems the perfect worksheet for my case.
However, unfortunately, I have to work per the comments here as this is a school project.
At least, there has been some progress in my file, but it still doesn't generate the consecutive missing rates...
Can you take a look at it again?
Option Explicit
Function Interpolate(oldRates As Variant) As Variant
Dim i As Integer, j As Integer, oldCount As Integer, 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 Variant
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 Maturity ' 1 to 10: i
For j = 1 To oldRates.Rows.Count ' 1 to 8 : J
If oldRates(j, 1) = Mat(i, 1) Then
Mat(i, 2) = oldRates(j, 2)
End If
Next j
Next i
For i = 1 To Maturity
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
Next j
Else
' Mat(i,2) = linear interpolation code here
Mat(i, 2) = Mat(i - 1, 2) + (Mat(j, 2) - Mat(i - 1, 2)) / (Mat(i + 1, 1) - Mat(i - 1, 1)) * (Mat(i, 1) - Mat(i - 1, 1))
End If
Next i
Interpolate = Mat
End Function