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

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