90.2K

Verified Solution

Link Copied!

Intro It is the end of 2019. You plan to complete a Ph.D. in 5 years. Your favorite uncle has promised to help you with your school expenses by giving you the following amounts for Christmas: Year Cash flow 2020 2021 2022 2023 2024 1,000 1,600 1,800 2,000 2,200 Your uncle is fairly wealthy and very reliable. You currently have $10,000 in a savings account paying an annual interest rate of 8%. Part 1 IB Attempt 4/10 for 10 pts. Create an Excel spreadsheet showing the years and cash flows as in the table above, then add a row that shows the number of years from now and another row that shows the present value of each cash flow. What is the sum of present values? Hint: do not hardcode the discount rate into your formulas. Instead, keep it in a separate cell and refer back to it. You'll be asked to change in discount rate in parts 5 to 8. Correct It is considered best practice to keep the discount rate in a separate cell (B1 in this example), so that it is easy to see and easy to change. C D E F G U A 1 Disc. rate B 0.08 Year 2020 2021 2024 Sum 2022 3 2023 4 From now Cash 1,000 1,600 1,800 2,000 2,200 flow 6 PV2019 925.93 1,371.741,428.9 | 1,470.061,497.28 6,694 =B5/(1+$B$1)^B4 =SUM(B6:F6) The PV for the 2020 cash flow is calculated by entering the following formula in cell B6: =B5/(1+$B$1)^B4 Copy and paste that formula to the other cells in row 6. To do so, select cell B6, press control/command + c, then select one or more other cells and press control/command + v. Note that the reference to the discount rate, $B$1, uses the dollar sign before the column (B) and row references (1). That way, the reference stays constant when copying the formula to other cells. The reference to the discount rate is absolute, while the references to the cash flows (B5) and years from now (B4) are relative Finally, we get the sum of all present values by adding the SUM() function to cell G6: =SUM(B6:F6), which gives us the result of $6,694. There are two interpretations for this amount: 1. The value of your uncle's gifts, expressed in current dollars, or 2. The amount you can take out of your savings account right now (2019) so that you still end up with the same balance in 2024 as when you didn't take out any money and didn't receive any gifts from your uncle. Basically, your uncle's gifts let you borrow from yourself now and then pay yourself back over time. The sum of present values is $6,694. Part 2 IB Attempt 4/10 for 8 pts. You also want to know how much each gift will be worth in 2024 if you save it. Add another row to your spreadsheet and calculate the value of each cash flow in 2024. What is the sum of all future values? No decimals Submit 18 Attempt 1/10 for 8 pts. Part 3 What is the present value of the sum of future values calculated in the previous part? No decimals Submit Part 4 18 Attempt 1/10 for 10 pts. Now assume that the interest rate is 6%. What is the present value of all the gifts? No decimals Submit Part 5 18 | Attempt 1/10 for 10 pts. Still assume that the interest rate is 6%. What is the future value of all the gifts? No decimals Submit Part 6 18 Attempt 1/10 for 10 pts. Now assume that the interest rate is 12%. What is the present value of all the gifts? No decimals Submit Part 7 IB | Attempt 1/10 for 10 pts. Still assume that the interest rate is 12%. What is the future value of all the gifts? No decimals Submit

Answer & Explanation Solved by verified expert

See Answer