Exponetial Interpolation VBA Code

[AF]

Hello folks,

does any one have a VBA Code for Exponential Interpolation in Excel? I
have the formula but since I am a "new guy" at VBA programming I am
having a hard time trying to implement it.

Formula:

ExpInterp = { (1+Pi)^Di * [(1+ Pi+1)^(Di+1)/(1+Pi)^Di]^((Dint-Di)/(Di
+1-Di)) }^(1/Dint)-1

Where: Pi and Pi+1 are points in the Y vector (e.g. points in a term
structure)
Di and Di+1 are points in the X vector (e.g. business days
--numbers or dates)
Dint the date of which you want the interpolation (it has
to be a number between Di and Di+1


Thanks in advance.

[d***@gmail.com]

Post by AF
Hello folks,
does any one have a VBA Code for Exponential Interpolation in Excel? I
have the formula but since I am a "new guy" at VBA programming I am
having a hard time trying to implement it.
ExpInterp = { (1+Pi)^Di * [(1+ Pi+1)^(Di+1)/(1+Pi)^Di]^((Dint-Di)/(Di
+1-Di)) }^(1/Dint)-1
Where: Pi and Pi+1 are points in the Y vector (e.g. points in a term
structure)
Di and Di+1 are points in the X vector (e.g. business days
--numbers or dates)
Dint the date of which you want the interpolation (it has
to be a number between Di and Di+1
Thanks in advance.

This will give you the formula as stated:

Function ExpInterp(D As Variant, P As Variant, V As Double) As Variant
Dim i As Long

If TypeName(D) = "range" Then D = D.Value
If TypeName(D) = "range" Then P = P.Value

i = 1
Do While V > D(i, 1)
i = i + 1
Loop
i = i - 1

ExpInterp = ((1 + P(i, 1)) ^ D(i, 1) * ((1 + P(i + 1, 1)) ^ (D(i + 1,
1)) / _
(1 + P(i, 1)) ^ D(i, 1)) ^ ((V - D(i, 1)) / (D(i + 1, 1) - D(i, 1))))
^ (1 / V) - 1

End Function


Open the Visual Basic Editor (alt-F11)
Insert a new code module (insert-module)
Paste in the code above

Thats it.

To use:
=ExpInterp(Drange, Prange, V)

where V is the value you want to interpolate on.


Are you sure about the formula though? It doesn't seem to give
accurate results.

[r***@gmail.com]

Folks, apparently I have the same problem. I have created a Function
to do Exponential Interpolation although is not really working. The
thing is one of the arguments "Date_Conv" isn't working properly it
should be different when inputing different numbers (e.g. 252,360 or
365) but once you input any number it does the calc. As far as I know
the coding is right perhaps the formula isn't. Can anyone PLEASE
PLEASE help me?
Thanks a lot.

Function Interp_Exp(x_values As Object, y_values As Object, X As
Variant, _
Date_Conv As Integer) As Double
Dim n As Long, ind As Long, i As Long
n = x_values.Rows.Count
If X < x_values(1) Then
Interp_Exp = y_values(1)

Interp_Exp = y_values(n)
Else
For i = 1 To n
If x_values(i) <= X Then
ind = ind + 1
Else
ind = ind
End If
Next
Dim X1 As Variant, X2 As Variant, Y1 As Double, Y2 As Double
X1 = x_values(ind)
X2 = x_values(ind + 1)
Y1 = y_values(ind)
Y2 = y_values(ind + 1)
Interp_Exp = (((((1 + Y1 / 100) ^ (X1 / Date_Conv)) * _
((1 + Y2 / 100) ^ (X2 / Date_Conv) / (1 + Y1 / 100) ^ (X1 /
Date_Conv)) _
^ ((X - X1) / (X2 - X1))) ^ (Date_Conv / X)) - 1) * 100
End If
End Function

[David B]

Hi rombonacho,
Actually, your coding is horrible, at least as posted. Why declare x_
(and y_) values as objects?!? Mistake. What use is the line below,
prepended with "*1*=>>", when you immediately override it with another
assignment? And what are you "thinking" with line *2*=>>? That's a
horrible loop! You (as well as dougaj4) also make (IMO) goofy implicit
assumptions about the nature of the objects/arrays being passed in, as
well as some inefficient "dims".

Do you still want some help, or have you already figured out things (to
your satisfaction) by now?

A kinder, gentler, central-coast version of my old buddy Harlan... <vbg>
Dave Braden

Post by r***@gmail.com
Folks, apparently I have the same problem. I have created a Function
to do Exponential Interpolation although is not really working. The
thing is one of the arguments "Date_Conv" isn't working properly it
should be different when inputing different numbers (e.g. 252,360 or
365) but once you input any number it does the calc. As far as I know
the coding is right perhaps the formula isn't. Can anyone PLEASE
PLEASE help me?
Thanks a lot.
Function Interp_Exp(x_values As Object, y_values As Object, X As
Variant, _
Date_Conv As Integer) As Double
Dim n As Long, ind As Long, i As Long
n = x_values.Rows.Count
If X < x_values(1) Then

*1*=>>> Interp_Exp = y_values(1)
*1*=>>>
*1*=>>> Interp_Exp = y_values(n)

Post by r***@gmail.com
Else
For i = 1 To n
If x_values(i) <= X Then
ind = ind + 1
Else

*2*=>>> ind = ind

Post by r***@gmail.com
End If
Next
Dim X1 As Variant, X2 As Variant, Y1 As Double, Y2 As Double
X1 = x_values(ind)
X2 = x_values(ind + 1)
Y1 = y_values(ind)
Y2 = y_values(ind + 1)
Interp_Exp = (((((1 + Y1 / 100) ^ (X1 / Date_Conv)) * _
((1 + Y2 / 100) ^ (X2 / Date_Conv) / (1 + Y1 / 100) ^ (X1 /
Date_Conv)) _
^ ((X - X1) / (X2 - X1))) ^ (Date_Conv / X)) - 1) * 100
End If
End Function

[d***@gmail.com]

Post by David B
You (as well as dougaj4) also make (IMO) goofy implicit
assumptions about the nature of the objects/arrays being passed in, as
well as some inefficient "dims".

David B, feel free to tell which implicit assumptions are goofy, and
why, and show us how to make the "dims" more efficient.


More to the point, the formula given in the OP gives the wrong
answers.

The code below will I believe give correct exponential interpolation:

Function ExpInterp2(D As Variant, P As Variant, V As Double) As
Variant
Dim i As Long, LogP As Double, LogPdiff As Double, Dprev As Double
Dim DDiff As Double, Logslope As Double, LogInterp As Double

If TypeName(D) = "range" Then D = D.Value
If TypeName(D) = "range" Then P = P.Value

i = 1
Do While V > D(i, 1)
i = i + 1
Loop
i = i - 1

LogP = Log(P(i, 1))
LogPdiff = Log(P(i + 1)) - LogP
Dprev = D(i, 1)
DDiff = D(i + 1, 1) - Dprev
Logslope = LogPdiff / DDiff

LogInterp = LogP + (V - Dprev) * Logslope

ExpInterp2 = Exp(LogInterp)

End Function


Goofy implicit assumptions are that D and P are single column ranges
containing values, and V is a value or a cell address containing a
value.

[r***@gmail.com]

Post by d***@gmail.com

Post by David B
You (as well as dougaj4) also make (IMO) goofy implicit
assumptions about the nature of the objects/arrays being passed in, as
well as some inefficient "dims".

David B, feel free to tell which implicit assumptions are goofy, and
why, and show us how to make the "dims" more efficient.
More to the point, the formula given in the OP gives the wrong
answers.
Function ExpInterp2(D As Variant, P As Variant, V As Double) As
Variant
Dim i As Long, LogP As Double, LogPdiff As Double, Dprev As Double
Dim DDiff As Double, Logslope As Double, LogInterp As Double
If TypeName(D) = "range" Then D = D.Value
If TypeName(D) = "range" Then P = P.Value
i = 1
Do While V > D(i, 1)
i = i + 1
Loop
i = i - 1
LogP = Log(P(i, 1))
LogPdiff = Log(P(i + 1)) - LogP
Dprev = D(i, 1)
DDiff = D(i + 1, 1) - Dprev
Logslope = LogPdiff / DDiff
LogInterp = LogP + (V - Dprev) * Logslope
ExpInterp2 = Exp(LogInterp)
End Function
Goofy implicit assumptions are that D and P are single column ranges
containing values, and V is a value or a cell address containing a
value.

Thanks for the insights people.
Apparently the function is working great.
Thanks again.
P.S. I'll try to be less "goofy" next time..but I am a beginner to
this progamming language.

[David B]

Hi,
Just spotted this thread while repairing my computers. Do you still want
help, or is all OK?

Dave Braden

Post by AF
Hello folks,
does any one have a VBA Code for Exponential Interpolation in Excel? I
have the formula but since I am a "new guy" at VBA programming I am
having a hard time trying to implement it.
ExpInterp = { (1+Pi)^Di * [(1+ Pi+1)^(Di+1)/(1+Pi)^Di]^((Dint-Di)/(Di
+1-Di)) }^(1/Dint)-1
Where: Pi and Pi+1 are points in the Y vector (e.g. points in a term
structure)
Di and Di+1 are points in the X vector (e.g. business days
--numbers or dates)
Dint the date of which you want the interpolation (it has
to be a number between Di and Di+1
Thanks in advance.