IRR, Goal Seek and Solver

[Kiser]

1) I have a series of cash flows.
2) The last entry of the series is a FINAL number.
3) I calculate the IRR with the IRR function.
4) Then, I use Goal Seek or Solver to determine what the
FINAL needs to be to get a 25% IRR.

Problem....every time I change the cash flows, I need to
rerun Goal Seek to determine a new value for FINAL to get
the 25% IRR. The problem comes because I have many
different series of numbers and running goal seek for each
of them takes a long time.

Question....is there a method to determine what the FINAL
number needs to be to make the IRR equal to 25% without
having to run goal seek each time?

Thx

 

[A.W.J. Ales]

Kiser,

A "direct" calculation (without iteration) of the FINAL should be possible
with the NPV function at 25% of all the payments, excluding the FINAL,
multiplied by 1,25 ^ N, whereby N = number of cashflows (including the final
payment).

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

 

[A.W.J. Ales]

Replace payment with cashflows and change the sign of the NPV function.

Example :

Cashflows : -1000 (t=0 in cell C1) ; + 200 (t=1; in cell C2) ;
+ 300 (t=2; in cell C3) ; +400 (t=3; in cell C4) ;
+ 500 (t=4; in cell C5)

IRR(C1:C5) = 12,8257...%

NPV(25%,C1:C4) * 1,25^5 = -1082.03

Replacing + 500 by +1082.03 and then IRR(C1:C5) gives 25%

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

 

[Guest]

Brilliant!

I wish I had thought of that!

Thanks for your help.

John

 

[A.W.J. Ales]

YW John. Thanks for the feedback. It's always nice to hear that a
suggestion was usefull.

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *