Suppose you want to save up funds over the next few years to buy a house in the very expensive SF Bay Area, and the required down payment on the house today is $100,000 plus the last 5 digits of your student ID number, e.g., if your student ID number is 987654321, then the down payment today would be $154,321. However, assume that real estate prices will keep growing at an average rate of g percent per year, so the future amount of money youre trying to save up is FV = $154,321(1 + g)t, where t is the number of years from now that you buy the house. In Excel, you can raise a number x to the yth power by using either the cell formula POWER(x, y) or x^y.

To save up FV, you plan on methodically depositing D dollars every month into a sinking fund that earns interest at an annual rate of r percent per year, compounded monthly. The interest rate per month is i = r/12. The monthly deposit D needed to reach your goal in t years is D = (i.FV) / [(1 + i)n 1], where n is the total number of months over which youll be making deposits. For example, if you want to buy in t = 4 years, then n = 48 months. Alternatively, you can also calculate D in Excel using the cell formula -PPMT(i, 1, n, 0, FV), where FV is the future value described above. One key decision to make is how many years (t) you want to take to accumulate FV, which impacts the size of your monthly deposit.

To do:

1. Name your Excel file A1-LastnameFirstname and submit on iLearn by 8:00 pm on Feb. 9.

2. On Sheet 1: Make an influence chart for this situation, with monthly deposit D as the output. You can use Excels Insert > Shapes to make rectangles, arrows, etc. This may take a little time and practice.

3. On Sheet 2: Create your Excel model following the format used in class, i.e., have different sections for Inputs (e.g., down payment, g, r), Decision Variables (theres only one), Calculated Quantities, and Outputs; also, use range names.

a. Use an average percentage increase in housing prices g = 3% per year.

b. Initially, use an annual interest rate earned on the sinking fund r = 5% per year. (This will be varied in part 4 below.)

c. Initially, assume you will take 4 years to accumulate FV. (This will be varied in parts 3 & 4 below.)

4. Also, on Sheet 2, make a 1-way Data Table in which the time to accumulate FV varies from 2 to 6 years (in 1-year increments) and the output columns are monthly deposit D and future value FV.

5. Also, on Sheet 2, make a 2-way Data Table for D in which the time to accumulate FV varies from 2 to 6 years (in 1-year increments) and the annual interest rate r earned by the sinking fund varies from 3% to 6% (in 0.5% increments). Highlight cells with a monthly deposit D < $3,000.

6. On Sheet 3: Make the requested plots and give brief answers to the following questions.

a. Make an XY plot of FV vs. number of years (put FV on the vertical axis). What is the relationship (positive/negative, linear/nonlinear) between FV and the number of years t?

b. Make an XY plot of D vs. number of years t (put D on the vertical axis). What is the relationship (positive/negative, linear/nonlinear) between D and the number of years t?

c. What is the relationship (positive/negative, linear/nonlinear) between D and the sinking funds annual interest r?

d. If you can afford to make monthly deposits of at most $3,000 and the sinking funds annual interest rate is 5%, how many years will it take to make the down payment on a house?