I need help finding out the NPV and Profitability Index. I would prefer if you could show me using excel functions or lay out the equation in a way that I can input to excel. References to cells would be helpful, as they are required, I may not just type in the numbers.

I know that NPV = Present Value of Future Cash Flows - Initial Investment. What I am confused about, is what is the amount or cell of the inital investment? Is it 1,600? (cell D13)? I have already done the calculations for this part in the PV of Incremental Cash Flows: =H30/(1+$C$7)^H7. So I should be able to use =ROUND((SUM(H32:K32)-D13),0). I'm getting $91, but it's marked wrong.

For the Profitability Index, I am putting =SUM(H32:K32)/D13, but I feel that D13, or the cost of equipment before is not what I should be using.

NPV X Fax ESUM(H32:K32)/D13 AA B C D E G H J K = 4 Discount rate: Tax Rate: 8 Equipment Replacement Analysis enter revenues & inflows as positive numbers, expenses & outflows as negative numbers round only where instructed to do so investment years of equipment usage t= = = = 9.4% Part 1: Before replacement | 0 | 1 | 2 | 3 | 20.0% Sales Revenue 2,500 2,500 2,500 After Before Expenses other than Depreciation (1.125) (1,125) (1,125) 2.500 2.500 Depreciation (200 (200) (200) 500 1,125 EBIT 1,175 1,175 1,175 2,000 I 1.600 EBIT After Taxes** 940 940 940 Add back: Depreciation 200 200 200 below 200 Free Cash Flow (Before) 1,140 1,140 1,140 11 12 Sales Revenue (constant) Expenses other than Depreciation Cost of equipment Expected life of equipment Depreciation (straight line) 2,500 (1,125) (200) 1,175 940 200 1,140 14 16 Part 2: After replacement Disposal of Old Equipment: see handout Old equipment was depreciated on a straight-line basis (2,000) 510 58 22 Age of old equipment: Accum. Deprec. On old equip: 1800 Net Book Value of old equip: 800 Proceeds of sale: 510 Gain/ (Loss) (290) Tax on Gain (Loss)* 58 round to s *Tax OUTFLOW if Gain *Tax INFLOW if loss ** EBIT * (1-tax rate) *** difference between FCF before new equip, and FCF after Net cost of new Equipment Sale of old equipment Tax on Gain / Loss: Sales Revenue Expenses other than Depreciation Depreciation EBIT EBIT After Taxes Add back: Depreciation Free Cash Flow (After) 2,500 (500) (333) 1,667 1,333 333 1,667 2,500 (500) (333) 1.667 1,333 333 1,667 2,500 (500) (333) 1.667 1,333 333 1,667 2,500 (500) (333) 1.667 1,333 333 1,667 (1,432) Part 3: Incrementa/*** cash flows (1,432) 527 527 527 527 after-before PV of Incremental Cash Flows: Cumulative Cash for Payback: Cumulative PV for Disc. Payback: 481 527 481 4 40 1,053 921 402 1,580 1,324 368 2,107 1,691 NPV: IRR: Payback Disc Payback Profitability Index: $91.00