please post excel formula:

Fisheries is considering two mutually exclusive investments. The projects' expected net cash flows are follows Gardial as Expected Net Cash Flows Project A ($375) ($300) ($200) ($100) $600 $600 $926 Project B ($575) $190 $190 $190 $190 $190 $190 $0 Time C 1 4 5 ($200) 7 a. If each project's cost of capital is 12%, which project should be selected? If the cost of capital is 18%, what project is the proper choice? 12% cost of capital @ 18% cost of capital Use Excel's NPV function as explained in this WACC 12% WACC 18% chapter's Tool Kit. Note that the range does not include the costs, which are added separately. NPVA NPV A NPV NPVB b. Construct NPV profiles for Projects A and B. Before we can graph the NPV profiles for these projects, we must create a data table of project NPVS relative to differing costs of capital Project A $0.00 Project B $0.00 NPV WACC-12 % $1.20 0% 2% $1.00 ProjectA 4% 6% PrajectB $0.80 8% 10 % S0.60 12% 14 % 16% $0.40 18% 20% $0.20 22% 24% $0.00 26% 5 % 25 % 30 % 10% 15% 20% 35% 28% 30% What is each project's IRR? c. We find the internal rate of return with Excel's IRR function: Note in the graph above that the X-axis intercepts are equal to the two projects' IRRS. IRR A= IRR- d. What is the crossover rate, and what is its significance? Cash flow Time differential 1 Crossover rate 3 The crossover rate represents the cost of capital at which the NPV of both projects is (the exact value) 5 6 7 What is each project's MIRR at a cost of capital of 12%? At r 18 % ? Hint: note that B is a 6-year project e. Cost of Capital 12% 18% MIRRA MIRRA MIRR MIRR a= f. What is the regular payback period for these two projects? Prolect A Time period 1 2 3 4 6 7 5 Cash flow $375 $300 $200 $100 $600 $600 $926 $200 Cumulative cash flow Intermediate cal cul ati on for payback Payback using intermediate calculations Payback using PERCENTRANK NOT ok because cash flows follow non-normal pattern. NA. Prolect B Time period 1 2 3 4 5 6 7 $190 Cash flow $575 $190 $190 $190 $190 $190 $0 Cumulative cash flow Intermediate calculation for payback Payback using intermediate cal cul ati ons Payback using PERCENTRANK or PERCENTRANK.INC Ok because cash flows follow normal pattern. Set significance to 6 g.At a cost of capital of 12% , what is the discounted payback period for these two projects? WACC 12% Prolect A Time period 0 1 2 3 4 5 7 Cash flow $375 $300 $200 $100 $600 $600 $926 $200 Disc. cash flow Disc. cum. cash flow Intermediate cal cul ati on for payback Payback using intermediate calculations Payback using PERCENTRANK NOT ok because cash flows follow non-normal pattern. NA Prolect B Time period 5 2 3 4 6 7 $190 Cash flow $575 $190 $190 $190 $190 $190 $0 Disc, cash flow Disc. cum. cash flow Intermediate calculation for payback Payback using intermediate cal cul ati ons Discounted Payback using PERCENTRANK| Ok because cash flows follow normal pattern. Set significance to 6 or PERCENTRANK.INC h. What is the profitability index for each project if the cost of capital is 12 %? PV of future cash flows for A: Pl of A PV of future cash flows for B Pl of B