Calculating IRR, NPV, and a Data Table

[kerry590]

I am having a time with this one, I am trying to calculate:

1) IRR

2) NPV

3) A Data Table for those two items.

I have no idea if I calculated the IRR (C18) correctly, the  NPV (D18) is 'correct' but I could not get the NPV function to function right. Lastly 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. At first it was saying input errors, in that instance I think the problem was I was putting in too many cells into the Data Table box. Second it was saying the copy paste cells were the wrong size, I'm not sure where to say I went wrong there. 

Can anyone help me with this? 

[#2]

@kerry590 

To use Excel NPV:

=NPV(Cost_of_Capital,D13:I13)+C13

Note that we cannot write simply NPV(...,C13:I13) because Excel NPV discounts the first value, as if the "present value" time is the __previous__ time period.

Your use of Excel IRR is completely incorrect because your terms are the discounted values.  Excel IRR expects the undiscounted values; it discounts the values internally.

So ostensibly, you should write =IRR(C13:I13).

But even though Excel IRR returns what appears to be a "reasonable" rate (12.597468098811%) which does reduce the NPV to zero (or relatively close, namely about 1.89E-09), that is tautological.  I'm not sure it is a __correct__ IRR for financial analysis purposes.

I would need to consult sources.  I do not have experience with calculating the IRR of business operations.

Off-hand, I believe the last cash flow (year 6) should include any amount that remains from the initial investment ($5,550,000).  Your cash flow model suggests that the entire initial investment was expended over the course of 6 years.

Was it?

In particular, I thing (but I'm not sure) that the salvage value of the initial machinery should be included in the year 6 cash flow.  And I think (but I'm not sure) that any remaining NWC (in particular, the initial NWC) should be accounted for in the year 6 cash flow.  (Being careful not to double-account for the net revenue in that year.)

Someone with more experience with financial analysis of business operations would need to comment.

-----

I will not comment on your "data table" question.

[#3]

@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):