Question

ONLY SHOW HOW TO DO IN EXCEL.

please no hard coded formulas

A. If the appropriate discount rate is 12%, rank the two projects.

B. Which project is preferred if you rank by IRR?

C. Calculate the crossover rate, namely, the discount rate r at which the NPVs of both projects are equal. D. Should you use NPV or IRR to choose between the projects? Give a brief description.

1 Section a. 2 Discount rate 12% 3 4 Year 5 6 72 83 9 4 105 11 Project A Project B -500 -500 180 200 175 250 160 170 100 25 100 30 12 13 NPV 14 IRR 15 16 17 18 19 20

Answer 1

SEE THE IMAGE. ANY DOUBTS, FEEL FREE TO ASK. THUMBS UP PLEASE

W ? v 00-16 ENG 07-02-2020 26 fix A1 2A 1 2 DISCOUNT RATE D E F G H I J formula Bar 12% 4 YEAR PROJECT A PROJECT B -500 180 175 160 INCREMENTAL CF (B-A) -500 200 250 170 100 100 30 -70 (=NPV(B2,B6:B10) +35] (=NPV(B2,C6:C10)+C5] 12 NPV 13 IRR 34.40 15.09% (=IRR(B5:B10)] 31.78 15.45% (=IRR(CS:C10)] 14 16 A AT RATE = 12% RANK PROJECT 18 1 NPV OF PROJECT A IS HIGHER ACCORDING TO IRR RANK 1 PROJECT B A PROJECT B HAS HIGHER IRR 2 FV NPV IRR

W ? v 00-16 ENG 07-02-2020 26 X A38 D E F G H K L 2A 15 16 A AT RATE = 12% RANK 1 PROJECT A 18 NPV OF PROJECT A IS HIGHER 2 ACCORDING TO IRR RANK 1 2 PROJECT B A PROJECT B HAS HIGHER IRR 26 C CROSSOVER RATE = 13.33% (=IRR(D5:010)] 27 NPV IS A BETTER METHOD AS IT SUGGESTS HOW MUCH WEALTH IS GENERATED FOR SHAREHOLDERS BY THE PROJECT AT 12%, NPV IS HIGHER FOR PROJECT A, SO IT IS TO BE SELECTED FV NPV IRRO