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
.