I was wondering if it was possible to interpolate without using loop in order to earn processing time.
Note : Interpolation goal is to calculate point which does not exist on my curve. For Example the rate for 9 october 2021
Curve Example
Actually I am using a For loop in order to browse my curve to calculate the point. Does a Smartest way exist ?
Function DCF(Periode As Double) As Double
Dim x As Integer
For x = 1 To 21
Date1 = ThisWorkbook.Worksheets("Courbes").Range("PeriodeCourbe").Offset(x).Value
Date2 = ThisWorkbook.Worksheets("Courbes").Range("PeriodeCourbe").Offset(x + 1).Value
TauxMid1 = ThisWorkbook.Worksheets("Courbes").Range("Mid").Offset(x).Value
tauxMid2 = ThisWorkbook.Worksheets("Courbes").Range("Mid").Offset(x + 1).Value
If Periode >= Date1 And Periode < Date2 Then DCF = 1 / (Date2 - Date1) * ((Periode - Date1) * tauxMid2 + (Date2 - Periode) * TauxMid1)
Next
End Function
I am using spline cubic interpolation but a simple example with linear interpolation will help me to build my own function.
Yes you can do it more efficiently without a loop using approximate MATCH/VLOOKUP.
see my post here for code and a comparison of the 2 methods.
https://fastexcel.wordpress.com/2011/06/06/writing-efficient-vba-udfs-part-2/