Question

1 Approved Answer

Posted on Sep 11, 2024

can someone please help me Question 1 You have the financial flows You should evaluate the value if these financial flows at the beginning of

can someone please help me image text in transcribed

Question 1 You have the financial flows You should evaluate the value if these financial flows at the beginning of the first year (indicated as 0 ) as well as the future value at the end of the 5" year using the EXCEL formulas PV, FV \& NPV as well as the appropriate mathematical formulas for the return (discount) rate you decide. Use the FORMULATEXT function to indicate the formulas you use. Mark with a background color the cells you use to drag and have all evaluations. Explain the difference of the return rate from the discount rate, and the functions you use. CHAPTEA 3 Measures for Evaluation of imvestment Opportunities 79 Comparing the projects using IRR: If we use the IRR rule to choose between the projects, then B is preferred to A, since the IRR of Project B is higher than that of Project A. Comparing the projects using NPV: Here the choice is more complicated. When the discount rate is 15% (as illustrated above), the NPV of Project B is higher than that of Project A. In this case, the IRR and the NPV agree: Both indicate that Project B should be chosen. Now suppose that the discount rate is 8%; in this case, the NPV and IRR rankings conflict: In this case, we have to resolve the conflict between the ranking on the basis of NPV (A is preferred) and ranking on the basis of IRR (B is preferred). As we stated in the introduction to this section, the solution to this question is that you should choose on the basis of NPV. We explore the reasons for this later on, but first we discuss a technical question. Why Do NPV and IRR Give Different Rankings? Below we build a table and graph that show the NPV for each project as a func- From the graph, you can see why contradictory rankings occur: - Project B has a higher IRR (27.38\%) than project A (19.77\%), (Remember that the IRR is the point at which the NPV curve cuts the x-axis.) - When the discount rate is low, Project A has a higher NPV than Project B, but when the discount rate is high, Project B has a higher NPV, There is a crossover point (in the next subsection, you will see that this point is 8.51% ) that marks the disagreementagreement range. - Project A's NPV is more sensitive to changes in the discount rate than Project B. The reason for this is that Project A's cash flows are more spread out over time than those of Project B; another way of saying this is that Project A has substantially more of its cash flows at later dates than Project B. Summing up: ialculating the Crossover Point The crossover point - which we claimed above was 8.51% - is the discount rate at which the NPV of the two projects is equal. A bit of formula manipulation will show you that the crossover point is the IRR of the differential cash flows. To see what this means, go back to our previous example: Column D in the above example contains the differential cash flows-the difference between the cash flows of Project A and Project B. In cell D43, we use the Excel IRR function to compute the crossover point. A bit of theory (can be skipped): To see why the crossover point is the IRR of the differential cash flows, suppose that for some rate r,NPV(A)=NPV(B) : NPV(A)=CFeA+(1+r)CFlA+(1+r)2CF2A++(1+r)NCFNN=CFen+(1+r)CF1s+(1+r)2CF3s++(1+r)NCFss=NPV(B) Question 1 You have the financial flows You should evaluate the value if these financial flows at the beginning of the first year (indicated as 0 ) as well as the future value at the end of the 5" year using the EXCEL formulas PV, FV \& NPV as well as the appropriate mathematical formulas for the return (discount) rate you decide. Use the FORMULATEXT function to indicate the formulas you use. Mark with a background color the cells you use to drag and have all evaluations. Explain the difference of the return rate from the discount rate, and the functions you use. CHAPTEA 3 Measures for Evaluation of imvestment Opportunities 79 Comparing the projects using IRR: If we use the IRR rule to choose between the projects, then B is preferred to A, since the IRR of Project B is higher than that of Project A. Comparing the projects using NPV: Here the choice is more complicated. When the discount rate is 15% (as illustrated above), the NPV of Project B is higher than that of Project A. In this case, the IRR and the NPV agree: Both indicate that Project B should be chosen. Now suppose that the discount rate is 8%; in this case, the NPV and IRR rankings conflict: In this case, we have to resolve the conflict between the ranking on the basis of NPV (A is preferred) and ranking on the basis of IRR (B is preferred). As we stated in the introduction to this section, the solution to this question is that you should choose on the basis of NPV. We explore the reasons for this later on, but first we discuss a technical question. Why Do NPV and IRR Give Different Rankings? Below we build a table and graph that show the NPV for each project as a func- From the graph, you can see why contradictory rankings occur: - Project B has a higher IRR (27.38\%) than project A (19.77\%), (Remember that the IRR is the point at which the NPV curve cuts the x-axis.) - When the discount rate is low, Project A has a higher NPV than Project B, but when the discount rate is high, Project B has a higher NPV, There is a crossover point (in the next subsection, you will see that this point is 8.51% ) that marks the disagreementagreement range. - Project A's NPV is more sensitive to changes in the discount rate than Project B. The reason for this is that Project A's cash flows are more spread out over time than those of Project B; another way of saying this is that Project A has substantially more of its cash flows at later dates than Project B. Summing up: ialculating the Crossover Point The crossover point - which we claimed above was 8.51% - is the discount rate at which the NPV of the two projects is equal. A bit of formula manipulation will show you that the crossover point is the IRR of the differential cash flows. To see what this means, go back to our previous example: Column D in the above example contains the differential cash flows-the difference between the cash flows of Project A and Project B. In cell D43, we use the Excel IRR function to compute the crossover point. A bit of theory (can be skipped): To see why the crossover point is the IRR of the differential cash flows, suppose that for some rate r,NPV(A)=NPV(B) : NPV(A)=CFeA+(1+r)CFlA+(1+r)2CF2A++(1+r)NCFNN=CFen+(1+r)CF1s+(1+r)2CF3s++(1+r)NCFss=NPV(B)