Calculating IRR, NPV, and a Data Table

%3CLINGO-SUB%20id%3D%22lingo-sub-2255610%22%20slang%3D%22en-US%22%3ECalculating%20IRR%2C%20NPV%2C%20and%20a%20Data%20Table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2255610%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20having%20a%20time%20with%20this%20one%2C%20I%20am%20trying%20to%20calculate%3A%3C%2FP%3E%3CP%3E1)%20IRR%3C%2FP%3E%3CP%3E2)%20NPV%3C%2FP%3E%3CP%3E3)%20A%20Data%20Table%20for%20those%20two%20items.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20no%20idea%20if%20I%20calculated%20the%20IRR%20(C18)%20correctly%2C%20the%26nbsp%3B%20NPV%20(D18)%20is%20'correct'%20but%20I%20could%20not%20get%20the%20NPV%20function%20to%20function%20right.%20Lastly%20I%20have%20to%20set%20a%20data%20table%20up%20in%20cells%20C19%3AC27%20%26amp%3B%20D19%3AD27%20but%20cannot%20for%20the%20life%20of%20me%20figure%20out%20the%20What-IF%20Data%20Table%20setting.%20At%20first%20it%20was%20saying%20input%20errors%2C%20in%20that%20instance%20I%20think%20the%20problem%20was%20I%20was%20putting%20in%20too%20many%20cells%20into%20the%20Data%20Table%20box.%20Second%20it%20was%20saying%20the%20copy%20paste%20cells%20were%20the%20wrong%20size%2C%20I'm%20not%20sure%20where%20to%20say%20I%20went%20wrong%20there.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20anyone%20help%20me%20with%20this%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2255610%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2257323%22%20slang%3D%22en-US%22%3ERe%3A%20Calculating%20IRR%2C%20NPV%2C%20and%20a%20Data%20Table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2257323%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F773784%22%20target%3D%22_blank%22%3E%40kerry590%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20use%20Excel%20NPV%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DNPV(Cost_of_Capital%2CD13%3AI13)%3CFONT%20color%3D%22%23FF0000%22%3E%2BC13%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23000000%22%3ENote%20that%20we%20cannot%20write%20simply%20NPV(...%2C%3CFONT%20color%3D%22%23FF0000%22%3EC13%3C%2FFONT%3E%3AI13)%20because%20Excel%20NPV%20discounts%20the%20first%20value%2C%20as%20if%20the%20%22present%20value%22%20time%20is%20the%20__previous__%20time%20period.%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23000000%22%3EYour%20use%20of%20Excel%20IRR%20is%20complete%20incorrect%20because%20your%20terms%20are%20the%20discounted%20values.%26nbsp%3B%20Excel%20IRR%20expects%20the%20undiscounted%20values%3B%20it%20discounts%20the%20values%20internally.%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20ostensibly%2C%20you%20should%20write%20%3DIRR(C13%3AI13).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20even%20though%20Excel%20IRR%20returns%20what%20appears%20to%20be%20a%20%22reasonable%22%20rate%20(12.597468098811%25)%20which%20does%20reduce%20the%20NPV%20to%20zero%20(or%20relatively%20close%2C%20namely%20about%201.89E-09)%2C%20that%20is%20tautological.%26nbsp%3B%20I'm%20not%20sure%20it%20is%20a%20__correct__%20IRR%20for%20financial%20analysis%20purposes.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20need%20to%20consult%20sources.%26nbsp%3B%20I%20do%20not%20have%20experience%20with%20calculating%20the%20IRR%20of%20business%20operations.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOff-hand%2C%20I%20believe%20the%20last%20cash%20flow%20(year%206)%20should%20include%20any%20amount%20that%20remains%20from%20the%20initial%20investment%20(%245%2C550%2C000).%26nbsp%3B%20Your%20cash%20flow%20model%20suggests%20that%20the%20entire%20initial%20investment%20was%20expended%20over%20the%20course%20of%206%20years.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWas%20it%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20particular%2C%20I'm%20not%20sure%20if%20the%20salvage%20value%20of%20the%20initial%20machinery%20should%20be%20included%20in%20the%20year%206%20cash%20flow.%26nbsp%3B%20And%20I'm%20not%20sure%20if%20and%20how%20any%20remaining%20NWC%20(in%20particular%2C%20the%20initial%20NWC)%20should%20be%20accounted%20for%20in%20the%20year%206%20cash%20flow.%26nbsp%3B%20(Being%20careful%20not%20to%20double-account%20for%20the%20net%20revenue%20in%20that%20year.)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESomeone%20with%20more%20experience%20with%20financial%20analysis%20of%20business%20operations%20would%20need%20to%20comment.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E-----%3C%2FP%3E%3CP%3EI%20will%20not%20comment%20on%20your%20%22data%20table%22%20question.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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 Replies

@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.

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

 

image.png