Question

Please address the following scenarios on different pages of your Excel worksheet:

Jack and Jill would like to purchase a house priced at $300,000 with a 30-year mortgage. The bank requires they put a 20% downpayment on the mortgage. The rest of the amount will be provided to J&J by the bank as a loan with 4.25% interest compunded monthly. The initial plan of J&J is to pay off the mortgage on the house in 12x30 = 360 months with equal monthly payments.

1. Calculate the equal end-of-month payments.
2. What is the remaining balance on the loan after 5 years of payments?
3. What is the total amount of the payments that went towards paying the interest portion of the mortgage.
4. J&J decided to make $200 additional payments each month in order to payoff the loan earlier. How long does it take them to payoff the loan now?
5. J&J would like to payoff the loan in 15 years instead of 30 years. How much additional payments should they make each month?

Answer 1

Down payment = 20%* $300000 = $60000

Mortgage amount = $300000 -$60000 = $240000

Monthly rate = 4.25%/12 = 0.00354167

So, Monthly mortgage payments (X) is given by

X/0.00354167 * (1-1/1.00354167^360) = 240000

=> X*203.2768674 = 240000

=> X = $1180.66

End of month payments is $1180.66

After 5 years of payments,

loan outstanding = present value of remaining payments (25 years or 300 months)

= 1180.66/0.00354167*(1-1/1.00354167^360)

= $217938.47

Loan amount outstanding after 5 years is $217938.47

Total payments made = $1180.66*60 = $70839.34

Total reduction in Principal amount = $240000 - $217938.47 = $22061.53

So, Total interest paid in 5 years = $70839.34- $22061.53 = $48777.82

So, $48777.82 went towards payment of interest portion of mortgage

Let it take X no of months now that the monthly payment is $1180.66 +$200 = $1380.66

So ,

1380.66/0.00354167*(1-1/1.00354167^X) = 217938.47

=> 1-1/1.00354167^X = 0.55905712

=> 1.00354167^X =2.267867

Taking natural log of both sides

X = ln(2.267867)/ln(1.00354167) =231.61 or 232 (rounded to nearest month)

So, it will take 232 months more to pay off the loan now (after 5 years) i.e. a total of 232+60 = 292 months from the start of the mortgage

This part is not clear about whether the additional payments start from beginning or after 5 years

If additional payments start from beginning of mortgage

Total mortgage payment (A) is given by

A/0.00354167*(1-1/1.00354167^180) = 240000

=> A =1805.47

So, Additional payments required each month= 1805.47-1180.66= $624.81

If additional payments start after 5 years

Total mortgage payment (A) after 5 years is given by

A/0.00354167*(1-1/1.00354167^120) = 217938.47

=> A =2232.51

So, Additional payments required each month= 2232.51-1180.66= $1051.85