Circular reference calculation breaks down above certain input value

reggie1611

New Member
Joined
Mar 20, 2020
Messages
4
Office Version
  1. 2011
Platform
  1. MacOS
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

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,951
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Mar 28, 2019
Messages
35
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Mar 28, 2019
Messages
35
Office Version
  1. 365
Platform
  1. Windows
Actually, that can be simplified to:
=(C14+C9-C10)/(1+C7)^C8
 

reggie1611

New Member
Joined
Mar 20, 2020
Messages
4
Office Version
  1. 2011
Platform
  1. MacOS

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
Joined
Mar 20, 2020
Messages
4
Office Version
  1. 2011
Platform
  1. MacOS
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
Joined
Mar 20, 2020
Messages
4
Office Version
  1. 2011
Platform
  1. MacOS
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!
 

Forum statistics

Threads
1,144,528
Messages
5,724,854
Members
422,585
Latest member
k3n

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

Which adblocker are you using?

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
Top