Question

A firm has a WACC of 10% and is deciding between two mutually exclusive projects. Project A has an initial investment of $63. The additional cash flows for project A are: year 1 - $17.year 2 - $35 year 3 - $67. Project B has an initial investment of $73.The cash flows for project Bare: year 1 =$51. year 2-$41. year 3 - $26. Calculate the payback and NPV for each project. (Show all answers to 2 decimals) Payback for A: Payback for B: NPV for A: NPV for B:

Answer 1

Solution :

The payback period of Project A = 2.16 Years

The payback period of Project B = 1.54 Years

The NPV of Project A = $ 31.72

​​​​​​​The NPV of Project B = $ 26.78

Please find the attached screenshot of the excel sheet containing the detailed calculation for the solution.

19.11.2019 Microsoft Excel FILE НOМE INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW A C D Е G Н К Statement showing calculation of Payback period of the project A Year Project Cash Flows 1 Sl.No. Particulars Cumulative cash flows 2 1 Cash Inflow Year 1 2 Cash Inflow Year 2 1 $ 2$ 17.00 $ 17.00 35.00 $ 67.00 $ 52.00 4. 3 $ 3 Cash Inflow Year 3 119.00 Payback Period: The Payback period lies between the year 2 & 3 as the Initial Investment of $ 63 is earned during the year 3 7 8 Hence Payback period 2 years+[ ($ 63- $52)/ $67 2+[11/35]2 + 0.1642 2.1642 years; =2.16 years 10 11 (when rounded off to two decimal places) 12 13 14 Statement showing calculation of Payback period of the project B Year Project Cash Flows 15 Cumulative cash flows Sl.No. Particulars 16 1 Cash Inflow Year 1 2 Cash Inflow Year 2 1 $ 2 $ 51.00 $ 17 51,00 41.00 $ 92.00 18 3 $ 3 Cash Inflow Year 3 26.00 S 19 118.00 20 Payback Period: The Payback period lies between the year 1 & 2 as the Initial Investment of $ 73 is earned during the year 2 21 22 Hence Payback period 1 year+[ ($ 73 - $51)/ $41] 23 = 1 + [ 22 /411 1+ 0.5366 24 =1.5366 years; =1.54 years 25 (when rounded off to two decimal places) 26 27 28 29 n ON

19,11.2019- Microsoft Excel FILE DATA НOМE INSERT PAGE LAYOUT FORMULAS REVIEW VIEW M А В C D Е G Н K L N P 1 Statement showing calculation of Net Present value of Project A Statement showing calculation of Net Present value of Project B Year Cash flows Cash flows (1) PV Factor@ Discounted Cash Flows Sl.No. Particulars Year PV Factor@ Discounted Cash Sl.No. Particulars (2) 10% (3) 1.0000 $ (2) 10% (1) Flows (3) (4)-(1)* (3) (4)-(1)* (3) 3 1 Cash outflow Year 0 $ 1 Cash outflow Year 0 $ 1.0000 S 73.00 73.0000 4 63.00 0 63.0000 0 $ 5 $ Total of Cash Outflows (a) Total of Cash Outflows (a) 2. 63.0000 2 73.0000 0.9091 $ $ 4 Cash Inflow Year 2 $ 5 Cash Inflow Year 3 $ 4 Cash Inflow Year 2 $ 5 Cash Inflow Year 3 3 Cash Inflow Year 1 17.00 3 Cash Inflow Year 1 0.9091 $ 0,8264 $ 46.3636 6 15.4545 51.00 1 0.8264 $ 41.00 7 35.00 2. 28.9256 2 33.8843 0.7513 $ 31 0.7513 $ 26.00 8 67.00 50.3381 33 19.5342 Total of Cash Discounted Inflows (b) Net Present Value (b) - (a) 8 Net Present Value ( Rounded off to two decimal places) Total of Cash Discounted Inflows (b) Net Present Value (b) - (a) 8 Net Present Value (Rounded off to two decimal places) 99.7821 9 6 94.7183 10 $ 31.7183 26.7821 7 7 11 12 $ 31.72 26.78 13 14 15 16