what if analysis data table issue

tanishqm 

Your mistake is putting constants in D6:I6 instead of the intended formula, namely (in D6):

=C6*(1+$B$2)

Thus, the NPV and IRR will change as we change the growth rate in B2.  See the image below and the attached Excel file.  I highlight significant changes in yellow and green.

I confess that Excel Data Tables are something of a mystery to me.  I prefer to set up the formulas myself so that the calculations are clear to the reader (moi !).

It might help if you explain the purpose of the Data Table; or at the very least, label the percentages in column L.

I assume that the purpose is to explore the effect of changing the growth rate in B2 on the NPV and IRR calculations.  That is consistent with your Data Table formula, to wit:  {=TABLE(,B2)}.

(PS....  In contrast, HansV assumes that the purpose is to explore the effect of changing the discount rate in B3.  But then his Data Table formula is {=TABLE(,B3)}, which differs from yours.)

BTW, M6 and N6 could simply reference the formulas in B8 and B9; that is, =B8 and =B9 respectively.  It was not necessary to duplicate their formulas.

It is obvious that changing the growth rate should change the cash flows in D6:I6, which in turn should change the NPV in B8, given the constant discount rate of 15% in B3.

And with your formula B9, the IRR changes as well, because you have a constant CF0 in B6.

We can demonstrate those changes by manually changing the growth rate in B2 and observing the effect on the formulas in B8 and B9.  Effectively, that is what your Data Table does.

tanishqm 

The IRR is fixed, there is no point in creating a data table for it.

For NPV, see the attached version.

tanishqm 

What results would you expect?