Instructions

1. Complete all questions on this Word document.
2. Add a snip (or a PrintScreen) from your Excel spreadsheet to back up your answer.
3. Create one Excel file with two tabs: one for Question 1, one for Question 2.
4. Submit both the Word doc and the Excel file through HuskyCT before the deadline. The Excel file should be fully functional, i.e., anyone should be able to run Solver without errors on your model, change parameters in the spreadsheet (within the range allowed), etc., without making additional modifications (you do not need to provide any error checking). All reports generated by Excel (e.g., Sensitivity Reports from Solver) should be included.
5. Your submission is considered incomplete (and will not be graded) if the Excel file is missing.
6. You are allowed to use all class materials (books, notes, class recordings, homeworks) to assist you in completing this quiz, but are not allowed to seek any help from others. UConns rules and regulations that govern the student code of conduct can be found at: The Student Code (printable copy) | Community Standards (uconn.edu)
7. The due date is Tuesday March 9 2021, before midnight via HuskyCT. Multiple attempts are allowed, i.e., you can upload your work multiple times (before the due date), and only the last submission will be graded.

Problem 1 (30 points)

You are planning your wealth for the next 15 years. You have an investment account that currently yields about 3.5% per year (earnings are not taxed) and has a balance of roughly $37,500. Currently, you think you will be able to set aside about $7,500 annually to add to this investment account at the end of each year. Because of salary raises, you expect this amount to go up by 1.5% per year. At the end of year 5, you plan to withdraw $50,000 from the investment account for the down payment of a house you are planning to buy.

For simplicity sake, you may assume that all actions occur at the end of the year, i.e., contributions, interest paid and withdrawals all happen on 12/31 each year (for simplicity).

1. (10 points) Parameters that a user should be allowed to change in your Excel model (without having to make additional modifications to the Excel model) are:
   • Investment horizon (min. 2, max 50 years)
   • Beginning balance of investment account ($37,500)
   • Yield (3.5%) on the investment account
   • Annual starting contribution ($7,500)
   • Increase in annual contribution (1.5%)
   • The year and amount of a one-time withdrawal (year 5, $50,000 for down payment of the house).

You do not need to provide any error checking in your Excel. Develop a spreadsheet model for this situation in Excel.

2. (2 points) How much would you have saved after 15 years?
3. (5 points) You currently start by contributing $7,500 per year to the investment account. How low can this initial contribution be (still growing at 1.5% annually) such that you still can withdraw $50,000 after year 5? Show how you used Goal Seek (provide a snip or copy-paste from the Excel screen) to accomplish this.
4. (7 points) Reset the initial contribution to the original $7,500. Make a one-way table displaying the final wealth at the end of the investment horizon for the following interest rates of the investment account: 2.5%, 3%, 3.5%, 4% and 4.5%.

5. (6 points) Make a two-way table displaying the final wealth when varying the parameters for the one-time withdrawal as:
   • Withdrawal year: 5,6,7 and 8.
   • Withdrawal amount: $35,000, $50,000, $65,000 and $80,000.