Question

2. You have borrowed a loan form bank for $400,000 to finance a new house. You are required to repay the loan in 360 equal monthly payments starting at the end of month 1. The bank charges an interest rate of 3% APR, compounded monthly. (i) What is the reduction in repayment time if you decide to add $400 to each payment? What is the remaining balance of the loan after 120 payments under (ii) the original payment plan? (iii) Should you refinance the loan at 5% APR, after making 60 payments in the original plan, if the bank charges 2 % of the remaining balance as service charge?

please show all steps

Answer 1

Monthly payment at 3% APR is calculated using PMT function in Excel :

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

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

pv = 400000 (loan amount)

PMT is calculated to be $1,686.42.

A1 A i ($1,686.42) fac =PMT(3%/12,360,400000) D E F B C

i]

If the monthly payment is increased by $400, monthly payment = $1,686.42 + $400 = $2,086.42.

The number of months to pay off the loan is calculated using NPER function in Excel :

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

pmt = -2086.42 (increased monthly payment)

pv = 400000 (loan amount)

NPER is calculated to be 261 months.

The reduction in prepayment time = 360 - 261 = 99 months.

A3 - f =NPER(3%/12, A2,400000) D E F B C 1 ($1,686.42) 2 ($2,086.42) 3 261

ii]

We calculate the principal paid off after 120 months using CUMPRINC function in Excel :

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

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

pv = 400000 (original 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 $95,920.76

A4 fx =CUMPRINC(3%/12,360,400000,1,120,0) C D E F G B 4 $ (95,920.76).

The balance loan principal outstanding after 120 months =  $400,000 - $95,920.76 = $304,079.24

iii]

If the bank charges 2% of the remaining balance as service charge, the 2% will be added to the outstanding principal.

In addition, the interest rate is not being decreased, but increased to 5%.

Thus, the monthly payments will increase.

Therefore, you should not refinance the loan