Exponetial Interpolation VBA Code

[Database Administrator]

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.

[Database Administrator]

On Jul 10, 6:02 am, AF wrote:
> 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.


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.

[Database Administrator]

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

[Database Administrator]

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

Dave Braden

AF wrote:
> 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.
>

[MichaelK]

hi folks,
can anyone give a final and right version about this exponential interpolation?
(in VBA)
I really need it, thanks