A A Wrap Text General Paste BIU - a-A- Merge & Center - $ . % +0.00 600 7.0 Clipboard Font Conditional Format as Cell Formatting Table Styles Styles Alignment Number U4 K P 3 5 B D E H M N 1 This Excel exercise is designed to find the present value of future cash flows. Assume there are two projects X & Y with cash flows at the end of the next three years as follows. 0 Q 2 Assume that the required rate of retum on projects is equal to 10%. Required Retum 1096 Today Year 1 Year 2 Year 3 4 Project X -100 10 60 70 Project Y -100 80 40 20 6 7 In the space below calculate the Net Present Values of projects X & Y by using the present value formula of Excel (PV(rate.nper, put[fVD. 8 Note: because these are uneven cash flow streams the pet -0. We can then solve for the PV of each cash flow, where each cash flow is a FV. 9 In cell C14. you can type-c13". In cell D14 you will type "-PV(SJS2, D12, 0, -D13)". You can then copy cell D14 to cells E14 and F14. 10 Next you simply need to sum the cells C14, C14, E14, and F14, to calculate the NPV for Project X. 11 12 Project X 2 3 13 Cash Flows -100 10 60 70 14 PV(CF) NPV 16 17 Now we will find the NPV of Project X using a different approach in cell C21 type " 20". In cell D21 type "-D20/(1+$J$27"D19". Copy cell D21 to F21 and F21. 18 Sum row 21 to calculate NPV. Project X 2 3 20 Cash Flows -100 60 PV(CF) NPV 23 24 Now copy cells B12 to F1S into the space below and change the information to calculate the NPV of project Y. Check your answers with the book Example 2 of Chapter 10 0 1 15 19 O 1 10 70 21 22 Merge & Center $ . % 0.00 .00.0 Clipboard Font Conditional Format as Cel Formatting Table - Styles Styles Alignment Number 44 X fx H 0 P A A B D E F G 15 NPV L M N 16 17 Now we will find the NPV of Project X using a different approach. In cell C21 type "C20". In cell D21 type "-D20/(1+$J$2) D19". Copy cell D21 to E21 and F21. 18 Sum row 21 to calculate NPV. 19 Project X 0 1 2 3 20 Cash Flows -100 10 60 70 21 PV(CF) 22 NPV 23 24 Now copy cells B12 to F1s into the space below and change the information to calculate the NPV of project Y. Check your answers with the book Example 2 of Chapter 10 25 26 Complete the information in the Table below by changing the required rate of return, r, in cell J2 and inputing the NPVs for projects X & Y using your analysis above. 27 NPV(X) NPVY) 28 096 29 596 30 10% 31 1596 32 2096 33 2596 34 Create a NPV profile in the space below, by inserting a line graph from using data in the above Table F 37 38 39 an