Use the PV and NPV formulas in Excel to solve for the three scenarios in the...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
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 ? ? ? ? ? ? ? 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 ? ? ? ? ? ? ?
Expert Answer:
Related Book For
Fundamentals of Investing
ISBN: 978-0133075359
12th edition
Authors: Scott B. Smart, Lawrence J. Gitman, Michael D. Joehnk
Posted Date:
Students also viewed these finance questions
-
Start Excel. Download and open the file named Exp19_Excel_Ch09_Cap_HouseCleaners.xfsx. Grader has automatically added your last name to the beginning of the filename. 1 2 The title and column labels...
-
(a) Find each sum. 1 + 2 + 3 + 4 + 5 = ? 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 = ? 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 + 10 = ? (b) Use the formula below for the sum of the first n natural numbers to verify...
-
Determine CPI, ETC (1), and EAC. Activity Total PV 1 2 3 4 5 6 7 8 9 10 11 12 13 14 1 6 6 2 20 10 10 3 30 9 6 6 6 3 4 20 8 2 5 5 5 16 4 4 4 4 6 18 9 9 7 8 4 4 Status Report: Ending Period 5 Task %...
-
Choose a company or product that you have been noticing or admiring. Develop a plan to advertise this product that involves the United States and at least one other country, and three forms of media....
-
In what way do you think product placement is effective?
-
How do quality practices that originated in the manufacturing industry differ from the traditional quality practices of healthcare organizations?
-
A 6-cylinder, 4-stroke, single acting petrol engine develops \(50 \mathrm{~kW}\) brake power at \(4200 \mathrm{rpm}\) and brake thermal efficiency is \(22 \%\). The temperature and pressure at the...
-
Record the following transactions on the books of Keyser Co. (a) On July 1,Keyser Co. sold merchandise on account to Maxfield Inc. for $15,200, terms 2/10, n/30. (b) On July 8, Maxfield Inc. returned...
-
On April 1, 2022, an investor bought 2500 shares of PT Telkom Tbk. With a price of IDR 4550 per share. The investor gets a margin of 65% with a maintenance margin of 30%. Try to determine what the...
-
The Philbrick Company has two plants on opposite sides of the United States. Each of these plants produces the same two products and then sells them to wholesalers within its half of the country. The...
-
In the month of June, Bed-ford Company sold 350 widgets. The average sales price was $34. During the month, fixed costs were $6,320 and variable costs were 40% of sales. Instructions a. Determine the...
-
Which statement best applies to an organization's restricted fund(s)? Restricted funds are created when you acquire physical capital assets that the company owns Restricted funds are usually found in...
-
How Does your business association enable you to easily raise capital?Why or why not? Explain how easy is it for the owners of the company to transfer their ownership? If one of the owners dies, what...
-
Shorten this so I can put it in manually to my website for recommendations: These folks helped me out with a tax concern and I couldn't be happier with the outcome! They are easy to contact, easy to...
-
Give examples of what segments of society products liability should seek to protect and safeguard. Provide examples of products liability issues or claims that have been brought or raised
-
The Patels will retire in 25 Years. They will need 70% of their current income ($182,000) adjusted for inflation
-
Year 1: Chip Corp had taxable income before taking a charitable contribution deduction of $25000. They made $4,000 in charitable donations that year. Year 2: Chip Corp had taxable income before...
-
What is removed during each of the three stages of wastewater treatment: primary, secondary, and tertiary? During which state would you expect items to be recovered that were accidentally flushed,...
-
Refer to the table for XLB in Problem 14.6. What happens if you are wrong and the price of XLB increases to $25 on the expiration date?
-
What is diversification? How does the diversification of risk affect the risk of the portfolio compared to the risk of the individual assets it contains?
-
Find the conversion value of a convertible preferred stock that carries a conversion ratio of 1.8, given that the market price of the underlying common stock is $40 a share. Would there be any...
-
Two cases of data concerning production costs, other expenses and sales are presented below. Required (a) Calculate the missing amounts for the letters (a) to (l). (b) Using the data in Case 1,...
-
An analysis of the accounts of Small Appliances Pty Ltd reveals the following manufacturing cost data for the month ended 30 June 2025. Required (a) Prepare the cost of goods manufactured schedule...
-
The following accounts and amounts (balances are normal balances) were taken from the records of New Manufacturers at 30 June 2025. Required (a) Prepare a cost of goods manufactured statement for the...
Study smarter with the SolutionInn App