IRR calculation with a Terminal Value

[jkaemmerer]

Is there a way to work around the inherent circular reference when using the IRR function on a set of cash flows that ultimately ends up in a terminal value calculation? The terminal value is calculated by taking the cash flow from the final year and dividing it by the discount rate less the terminal growth rate. Therefore since the IRR function includes the undiscounted terminal value in its calculation, I believe there is a circular reference as a result of it trying to derive a figure that is used within its calculation.

As an example, if I have undiscounted cash flows as follows:

Yr 1 =	(2,164.03)
Yr 2 =	(5,313.67)
Yr 3 =	(8,450.55)
Yr 4 =	(5,599.44)
Yr 5 =	7,102.65
Yr 6 =	7,583.27
Yr 7 =	7,569.36
Yr 8 =	7,555.03
Yr 9 =	9,147.27
Yr 10 =	9,132.07
TV* =	76,100.58

<tbody>
</tbody>

*TV = ((9,132.07/(15%-3%))

Then when I run an IRR formula on them I get 31.6% (I do recognize that IRR function is seeing the TV as a YR 11 cash flow instead of a YR 10 cash flow as would be appropriate, but I was hoping it wouldn't be too material). Then when I run an NPV formula on the undiscounted cash flows and use Goal Seek to find the Discount Rate that makes the NPV = 0, I get 28.0% (which I believe to be correct).

So assuming I'm not mistaken about the correct IRR, is there anything I can do to work around the IRR formula issues and not have to run Goal Seek all the time? Thanks for the feedback.

 

[Uttershambles]

I have exactly the same issue - the IRR function, when including a TV, is returning an incorrect % value
Like you I would rather avoid including a goal seek macro - and I confess i don't understand why the TV (which is a representation of DCF in perpetuity) would throw the excel function off - it's basically just a large positive cashflow at the end of the analysis period

Any ideas out there - I really don't want to try and go through MS