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 he
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 a

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