Circular reference calculation breaks down above certain input value

[reggie1611]

Hi all. Hope everyone is keeping safe from Covid-19.

I am having trouble with a calculation involving circular referencing. The calculation is designed to yield an output based on a value in the input cell. However, the calculation works fine as long as the value in the input cell is below a specific number (seemingly random), but breaks down if a higher value is entered. Has anyone encountered this before and have a fix? More context/explanation below and attached...

--- EX_IRR_CapitalEquipmentCost_V4.xlsx

In this example project model, I am solving for the 'Cost of Capital Equipment' which uses a series of calculations that involve circular referencing. The input for the model is the desired IRR and the output is the 'Cost of Capital Equipment.' Essentially, I am trying to determine how much I can afford to pay for the capital equipment for a given IRR I want from the project.

The circular referencing occurs because the desired output, 'Cost of Capital Equipment,' is also a variable used to determine 'Initial Investment,' which in turn drives the 'Net Profit' required to satisfy the IRR input.

The model works fine up to a certain IRR (~25%; I have no idea why this is the 'upper limit'; the real limit should be ~82%, which is when 'Cost of Capital Equipment' would be $0). However, it starts to break down if an IRR of 26% or greater is entered. Note: Iterative calculation must be enabled.

Question: How can I fix the model so that it will work even if I input an IRR greater than 25%?

Bonus Question: What is the significance of 25%? Very curious why the model breaks down above this number.

 

[jasonb75]

No idea without being able to test the formulas and the link that you provided doesn't allow viewing of the formulas or downloading of the file.

 

[i_nth]

Circular references are inherently bad. They have many problems, including instability, non-convergence, etc.
In your case, the circularity can be removed via a bit of algebra. That is, replace the formula in C15 with:
=(C14+C9-C10)/(1+((1+C7)^C8-1))
You should check that this works as expected.

 

[i_nth]

Actually, that can be simplified to:
=(C14+C9-C10)/(1+C7)^C8

 

[reggie1611]

Actually, that can be simplified to:
=(C14+C9-C10)/(1+C7)^C8

Hey, thanks so much for going through the trouble to do the algebra. It works great!

 

[reggie1611]

No idea without being able to test the formulas and the link that you provided doesn't allow viewing of the formulas or downloading of the file.

Hi there, hmm strange, it works fine. Maybe you wanna give it a try again?

 

[reggie1611]

Was wondering if anyone can think of a solution without using an equation that solves for equipment cost? Still puzzled as to why the model starts to break down when an IRR input is greater than 25%. Thanks!