8 116, May. 20, 2005
y 0.5 9, 644 e = 10, 000 1 9644 y = ln = 3.6(%) 0.5 10000 9 116, May. 20, 2005
y 0.5 9, 644 e = 10, 000 1 9644 y = ln = 3.6(%) 0.5 10000 1 FV r T = ln T PV 10 116, May. 20, 2005
Public Function ZeroRate(T As Double, FV As Double, PV As Double) As Double ZeroRate = Log(FV / PV) / T ( T ( )) T ( ) 2 1 ( ) ( ( )( )) exp r T t = exp r T t exp f t, T, T T T 2 1 1 2 2 1 rt ( T 2 2 t) rt ( T 1 1 t) f(, t T1, T2) = T2 T1 rt T2 2 rt T1 1 f(0, T1, T2) = T2 T1 11 116, May. 20, 2005
Public Function ForwardRate(T1 As Double, R1 As Double, T2 As Double, R2 As Double) ForwardRate = (R2 * T2 - R1 * T1) / (T2 - T1) 10,500 400 400 400 400 10, 400 0.5 y y 1.5 y 2 y 2.5 y = e + e + e + e + e 12 116, May. 20, 2005
Public Function BondPresentValue(Time() As Double, CF() As Double, Rate() As Double) As Double Dim i As Integer Dim TempPV As Double If IsArray(Time) Then If Not (UBound(Time) = UBound(CF) And UBound(Time) = UBound(Rate) _ And UBound(CF) = UBound(Rate)) Then 'dimension MsgBox ("Invalid Input Data") BondPresentValue = xlerrref Else TempPV = 0 For i = 1 To UBound(Time) TempPV = TempPV + CF(i) * Exp(-Rate(i) * Time(i)) Next i BondPresentValue = TempPV 13 116, May. 20, 2005
Public Function CalcYTM(Time() As Double, CF() As Double, PV As Double) 'Time: 'CF: 'PV: Dim TempYTM As Double, TempValue1 As Double, TempValue2 As Double, Dim TempRate() As Double, TempCF() As Double, Diff As Double, Epsilon As Double Dim NCount As Integer, i As Integer NCount = UBound(Time) ReDim TempRate(1 To NCount) As Double ReDim TempCF(1 To NCount) As Double Epsilon = 0.00001 TempYTM = 0 ' ' Do For i = 1 To NCount TempRate(i) = TempYTM TempCF(i) = -1 * Time(i) * CF(i) ' BondPresentValue ' Next i TempValue1 = BondPresentValue(Time, CF, TempRate) - PV ' If (Abs(TempValue1) < Epsilon) Then ' CalcYTM = TempYTM Exit Do Else TempValue2 = BondPresentValue(Time, TempCF, TempRate) TempYTM = TempYTM - TempValue1 / TempValue2 ' 'iteration Loop 14 116, May. 20, 2005
Time 0.25 0.5 0.75 1 1.5 2 2.5 3 5 YTM 3.46% 3.54% 3.59% 3.61% 3.69% 3.76% 3.74% 3.75% 3.94% 3.54% 0.03445 0.25 3.54% 0.25r0.5 10, 000 10, 000 e = + 10, 000 1+ e 4 4 15 116, May. 20, 2005
Option Explicit Private numytmbuckets As Integer Private numbuckets As Integer Private Buckets() As Double Private YTMs() As Double Private ZeroRates() As Double Private DFs() As Double Private Sub Class_Initialize() Dim currentcell As Range Dim n As Integer Set currentcell = Range("B1") n = 0 Do Until (IsEmpty(currentcell)) Set currentcell = currentcell.offset(0, 1) n = n + 1 Loop numytmbuckets = n End Sub 'YTM Bucket Public Function Get_NumYTMBuckets() As Integer Get_NumYTMBuckets = numytmbuckets Public Function Zero(t As Double) As Double 'zero ratelinear interpolation Dim i As Integer If t > Buckets(numBuckets) Then t = Buckets(numBuckets) If Not t <= 0 Then i = 1 Do While (Buckets(i) < t) And (i < numbuckets) i = i + 1 Loop If i = 1 Then Zero = ZeroRates(1) Else Zero = ZeroRates(i - 1) + (ZeroRates(i) - ZeroRates(i - 1)) / (Buckets(i) - Buckets(i - 1)) * (t - Buckets(i - 1)) Else Zero = 0 16 116, May. 20, 2005
Public Function Discount(t As Double) As Double Dim t_zero As Double Dim tempt As Double tempt = t t_zero = Zero(tempT) Discount = Exp(-t_zero * t) Public Sub YTM_Load() Dim i As Integer, j As Integer, idx As Integer Dim tmpprice As Double Dim temp As Variant Dim nrows As Integer, ncols As Integer Dim currentcell As Range Set currentcell = Range("A1") ReDim temp(1 To 2, 1 To numytmbuckets) For i = 1 To numytmbuckets temp(1, i) = currentcell.offset(0, i) ' time bucket temp(2, i) = currentcell.offset(1, i) ' YTM Next i ReDim Buckets(1 To 40) ReDim YTMs(1 To 40) ReDim DFs(1 To 40) ReDim ZeroRates(1 To 40) numbuckets = 40 For i = 1 To 40 Buckets(i) = 0.25 * i Next i idx = 1: i = 1 Do While idx <= 40 And i <= UBound(temp, 2) If temp(1, i) - Buckets(idx) <= 0.125 Then YTMs(idx) = temp(2, i) Else Do Until temp(1, i) - Buckets(idx) < 0.125 idx = idx + 1 YTMs(idx) = (temp(2, i) * (Buckets(idx) - temp(1, i - 1)) / (temp(1, i) _ - temp(1, i-1)) + temp(2, i-1) * (temp(1, i)-buckets(idx)) _ - / (temp(1, i) - temp(1, i - 1))) Loop i = i + 1 Loop ' Bootstrap DFs(1) = 1 / (1 + YTMs(1) / 4): ZeroRates(1) = -Log(DFs(1)) / Buckets(1) For i = 2 To numbuckets tmpprice = 0 For j = 1 To i - 1 tmpprice = tmpprice + YTMs(i) / 4 * DFs(j) Next j DFs(i) = (1 - tmpprice) / (1 + YTMs(i) / 4) ZeroRates(i) = -Log(DFs(i)) / Buckets(i) Next i End Sub 17 116, May. 20, 2005
18 116, May. 20, 2005