Internal Rate of Return Question

M

matador04

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
 
Quote from matador04:

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
More...

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
 
You must log in or register to reply here.
Back
Top