Question

How much will a $700,000 house cost in 15 years if house prices increase at 3% per year?

Your banker says that you'll need a 20% down payment for your first home purchase. How much will you need to save over the course of the next 15 years to have 20% down? If you are going to make monthly deposits into a savings account (earning 3% interest) how much will you need to save each month for the next 15 years? Start your savings plan at the beginning of the month.

Create a table showing how your savings will grow. Show your beginning balance, monthly deposit, interest earned and ending balance for each month of the savings period. Hint: You should have 180 rows and the final balance should equal the down payment you calculated in part b.

When you buy the house (after putting 20% down) what will your monthly payment be? Assume that the annual interest rate is 4.5% and that you'll pay off the loan over 30 years.

Make a loan amortization table to show how the balance of your loan will decline over time. Make sure that the ending balance is 0.

You aren't totally sure that house prices will increase at 3% per year. Create 2 data tables: one that shows how the price of the house in 15 years will vary depending on the rate at which prices increase. Use annual rates of 1% to 10%. In the other table, show how your monthly payment will vary depending on the price of the house. Use the values that you found in the first data table as your house prices in the second table. (Don't worry about changing your down payment).    All problems in excel with formulas....I need direction.

Answer 1

1. Cost of house after 15 years = 700000 * 1.03¹⁵ = 1090577 (type the same in Excel)

2. Amount of monthly deposit = 958.58

3. Look at the following construct and prepare an exact table in a new excel workbook

Once you have made the above table, extend it till 180 months and drag down the formulae as shown in the image below:

Once you have done the same, Use the Goal Seek functionality in Excel and set the Cell "G183" i.e the ending balance of month 180 to 218115.40 (20% *1090577) by changing the cell "E4" i.e the first deposit cell

Once you do this, you shalll have the following output

4. Amount of repayment = 4420.63

5. Construct the following construct first:

After the same, Use Goal Seek functionality to set the cell, "G364" (ending balance of month 360) by changing the cell, "F5" (i.e. the first repayment cell).

You shall get the following resut: