Forum Discussion

Ian Goldsmith's avatar
Ian Goldsmith
Copper Contributor
Jun 07, 2017

IRR calculation and circular reference

I have a set of cash flow figures for which I am trying to calaculate the IRR. However the final or terminal value in the series is calculated use the IRR result and so of course gives a circular reference

 

I though this could be fixed by leaving the circular reference in place but using iterative calculation

 

Now though I get a #VALUE error in the main IRR calc

 

Does anyone have a fix for this at all ?

 

Many thanks

Ian

Excel on Mac
Formulas & Functions

1 Reply

Sort By
  • Fergus Allan's avatar
    Fergus Allan
    Former Employee
    Jun 07, 2017
    The IRR calculation can be unstable - it tries to iteratively zero in on the correct value, but depending on the flows, that's not always possible and the starting value can be important. In addition, there are certain flows the IRR calculation can't handle.

    An alternative is to create a formula that includes the interest rate. Apply the interest rate to the cash flow and summate the cash flows. Then goal seek the percentage such that the total of the cash flows is zero.

Resources