PDA

View Full Version : [SOLVED] Linear interpolation. retrieving the wrong data.



demian
09-28-2017, 11:34 AM
Hi VBA experts!

I'm trying to build a linea interpolation model using VBA that accepts a matrix of data and a frequency to split years by: = interpolate(data array, freq)

The given data ranges are from year 1 to 30 and each year has its own rate. And, the missing rate needs to be calculated as the frequency changes.
So to speak, if the freq is 2, each year needs be split by 2 pieces and to be filled with the calculated rate.

My model looks little rusty, but working, except that the rate I retrieve from the old array into the new array for a year such as year 1,2,3.

For example, for given data, the yield for the year is 1.24% but in the frequency 2,3,4, table, yield for year 1 is still 1.46%. I want to get 1.24% for year in all the frequency tables.
20507
So, I changed the code as below but it doesn't still correct the rate for the year 1.



'copies over known rates using the step.
For i=1 to Maturity * freq+1 step freq
mat(i*freq,2) = oldrates(i,2)
next i

Why this is not working?:bug: any help is greatly appreciated.

Regards,
Demian

Paul_Hossler
09-28-2017, 12:01 PM
"'copies over known Rates using the step" was wrong



Option Explicit

Function Interpolate(oldRates As Variant, freq As Integer) As Variant
Dim i As Long, j As Long, Maturity As Integer

Maturity = oldRates(oldRates.Rows.Count, 1)


Dim Mat() As Double
ReDim Mat(1 To Maturity * freq + 1, 1 To 2)

'Fills in the years
For i = 2 To Maturity * freq + 1
Mat(i, 1) = Mat(i - 1, 1) + 1 / freq
Mat(i, 2) = 0
Next i

'-------------------------------------------------------------------------------
'copies over known Rates using the step
For i = 1 To Maturity
Mat(i * freq + 1, 2) = oldRates(i, 2)
Next i
'-------------------------------------------------------------------------------

'find if missing rates
Mat(1, 2) = 0
For i = 2 To Maturity * freq + 1
If Mat(i, 2) = 0 Then

For j = i + 1 To Maturity * freq + 1 'find the next Filled Rate to interpolate with
If Mat(j, 2) <> 0 Then 'if statement

Mat(i, 2) = Mat(i - 1, 2) + (Mat(j, 2) - Mat(i - 1, 2)) / (Mat(j, 1) - Mat(i - 1, 1)) * (Mat(i, 1) - Mat(i - 1, 1))
Exit For
End If
Next j
End If
Next i

Interpolate = Mat

End Function

demian
09-29-2017, 09:51 AM
Great! Big Thanks, Paul!