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

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

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

ADVERTISEMENT

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
165
Replies
1
Views
396
Replies
5
Views
115
Replies
10
Views
2K
Replies
10
Views
871

Threads
1,148,397
Messages
5,746,464
Members
424,021
Latest member
naimathulla

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.
Allow Ads at MrExcel

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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