You can create a user defined function to calculate interpolated values of the gilt curve using a cubic spline. The following spreadsheet shows the calculation of a cubic spline interpolated value given known values for fixed periods.
Excel Spreadsheet:
A
B
C
D
E
1
Period:
Rate:
X Period:
Spline Value:
2
1
5.2
1.2
=spline($A$2:$A$4,$B$2:$B$4,D2)
3
2
4.3
4
3
6.5
The value of the calculated spline value for the interpolated period will be 4.87. Columns D and E can be copied down to create the entire curve for the X periods required in column D.
Of course the spline funtion first needs to added to a VBA project. The spline function is as follows.
VBA Code:
Function spline(periodcol As Range, ratecol As Range, x As Range) Dim period_count As Integer Dim rate_count As Integer period_count = periodcol.Rows.Count rate_count = ratecol.Rows.Count If period_count <> rate_count Then spline = "Error: Range count dos not match" GoTo endnow End If
ReDim xin(period_count) As Single ReDim yin(period_count) As Single Dim c As Integer For c = 1 To period_count xin(c) = periodcol(c) yin(c) = ratecol(c) Next c Dim n As Integer Dim i, k As Integer Dim p, qn, sig, un As Single ReDim u(period_count - 1) As Single ReDim yt(period_count) As Single n = period_count yt(1) = 0 u(1) = 0 For i = 2 To n - 1 sig = (xin(i) - xin(i - 1)) / (xin(i + 1) - xin(i - 1)) p = sig * yt(i - 1) + 2 yt(i) = (sig - 1) / p u(i) = (yin(i + 1) - yin(i)) / (xin(i + 1) - xin(i)) - (yin(i) - yin(i - 1)) / (xin(i) - xin(i - 1)) u(i) = (6 * u(i) / (xin(i + 1) - xin(i - 1)) - sig * u(i - 1)) / p
Next i
qn = 0 un = 0 yt(n) = (un - qn * u(n - 1)) / (qn * yt(n - 1) + 1) For k = n - 1 To 1 Step -1 yt(k) = yt(k) * yt(k + 1) + u(k) Next k Dim klo, khi As Integer Dim h, b, a As Single klo = 1 khi = n Do k = khi - klo If xin(k) > x Then khi = k Else klo = k End If k = khi - klo Loop While k > 1 h = xin(khi) - xin(klo) a = (xin(khi) - x) / h b = (x - xin(klo)) / h y = a * yin(klo) + b * yin(khi) + ((a ^ 3 - a) * yt(klo) + (b ^ 3 - b) * yt(khi)) * (h ^ 2) / 6
hi
I guess that this code assumes "zero" value for the second derivatives of the first and last points of the data which are needed as the boundary conditions. Am I right?
thanks
