Question

Peter borrowed $800,000 to refit his fishing trawler.  The loan requires monthly repayments over 15 years.  When he borrowed the money the interest rate was 13.5% per annum, but 18 months later the bank increased the interest rate to 15% per annum, in line with market rates.  The bank tells Peter he can increase his monthly repayment (so as to pay off the loan by the originally agreed date) or he can extend the term of loan (and keep making the same monthly repayment).  Calculate using excel:

 

a. (4 marks)

The new monthly repayment if Peter accepts the first option.

 

b. (2 marks)

The extra period added to the loan term if Peter accepts the second option.

Answer 1

(a) Calculation of New monthly repayment amount:

Borrowed Amount = $800,000 for 15 years (or 180 months) and Rate of Interest = 13.5% (or 0.01125 monthly)

Let Monthly repayment be x: Formula to be used:

Pricipal Loan Amount = (x/r) * { 1 - [1/(1+r)^n]}

$800,000 = x / 0.01125 * { 1- [1/(1+0.01125)^180]}

x = 10,386.55

Principal = Monthly repayment / r * { 1 - [1/(1+r)^n]}

= $10,386.55/0.01125 * { 1 - [1/(1+0.01125)^162]}

= $772,506.23

Let new monthly repayment be x

r = 0.15/12 = 0.0125

Pricipal Loan Amount = (x/r) * { 1 - [1/(1+r)^n]}

$772,506.23 = x / 0.0125 * { 1- [1/(1+0.0125)^162]}

x = $11,146.13

(b) Calculation of New term if monthly repayment amount remains same: Let it be t,

t = log[m/(m - Pi)] / log(1+r); m = monthly repayment amount and P = Principal amount

t = log[$10,386.55/($10,386.55 - $772,506.23 * 0.0125)] / log(1+0.0125)

t = log(14.2238)/log(1.0125)

t = 213.72 months (using excel or Financial calculator)

Extra period added = 51.72 months (213.72 - 162)