Question

You plan to buy your dream home in 10 years. At that time, you would like to be able to afford a $400,000 home and put 20% down (pay 20% of the purchase price with your own money), and take out a 30 year mortgage for the rest. You can invest at 9% per year compounded monthly. Assume this will also be the interest rate on the mortgage.

a. How much money do you need to save each month for the next 10 years to make the down payment?

b. How much will you have to borrow to buy the home?

c. What will your monthly payment be on the mortgage? Assume the same 9% monthly APR from above.

d. What will the balance on your mortgage be after you’ve made your monthly payments for 10 years (120 payments)?

Answer 1

a]

Amount to save each month is calculated using PMT function in Excel :

rate = 9%/12 (converting annual rate into monthly rate)

nper = 10*12 (10 years with 12 monthly savings each year)

pv = 0 (Amount currently saved is zero)

fv = 80000 (Down payment = cost of home * 20% = $400,000 * 20% = $80,000)

PMT is calculated to be $413.41

Amount to save each month is $413.41

b]

Amount to borrow = cost of home - down payment = $400,000 - $80,000 = $320,000

c]

Monthly loan payment is calculated using PMT function in Excel :

rate = 9% / 12   (converting annual rate into monthly rate)

nper = 30*12 (30 year loan with 12 monthly payments each year)

pv = 320000 (loan amount)

PMT is calculated to be $2,574.79

d]

We calculate the principal paid off after 10 years (120 months) using CUMPRINC function in Excel :

rate = 9% / 12   (converting annual rate into monthly rate)

nper = 30*12 (30 year loan with 12 monthly payments each year)

pv = 320000 (loan amount)

start period = 1 (We are calculating principal paid off between 1st and 120th month)

end period = 120 (We are calculating principal paid off between 1st and 120th month)

type = 0 (each payment is made at the end of month)

CUMPRINC is calculated to be $33,824.82

The balance loan principal outstanding after 10 years =  $320,000 - $33,824.82 = $286,175.18