If I have various cashflows to analyze, can I use VBA to give me an internal rate without having to enter "Values(1) = 20000: Values(2) = 20000)"? Say I have 360 identical cashflows in one sheet, 60 cashflows in another, etc. Is there some kind of summation loop I can use?? This is what I have, but I'm trying to make it more efficient. Dim Guess, Fmt, RetRate, Msg Static Values(6) As Double Guess = 0.01 Fmt = "#0.00" Values(0) = -70000 ' Positive cash flows Values(1) = 20000: Values(2) = 20000 Values(3) = 20000: Values(4) = 20000 Values(5) = 20000: Values(6) = 20000 RetRate = IRR(Values(), Guess) * 100 Msg = "internal rate for six cash flows " Msg = Msg & Format(RetRate, Fmt) & " percent." MsgBox Msg
This syntax may be a bit rough: Dim count As Integer For count = 1 to 360 Values(count) = 20000 Next count You can replace the 360 and the 20000 with variables, to make the loop more general. I hope that's what you were asking. IRR is calculated using an iterative process, so there isn't a very elegant solution to the calculation itself, given you've already populated the Values array. Tim