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

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,240
Members
448,555
Latest member
RobertJones1986

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
Back
Top