Calculating IRR, NPV, and a Data Table

kerry590 

Sorry for the late response.  For posterity....

Previously, I wrote (excerpts):

D18: =NPV(Cost_of_Capital,D13:I13)+C13

C18: =IRR(C13:I13)

I'm not sure it is a __correct__ IRR for financial analysis purposes. I would need to consult sources.

I did my "research" (it's been 4+ decades since I worked such analysis), and I believe your cash flow model is essentially correct.  In particular, the model does indeed incorporate the salvage value and the release of NWC for Year 6 in I11 and I12.

So ostensibly, the NPV and IRR formulas above are correct for your purpose.

I quibble with your calculation of taxes in row 9 and in K15.  But that's a detail.  If you are curious, ask about it in a response.

However, some change is necessary in row 9 in order for the what-if analysis below to work.  For now, let's make the simplifying assumption that the incremental tax is based on the marginal tax rate calculated in K15 (Marginal_Tax). So, the formula in D9 should be =D8*Marginal_Tax, copied across.

Also, we need to correct the formulas in E19:E27.  Currently, they all reference D19.  Instead, they should reference D19, D20, etc.  So, copy E19, and paste-function into E20:E27.

-----

Re: ``I have to set a data table up in cells C19:C27 & D19:D27 but cannot for the life of me figure out the What-IF Data Table setting.``

I could explain how to use the Data Table feature.  But IMHO, the what-if changes are too complicated for a Data Table set-up.  Changing units sold (B19:B27) has a complex effect on CFFA in row 13, if only because of the impact on taxes in row 9 (especially if we fix row 9 to calculate taxes correctly).

Instead, I would suggest the following manual procedure, which could be automated with a VBA macro.  The following assumes that you enter the IRR and NPV formulas above into C18 and D18.

1. For each of B19:B27, change K4 (Units_Sold) to =B19, =B20, etc.

2. After each change, copy C18:D18, and paste-value into the corresponding row of columns C:D; that is, C19:D19, C20:D20, etc.

The result should be (after restoring K4 to 70,000):