Question

You are ready to purchase a home that has a price of $400,000. You will make a down payment of $50,000 and will finance the remainder with a 15-year loan. The stated rate on the loan is 6% annual with monthly payments.

a) Compute the monthly payment that you will be making on this loan.

b) What would be your monthly payment if you financed the home for 30 years?

c) Provide an amortization schedule of three months for this loan, beginning with the first payment.

d) After living in this home for 10 years, you decided to sell the home and find that the sale price of the home will be $500,000. If you sell the home at this price, how much cash can you receive from the sale, ignoring the commissions?

Answer 1

a) Monthly payment can be calculated using PMT function on a calculator

N = 15 x 12 = 180, I/Y = 6%/12, PV = 350,000, FV = 0

=> Compute PMT = $2,953.50 is the monthly payment

b) If N = 30 x 12 = 360 => PMT = $2,098.43

c) Interest Payment = Loan Amount x 6%/12

Principal Paid = Payment - Interest Paid

Period	Payment	Interest	Principal	Outstanding
1	$2,953.50	$1,750.00	$1,203.50	$348,796.50
2	$2,953.50	$1,743.98	$1,209.52	$347,586.98
3	$2,953.50	$1,737.93	$1,215.56	$346,371.42

d) Principal Paid over 10 years can be calculated using CUMPRINC function in excel

CUMPRINC(rate = 6%/12, nper = 15*12, pv = 350000, 1, 120, 0) = $197,228.60

Outstanding loan amount = 350,000 - 197,228.60 = $152,771.39

Cash Received from sale = 500,000 - 152,771.39 = $347,228.61