Use the PV and NPV formulas in Excel to solve for the three scenarios in the spreadsheet. After completing all three requirements, verify the amounts from each scenario below. For Scenario A: Discount Rate 10% Check your formula setup by changing the "Future date received" to 5 years instead of 2 years, while keeping all other data the same from the original example. If working properly, at a Discount Rate of 10% , the Present Value should now be $6,209.21. For Scenario B: Discount Present Value $8,264.46 Rate 10% Present Value $18,953.93 Check your formula setup by changing the "Annual Cash Receipt" to $7,500 instead of $5,000 years, while keeping all other data the same from the original example. If working properly, at a Discount Rate of 10%, the Present Value should now be $28,430.90. For Scenario C: NPV of Investment $459.13 Check your formula setup by changing the "Annual Cash Receipt" to $7,500 instead of $5,000 years, while keeping all other data the same from the original example. If working properly, at a Discount Rate of 10%, the Present Value should now be $28,430.90. For Scenario C: NPV of Investment $459.13 Check your formula setup by changing the "Initial Investment" to ($8,000) instead of ($5,000), while keeping all other data the same from the original example. If working properly, the Net Present Value should now be ($2,540.87). Hint: For Scenario C, the Net Present Value includes the total NPV of all cash flows received in Years 1 through 5, plus the cost of the initial investment to determine the final total NPV of the investment. If you did not get any of these answers, reset the values indicated in the scenarios above and review your formulas based on what you learned in the Tutorial tab. For each requirement, change the values of the given information as shown and keep all other original data the same. Then enter your updated final answers for each scenario. Scenario A: Future value to be received Future date received Discount Rate 10% 14% 20% Scenario B: Annual Cash Receipt Number of Years Discount Rate 10% 14% 20% Scenario C: Discount Rate 12% $ 11,000 2 years $ 5,500 5 years Investment Project Initial Investment Year 1 Year 2 Year 3 Year 4 Year 5 Discount Rate Present Value Cash Flow 10% 14% 20% $(7,000) 950 Required: a. A company is expecting to receive a lump sum of money at a future date from now. Using the PV formula in Excel, what is the Present Value of that money at three different rates? (Round your answers to 2 decimal places.) $ $ 1,050 $ 1,650 $ 3,350 $ 6,300 b. A company is expecting to receive a stream of year-end annual cash payments over multiple years. Using the PV formula in Excel, what is the Present Value of that money at three different discount rates? (Round your answers to 2 decimal places.) Discount Rate 10% 14% 20% Present Value c. A company is planning to invest in a project over a 5-year period, but wants to know its financial implications. It expects the cash in- flow return on the investment to steadily increase over the 5 years. Using the information given, help to determine the Total Net Cash Flows, the Net Present Value, and the estimated Payback Period. Note: Estimate the payback period to the nearest year. (Round your Net Cash Flow values to the nearest whole dollar and your final Net Present Value answer to 2 decimal places.) Investment Project Initial Investment Year 1 Year 2 Year 3 Year 4 Year 5 NPV of investment Estimated Payback Period Total Net Cash Flow 1 PV and NPV - Exercise 2 3 Below are multiple scenarios where each company needs assistance to calculate the Present Value, Net Present 4 Value, and Payback Period. 5 6 Required 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 a. A company is expecting to receive a lump sum of money at a future date from now. Using the PV formula in Excel, what is the present value of that money at three different discount rates shown below? Note: Your final answers should be displayed as a positive number. Future value to be receive $ Future date received Discount Rate 10% 14% 20% 10,000 2 years Annual Cash Receipt Number of Years Present Value ? ? ? b. A company is expecting to receive a stream of year-end annual cash payments over multiple years. Using the PV formula in Excel, what is the present value of that money at three different discount rates shown below? Note: Your final answers should be displayed as a positive number. $ 5,000 5 years 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 Annual Cash Receipt Number of Years Discount Rate 10% 14% 20% Discount Rate Investment Project Initial Investment Year 1 Year 2 Year 3 Year 4 Year 5 $ NPV of investment Estimated Payback Period c. A company is planning to invest in a project over a 5-year period, but wants to know its financial implications. It expects the cash in-flow return on the investment to steadily increase over the 5 years. Using the information below, help determine the Total Net Cash Flows, the Net Present Value and the estimated Payback Period. Note: Estimate the payback period to the nearest year. Present Value ? ? ? 5,000 Cash Flow $ $ $ $ 60606060 $ 5 years $ 12% (5,000) 800 900 1,500 1,800 3,200 Total Net Cash Flow ? ? ? ? ? ? ?