Apr 05 2021 07:31 PM
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?
Apr 06 2021 12:30 PM - edited Apr 08 2021 12:11 PM
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.
Apr 08 2021 10:19 AM - edited Apr 08 2021 12:20 PM
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):