Question

You are considering the projects below and can take only one. Your cost of capital is 11%. Complete the steps below using cell references to given data or previous calculations. In some cases, a simple cell reference is all you need. To copy/paste a formula across a row or down a column, an absolute cell reference or a mixed cell reference may be preferred. If a specific Excel function is to be used, the directions will specify the use of that function. Do not type in numerical data into a cell or function. Instead, make a reference to the cell in which the data is found. Make your computations only in the blue cells highlighted below. In all cases, unless otherwise directed, use the earliest appearance of the data in your formulas, usually the Given Data section. a. What is the NPV of each project at your cost of capital? b. What is the IRR of each project? c. At what cost of capital are you indifferent between the two projects? d. What should you do? Project A Project B $ -100 $ 50 $ 40 $ 30 $ Cost of capital 11% a. What is the NPV of each project at your cost of capital? NPV project A NPV project B b. What is the IRR of each project? IRR project A IRR project B c. At what cost of capital are you indifferent between the two projects? Projects A-B IRR of difference d. What should you do? You should invest in project: Requirements 1 In cell D18, by using the NPV function and cell references, calculate the NPV of project A (1 pt.). 2 In cell D19, by using the NPV function and cell references, calculate the NPV of project B (1 pt.). 3 In cell D23, by using cell references, calculate the IRR of project A (1 pt.). 4 In cell D24, by using cell references, calculate the IRR of project B (1 pt.). 5 In cell range D29:H29, by using cell references, calculate the difference between the cash flows of project A and project B (1 pt.). 6 In cell D31, by using cell references, calculate the IRR of the difference in cash flows (1 pt.). 7 In cell E35, type A or B depending on whether you should take project A or project B (1 pt.).

Answer 1

А в D E F G H I Project A Project B 0 ($100) $100) 1 $25 | $50 2 $30 $40 3 $40 | $30 4 $50 $20 Cost of Capital 11% NPV project A NPV project B $9.06 $12.62 IRR project A IRR project B 14.70% 17.80% Projects A-B $0 ($25) ($10) $10 $30 IRR of difference 5.57% You should invest in project: Project B (Higher NPV Project, would add higher value to firm)

-100 Project A Project B -100 50 40 30 Cost of Capital 0.11 NPV project A NPV project B =NPV(D14,E11:H11)+D11 =NPV(D14, E12:H12)+D12 IRR project A IRR project B =IRR(D11:H11) =IRR(D12:H12) Projects A-B =D11-D12 =E11-E12 =F11-F12 =G11-G12 =H11-H12 IRR of difference =IRR(D29:H29) Projecte You should invest in project: Project B (Higher NPV Project,