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/du...-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---


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

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/universi...ancedbond5.asp

I'm getting some REALLY crazy results now!!

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"ryguy7272" wrote:

> 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/du...-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---
>
>
> --
> Ryan---
> If this information was helpful, please indicate this by clicking ''Yes''.

On 2/18/2010 4:01 PM, ryguy7272 wrote:
> 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/universi...ancedbond5.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/universi...ancedbond5.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

On 2/18/2010 4:01 PM, ryguy7272 wrote:
> 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

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.

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Dana DeLouis" wrote:

> On 2/18/2010 4:01 PM, ryguy7272 wrote:
> > 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
> .
>