Calculate Duration with UDF

[ryguy7272]

I just developed a simple duration function:
Function Dur(PDown, PUp, P, DelY)
Dur = (PDown - PUp) / (2 * P * DelY)
End Function

This is based on the sample here:
http://thismatter.com/money/bonds/duration-convexity.htm

My duration function is giving a result about 6.96% different than the Excel
built in duration function. I can’t figure out why mine is wrong. Can
someone please tell me.

My assumptions:
P-Down = $1,035.94
P-Up = $965.68
P = 1000
Delta-Y = 0.005

Excel Assumptions:
Bond Settlement = 01/02/2000
Bond Maturity = 01/02/2010
YTM = 7%

I'm just playing around with my own UDF, that's all this is...

Thanks,
Ryan---

 

[ryguy7272]

I think it was off just a bit because I was calculating duration using two
slightly different methods. I'm trying this UDF now:
Function Dur(CF, t, i, n, M, C)
't = time to maturity
'C = cash flow
'i = required yield
'n = number of cash flows
'M = maturity (par) value
'P = bond price

Dur = ((CF * t) / (1 + i) ^ t) + ((n * M) / (1 + i) ^ n) / C * ((1 - (1
/ ((1 + i) ^ n))) / i) + M / (1 + i) ^ n

End Function

It's based on the function here:
http://www.investopedia.com/university/advancedbond/advancedbond5.asp

I'm getting some REALLY crazy results now!!

 

[Dana DeLouis]

I think it was off just a bit because I was calculating duration using two
slightly different methods. I'm trying this UDF now:
Function Dur(CF, t, i, n, M, C)
't = time to maturity
'C = cash flow
'i = required yield
'n = number of cash flows
'M = maturity (par) value
'P = bond price

Dur = ((CF * t) / (1 + i) ^ t) + ((n * M) / (1 + i) ^ n) / C * ((1 - (1
/ ((1 + i) ^ n))) / i) + M / (1 + i) ^ n

End Function

It's based on the function here:
http://www.investopedia.com/university/advancedbond/advancedbond5.asp

I'm getting some REALLY crazy results now!!

Hi. I don't get the same "equation" as listed from your link...

http://www.investopedia.com/university/advancedbond/advancedbond5.asp

You didn't mention what your results were, and what you were expecting.

I'm just playing around with my own UDF, that's all this is...

Here is my interpretation of the equation of the link...
Things can be reduced (like removing the loop), but I'll just follow the
equation as best I can...


Sub TestIt()
'Excel 2007 Help on "Duration"
Debug.Print Duration(8, 2, 0.08, 0.09)
'Your Link Reference
Debug.Print Duration(5, 1, 0.05, 0.05)
End Sub

Function Duration(Years, NPer, CRate, Yield)

' = = = = = = = = = = = = = = = = = =
'// NPer is the number of periods per year
Const Face As Double = 1000
Dim BondPrice As Double
Dim Pc As Double
Dim Py As Double
Dim Num As Double 'Numerator
Dim t As Long

Dim Np As Double
Dim Ny As Double
Dim UL As Double 'Upper limit, ie..number of payments.
Dim C As Double 'Cash Flow
' = = = = = = = = = = = = = = = = = =

Pc = CRate / NPer
Py = Yield / NPer

UL = Years * NPer
C = Face * Pc / NPer

BondPrice = (Face * Pc) * ((1 - 1 / (1 + Py) ^ UL) / Py) + Face /
(1 + Py) ^ UL

For t = 1 To UL
Num = Num + (t * C) / (1 + Py) ^ t
Next t

Num = Num + (Face * UL / NPer) / (1 + Py) ^ UL

Duration = Num / BondPrice
'// If you wish to Round...
Duration = Round(Duration, 2)
End Function


Results are:

5.99377495554519
4.54595050416236

Or rounded...
5.99
4.55

Notice that Excel 2007 help on 'Duration gave a result to 6 decimal
places of:
5.993775
which matches with the above equation.


Again, we could clean this up a little, but I think you wanted to follow
along.

= = = = = = =
HTH :>)
Dana DeLouis

 

[Dana DeLouis]

Dur = ((CF * t) / (1 + i) ^ t) + ((n * M) / (1 + i) ^ n) / C * ((1 - (1
/ ((1 + i) ^ n))) / i) + M / (1 + i) ^ n

Hi. Here's my attempt at reducing the loop...

Sub TestIt()
'Excel 2007 Help on "Duration"
Debug.Print Duration(8, 2, 0.08, 0.09)

'Your Link Reference
Debug.Print Duration(5, 1, 0.05, 0.05)
End Sub


Function Duration(Years, n, c, y)
Dim P As Double
Dim k As Double
k = n + y
P = Years * n
Duration = (k/y - ((k + P * (c - y)) /(c * ((k / n) ^ P - 1) + y)))/ n
Duration = Round(Duration, 6)
End Function

Returns:

5.993775
4.545951

Which matches Excel's solution for the Function "Duration" and the
example from your link.

= = = = = = =
HTH :>)
Dana DeLouis

 

[ryguy7272]

Here's what I ended up with:
Function Dur(CF, t, i, n, M, C)
Dim sumall As Double
Dim j As Integer
Dim ttm As Integer

sumall = 0
For j = 1 To n
ttm = t - j + 1
sumall = sumall + ((CF * ttm) / (1 + i) ^ ttm)
Next j

Dur = (sumall + ((n * M) / (1 + i) ^ n)) / ((C * ((1 - (1 / ((1 + i) ^
n))) / i) + (M / (1 + i) ^ n)))
End Function

It matches Excel's built-in Duration function.