reggie1611
New Member
 Joined
 Mar 20, 2020
 Messages
 4
 Office Version

 2011
 Platform

 MacOS
Hi all. Hope everyone is keeping safe from Covid19.
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.
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.