Rounding issue with Goal Seek for IRR

[Curt]

I am using goal seek to solve for a desired IRR ("To Value") by modifying the
initial cash outflow ("By Changing Cell") that is followed by a fixed series
of cash inflows. The values I input for IRR are whole % (ex. 15.0%, 30.0%,
etc) however, the resulting IRR that is calculated through goal seek is never
a round number. In most cases, it will round to my desired result but
occasionally it will leave me off by 0.1% (rounded). Anybody know why the
result doesn't equal the exact number i enter in the "To Value" field?

Thanks
Curt

 

[vandenberg p]

Hello:

I just saw your post. I am not sure I understand your problem exactly (so
the second comment may not reflect my understanding of what you are
trying to do)

But let me first say that Goal Seel uses a guessing algorithim to find the answer.
It stops looking when the accuracy of the answers meets some level, which is
predefined by Excel. The more robust Solver allows you to set this level.
So given the complexity of solving poynomials it is not surprising that you are
getting this result.

The second comment is why are you doing this? You can solve for the outflow by
using the NPV function. Just take your inflows and solve for the equivalent amount
in PV terms =NPV(IRR rate you want,the fixed cash inflows (probably a vector))

The result of this calculation is the amount you could pay for the investment
(your outflow in period 0) that would earn the IRR you wanted.

Pieter Vandenberg


: I am using goal seek to solve for a desired IRR ("To Value") by modifying the
: initial cash outflow ("By Changing Cell") that is followed by a fixed series
: of cash inflows. The values I input for IRR are whole % (ex. 15.0%, 30.0%,
: etc) however, the resulting IRR that is calculated through goal seek is never
: a round number. In most cases, it will round to my desired result but
: occasionally it will leave me off by 0.1% (rounded). Anybody know why the
: result doesn't equal the exact number i enter in the "To Value" field?

: Thanks
: Curt

 

[Curt]

Pieter,

Thanks for the suggestion on the NPV formula...I guess I was making it more
complicated than it need to be.

Curt