Question

solve using excel

Net Present Value and Other Capital Budgeting Measures 6 Consider two mutually exclusive projects with these cash flows: Year Project A -$28,300 13,700 11,600 8,850 4,750 Project B $28,300 3,950 9,450 14,500 16,100 The required rate of return on both projects is 10% a. Which project should be accepted based on the payback period? b. Which project should be accepted based on the PI? c. Which project should be accepted based on the IRR? d. Which project should be accepted based on the NPV?!

Answer 1

Please refer to below spreadsheet for calculation and answer. Cell reference also provided.

B Year C D Project A CF Cum. CF ($28,300.00)| ($28,300.00) $13,700.00 ($14,600.00) $11,600.00 $3,000.00 $8,850.00 $5,850.00 $4,750.00 $10,600.00 E F Project B CF Cum. CF ($28,300.00)| ($28,300.00) $3,950.00 ($24,350.00) $9,450.00 ($14,900.00) $14,500.00 ($400.00) $16,100.00 $15,700.00 Required return 10% Payback period Profitability Index (PI) 11 12 a. 13 b. 14 c. 15 d. 16 17 Project-A 2.34 1.13 16.88% $3,634.77 Project-B 3.02 1.18 16.45% $4,991.41 Accept Project-A Project-B Project-A Project-B IRR NPV

Cell reference -

в с Year CF -28300 13700 11600 8850 4750 DE Project A Project B Cum. CF CE Cum. CF =C3 1-28300 =E3 =D3+C4 3950 =F3+E4 D4+C5 9450 =F4+E5 =D5+C6 14500 =F5+E6 =D6+C7 16100 =F6+E7 Required return 0.1 | 11 12 a. 13 b. 14 C. 15 d. 16 Project-A Payback period =B5+(-D5/D6-D5)) Profitability Index (PI) ENPV(C9,C4:07)/-C3 IRR =IRR(C3:07) NPV =C3+NPV(C9,C4:07) Project-B =B6+(-F6/(F7-F6)) =NPV(C9,E4:E7)/-E3 =IRR(E3:E7) =E3+NPV(C9, E4:E7) Accept Project-A Project-B Project-A Project-B 17

Please note: Initial cash flow of Project-B should be negative -$28,300 otherwise we can not calculate the payback,irr,PI of project-B. Thus, i considered it as -$28,300 for solution. If it is not a typo error in question,please let me know.

Hope this will help, please do comment if you need any further explanation. Your feedback would be highly appreciated.