# Circular reference calculation breaks down above certain input value

#### reggie1611

##### New Member
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.

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

#### jasonb75

##### Well-known Member
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

##### New Member
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

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

#### reggie1611

##### New Member

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

##### New Member
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

##### New Member
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!

Replies
5
Views
151
Replies
1
Views
357
Replies
5
Views
114
Replies
10
Views
2K
Replies
10
Views
863

1,147,734
Messages
5,742,863
Members
423,760
Latest member
photogfrog

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back