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