Home > Forum Home > Valuing Investments and Businesses > Gilt Curve Share

Gilt Curve

Excel Help for Gilt Curve in Valuing Investments and Businesses


Forum TopicPost Reply Login

Gilt Curve

Rate this:
(3/5 from 1 vote)
ConfusedHi

Looking to find some cunning VBA code to build a Gilt curve using some sort of cubic spline.

Ben
 Posted by on
 
Replies - Displaying 1 to 3 of 3Order Replies By: Most Recent | Chronological | Highest Rated
Happy
Rate this:
(4.1/5 from 39 votes)
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:
 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
Confused
Rate this:
(3/5 from 1 vote)
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
 Posted by on
Grateful
Rate this:
(3.5/5 from 2 votes)
The VBA function does assume zero values for the first and last points of the boundaries . 
 Excel Business Forums Administrator
 Posted by on
 Displaying page 1 of 1 

Find relevant Excel templates and add-ins for Gilt Curve in the