Question

You are planning to buy a new house. You currently have 535,000 and your bank told you that you would need a 15% down payment Jus an additional 4% in closing costs. If the house that you want to buy costs $250.000 and you can make a 7% annual return on your avestment, determine the following: When will you have enough money for the down payment and closing costs, assuming that the $35.000 is the only investment that you make? 5 ) You decide that you want to buy the house in 3 years. How much do you need to save every month to achieve your goal? 3) Assume that three years later the house still has the same price and that you can get a 15-year mortgage from your bank at a fixed rate of 4.5% What are the monthly payments on the loan? How much will you have to pay the bank each year? What is the total interest over the term of the loan? 0 How much do you pay on interest and principal the first monthly payment? 11 low much in the 50th month? (Hint: Also use the IPMT and PPMT functions)

Answer 1

1). Down payment required = 15%*price of the house = 15%*250,000 = 37,500

Closing costs = 4%*250,000 = 10,000

Total amount required = 37,500 + 10,000 = 47,500

FV = -47,500; PV = 35,000; rate = 7%, solve for Nper. Number of years after which total amount will be available is 4.51 years.

2). 47,500 is required in 3 years, now.

PV = 35,000; FV = -47,500; N = 3*12 = 36; rate = 7%/12 = 0.583%, solve for PMT.

Monthly savings needed = $108.88

3). Mortgage amount = price - down payment = 250,000 - 37,500 = 212,500

PV = -212,500; n = 15*12 = 180; rate = 4.5%/12 = 0.375%, solve for PMT.

Monthly payment = $1,625.61

4). Annual amount to be paid to the bank = monthly payment*12 = 1,625.61*12 = $19,507.33

5). Total payment to be made over 15 years = monthly payment*180 = 292,609.93

Principal = 212,500

Total interest = total payment - principal = 292,609.93 - 212,500 = $80,109.93

6). First month interest = monthly interest rate*outstanding principal = 0.375%*212,500 = $796.88

Principal = monthly payment - interest = 1,625.61 - 796.88 = $828.74

7). 50th month interest (using IPMT function): rate = 0.375%; per = 50; Nper = 180. PV = -212,500, solve for IPMT. Interest = $630.05

50th month principal (using PPMT function): rate = 0.375%; per = 50; Nper = 180; PV = 212,500, solve for PPMT. Principal = $995.56