-1

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

enter image description here

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.

TourEiffel
  • 4,034
  • 2
  • 16
  • 45
  • 1
    Look at this good example https://mathformeremortals.wordpress.com/2014/04/27/cubic-and-bicubic-interpolation-excel-functions/. – pizzettix Jan 27 '21 at 17:51
  • @pizzettix but in your example he still use loop, i was wondering if an other way without loop was existing – TourEiffel Jan 27 '21 at 17:58
  • 1
    simple linear you can use the worksheet formulas SLOPE and INTERCEPT to create a linear formula that will do what you want. – Scott Craner Jan 27 '21 at 18:01
  • @Dorian: look at this https://stackoverflow.com/questions/41920162/calculate-matrix-in-cubic-spline-interpolation. Maybe you can express the solution as a matrix multiplication (it depends on the cubic interpolation type)? In excel and vba you can caluculate with matrix too. – pizzettix Jan 27 '21 at 18:02

1 Answers1

1

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/

Charles Williams
  • 23,121
  • 5
  • 38
  • 38