It is the end of 2020. 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	2021	2022	2023	2024	2025
Cash flow	1,100	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 6%.

FIND PROBLEMS 5,6,7

Part 1 Attempt 2/6 for 8 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. 7201 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. D E F G A 1 Disc. rate 2 B 0.06 3 Year 2021 2022 2023 2024 2025 Sum From 4 1 2 2 3 4 5 5 now 5 1,100 1,600 1,800 2,000 2,200 Cash 5 flow 6 PV2020 7 1,037.74 1,423.99 1,511.31 1,584.19 1,643.97 7,201 =B5/(1+$B$1)^B4 =SUM(B6:F6) The PV for the 2021 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 Part 2 | Attempt 6/6 for 0 pts. You also want to know how much each gift will be worth in 2025 if you save it. Add another row to your spreadsheet and calculate the value of each cash flow in 2025. What is the sum of all future values? 25250.67 Incorrect This question asks for the future value (FV) of an earlier cash flow. The FV formula is: FV = PV (1+r) D E F G B 0.06 A 1 Disc. rate 2 3 Year From 2021 2022 2023 2024 2025 Sum 4 1 2 4 5 now 1,100 1,600 1,800 2,000 2,200 Cash flow 6 PV 2020 7 FV2025 1,037.74 1,423.99 1,511.31 1,584.19 1,643.97 7,201 1,388.72 1,905.63 2,022.48 2,120 2,200 9,637 =B5*(1+$B$1)^(5-B4) =SUM(B7:F7) 8 The FV for the 2021 cash flow is calculated by entering the following formula in cell BT: =B5 * (1+$B$1)^(5-B4) The exponent is (5-B4), or 5-1=4, since there are 4 years from 2021 to 2025. Finally, we get the sum of all future values by adding the SUM() function to cell G7: =SUM(B7:F7), which gives us the result of $9,637. If you save all gifts and earn an interest rate of 6% throughout, you will end up with $9,637 after 5 years. Part 3 18 Attempt 5/6 for 8 pts. What is the present value of the sum of future values calculated in the previous part? 7201.327 Correct There are two ways to find a present value (a similar argument can be made for the future value): 1. Calculate the PV of each cash flow and sum up the PVs, or 2. Calculate the PV of the sum of FVs. The important thing to remember is to only sum cash flows that are expressed in same-year dollars (2020 for method 1, and 2025 for method 2 in this example). PV = FV / (1+r)N = 9,637 / (1.06) = 7,201 Unsurprisingly, this is the same value we found in part 1. Part 4 18 | Attempt 4/6 for 8 pts. Now assume that the interest rate is 4%. What is the present value of all the gifts? 7655 Correct Having the discount rate in a separate cell makes it easy to get the result. B D E F 0.04 2 A 1 Discount rate 2 3 Year 4 From now 5 Cash flow 6 PV2020 2021 2022 2023 2024 2025 Sum 1 2 3 4 5 1,100 1,600 1,800 2,000 2,200 1,057.69 1,479.29 1,600.191,709.61 1,808.247,655 Part 5 18 Attempt 2/6 for 8 pts. Still assume that the interest rate is 4%. What is the future value of all the gifts? 0+ decimals Submit Part 6 18 Attempt 1/6 for 8 pts. Now assume that the interest rate is 10%. What is the present value of all the gifts? 0+ decimals Submit Part 7 18 Attempt 2/6 for 8 pts. Still assume that the interest rate is 10%. What is the future value of all the gifts? 0+ decimals Submit