Home > Forum Home > Valuing Investments and Businesses > Gilt Curve > Cubic Spline Function in VBA Share

Cubic Spline Function in VBA

Excel Help for Cubic Spline Function In Vba in Valuing Investments and Businesses


Forum TopicLogin

Cubic Spline Function In Vba

Rate this:
(4.1/5 from 39 votes)
HappyYou 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:
 ABCDE
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

spline = y
endnow:
End Function
 Excel Business Forums Administrator
 Posted by on
 
View Full Post

Find relevant Excel templates and add-ins for Cubic Spline Function in VBA in the