0 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. Required Return- Assume that the required rate of return on projects is equal to 10%. 10% Today Year1 Year2 Year 3 100 100 10 Project X Project Y 7In the space below calculate the Net Present Values of projects X & Y by using the present value formula of Excel (PV(rate,nper,pmt,[fv]) 8 Note: because these are uneven cash flow streams the pmt- 0. We can then solve for the PV of each cash flow, where each cash flow is a FV. In cell Cl 4, you can type-cl 3". n cell D14 you will type "=PV(SJ$2, DI 2, 0,-D13)". You can then copy cell DI 4 to cells El 4 and F14 Next you simply need to sum the cells C 14, C 14, E 14, and F14, to calculate the NPV for Project X. 10 12 13 14 15 16 Project X Cash Flows PV(CF) NPV 100 10 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+SJS2 D19". Copy cell D21 to E21 and F21 18 Sum row 21 to calculate NPV 19 20 21 Project X Cash Flows PV(CF) 100 10 23 24 Now copy cells B12 to F15 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 27 31 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. NPVX) NPV 0% 5% 10% I 5% 35 37 41 42 43 Create a NPV profile in the space below, by inserting a line graph from using data in the above Table. 45 47 0 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. Required Return- Assume that the required rate of return on projects is equal to 10%. 10% Today Year1 Year2 Year 3 100 100 10 Project X Project Y 7In the space below calculate the Net Present Values of projects X & Y by using the present value formula of Excel (PV(rate,nper,pmt,[fv]) 8 Note: because these are uneven cash flow streams the pmt- 0. We can then solve for the PV of each cash flow, where each cash flow is a FV. In cell Cl 4, you can type-cl 3". n cell D14 you will type "=PV(SJ$2, DI 2, 0,-D13)". You can then copy cell DI 4 to cells El 4 and F14 Next you simply need to sum the cells C 14, C 14, E 14, and F14, to calculate the NPV for Project X. 10 12 13 14 15 16 Project X Cash Flows PV(CF) NPV 100 10 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+SJS2 D19". Copy cell D21 to E21 and F21 18 Sum row 21 to calculate NPV 19 20 21 Project X Cash Flows PV(CF) 100 10 23 24 Now copy cells B12 to F15 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 27 31 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. NPVX) NPV 0% 5% 10% I 5% 35 37 41 42 43 Create a NPV profile in the space below, by inserting a line graph from using data in the above Table. 45 47