FIND ZERO convergence issues with subscripts

[sibanez]

Hi again,

I am developing a model which calculated the IRR (Internal Rate of Return) of specific assets as an intermediate variable. I am sending attached a simplified piece of the model as an example.

PROBLEM DEFINITION
- There are 5 different asset classes (subscripted): W, SPV, SH, STE and GCC
- Each class has an associated initial investment, annual (constant) cash flow and life. This data is included in the 'Test - Convergence v0.xls' spreadsheet attached
- IMPORTANT: The model calculates the IRR for investments done in years 1998 to 2011 and which have their own specific useful life
- The IRR is calculated as the rate of return 'r' which makes the NPV equal to zero
- The NPV is calculated as the NPV of an annuity according to the formula below:

NPV = - Investment + Annual Cash Flow x (1-(1+r)^(-Asset Life))/r

- Then, the NPV is divided by the Investment in order to scale it to the order of 1

RESULTS
- When I simulate the 1st 4 subscripts (W, SPV, SH and STE) everything works well
- When I add the 'GCC' subscript (you can replicate this just by adding 'GCC' at the subscript control) it does not converge in years 2001, 2003, 2004, 2006, 2007, 2010 and 2011
- This makes sense as the expected cashflows are negative so that there is no IRR solution for these years
- I think I can easily filter the 'non-converging' IRRs just by including upper limits. Nevertheless, the big problem is that when 'GCC' is not converging the rest of the IRRs change and get crazy values (see 'W' for Example)

QUESTIONS
- Is there a way to detect non-converging cases and assign a predefined value to the IRR variable (kind of the IFERROR function in Excel?)
- Is there a way to avoid the rest of the subscripts becoming affected by the non-converging one?

Thank you very much.

Santiago

[LAUJJL]

Hi
The find zero has a drawback. You cannot trace the intermediate calculations made while converging.
If you cannot find a solution using the find zero, you can build your own convergence algorithm using arrays to simulate loops: see two past posts about this subject
http://www.ventanasystems.co.uk/forum/v ... =+for+loop
or
http://www.ventanasystems.co.uk/forum/v ... f=2&t=5468

Regards.
JJ

[tomfid]

Is there a reason for not using the INTERNAL RATE OF RETURN function here?

[Administrator]

It cannot be used in this case, we discussed it in a different thread,
http://www.ventanasystems.co.uk/forum/v ... 193#p21193

[tomfid]

It seems like there ought to be an analytic solution, but I don't know it off the top of my head.

You can separate the processing of the FIND ZERO subscripts by writing the equation to solve one element at a time (as in IRRGCC), but that would be a pain.

A better approach, I think, is to detect the conditions that cause nonconvergenze, and zero out the NPV Calc vector, so that the FIND ZERO algorithm doesn't search for an impossible solution. Then you just have to disregard the calculated IRR at those points.

Test - Convergence v1.mdl

(5.33 KiB) Downloaded 336 times