Question

Tedros borrowed $2 million and planned to repay the loan by making equal month-end payments over

a period of 10 years. The interest rate on the loan is 6%, compounded monthly.

(a) Calculate the amount of monthly payment.

(b) Of the 60th payment, how much will be used to repay the interest and principal for the month?

(c) Tedros plans to pay off the loan immediately after making the 60th payment. What should the

size of the lump-sum (pre-)payment be?

(d) Immediately after the 60th repayment, the central bank increased the market interest rate

and the bank subsequently raised the loan's interest rate to 8% p.a., compounded

monthly. If Tedros decides to keep the number of remaining payments unchanged,

what is the size of the new monthly repayments?

(e) Calculate the total amount of principal repaid and interest paid in the first 60 monthly

payments. Assume that the relevant interest rate is still 6%, compounded monthly (that is,

not 8%).

[Hint: One make-sense way to think about this is by focusing on the relationships

between amount borrowed, total amount repaid to the bank and the amount still owed

to the bank after the 60th payment is made. You may think of the total amount of

principal repaid first].

(f) Suppose that if the bank allows Tedros to only pay the first five years of interests at

t=60 in a single payment (with principal repayment only to be made at the end of Year

10). How much interests will Tedros has to pay at t=60? What explains the difference

in the total amount of interests paid in part (e) and here [part (f)]? Assume that the

relevant interest rate is still 6%, compounded monthly (that is, not 8%).

Answer 1

a]

Monthly loan payment is calculated using PMT function in Excel :

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

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

pv = 2000000 (loan amount)

PMT is calculated to be $22,204.10

b]

The interest amount in the 60th month payment is calculated using IPMT function in Excel :

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

per = 60 (we are calculating the interest amount in the 60th month payment)

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

pv = 2000000 (loan amount)

IPMT is calculated to be $5,824.50

The principal amount in the 60th month payment is calculated using IPMT function in Excel :

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

per = 60 (we are calculating the interest amount in the 60th month payment)

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

pv = 2000000 (loan amount)

PPMT is calculated to be $16,379.60

c]

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

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

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

pv = 2000000 (loan amount)

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

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

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

CUMPRINC is calculated to be $851,480.46

The balance loan principal outstanding after 60 months =  $20,000,000 - $851,480.46 = $1,148,519.54

The size of the lump sum payment should be $1,148,519.54

d]

Monthly loan payment is calculated using PMT function in Excel :

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

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

pv = 1148519.54 (loan amount)

PMT is calculated to be $23,287.84

The monthly loan payment is$23,287.84