Problem in working with Data tables in Excel 2016


New Member
Apr 1, 2019
Hello, i am facing a peculiar problem. I was doing a sensitivity analysis through data table and putting the IRR value as a reference for the data table to calculate values. For this I am using the cell reference of the cell in which I have calculated the IRR value of the project. And it is in a separate worksheet, i.e., I am trying to calculate the data table in one worksheet with reference to some other worksheet of the same workbook. And everytime it's showing the same IRR vales across the data table.
Then , when I tried to detect the formula dependants by using Trace dependence option it's showing nothing saying "The Trace Dependent has found no formula refered to in the active cell! Then, again I tried to bring the data table to the same page in which the IRR has been calculated and checked with the Trace dependents and again the same results shown. I am baffled, don't know what to do.
I was using the IRR formula as a reference in data table. Actually what I wanted to create was a data table that displays the IRR of the project at various debt interest and debt funding values.
I am taking the IRR value from the 'Return Analysis' worksheet and using it in the Data Table of 'Sensitivity Analysis' worksheet.
I've calculated IRR in the 'Return Analysis' worksheet only by using the IRR formula.
and in the 2nd table I have taken different Equity funding values in the rows and Cost of Equity % in the column for calculating the NPV variance in the data table as a part of the Sensitivity Analysis.
Can anyone please help me sort it out?

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics