Calculating IRR, NPV, and a Data Table

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.