Question

a. Calculate the monthly interest and principal cash flow for a fixed rate self amortizing mortgage with an initial balance of $350,000, a contract rate of 3.75%, and a maturity of two years. b. If the borrower decides to pay the loan back in full after the sixteenth month and the lender charges a 1% prepayment penalty, what must the borrower pay the lender? c. What is the market price of this loan if the lender decides to sell it into the secondary market at the end of the tenth month and the market yield to maturity at that time is expected to be 4.15%? Would you advise the lender to sell?

Please answer all parts A, B, and C. Please show all work! If you use Excel to solve this problem please make clear the calculations you made.

Answer 1

Part (a):

The monthly payments is $ 15,159.81 as follows:

7 24 1 A B C D Ε 1 Constant Payment Mortgage or Equated Monthly Installments (EMI) 2 Assuming payments at the end of each period 3 EMI is calculated using the formula EMI=[P*r*(1+r)^n]/[(1+r)^n]-1 4 Where P= Principal (loan amount), 5 n= Number of installments. ie if N= number of years, n=N*12 6 r= Rate of interest per month in decimals ie. If R= Yearly rate, r=R/(100*12) All amounts in $ 8 Given that 9 Principal (loan amount) (P)= 350,000 No. of years (N)= 10 Rate of interest (% p.a)= R= 3.75 No. of months (n)= 11 12 Calculations: 13 Formula Cell reference Value 14 Interest rate per month in decimals (r )= R/(100*12) D10/(100*12) 0.003125 15 No. of months (n)= N*12 G9*12 24 16 (1+r)= 1+G14 1.003125 17 (1+r)^n= G16610 1.077758 18 [(1+r)^n)-1= G17-1 0.077758 19 P*r*(1+r)^n= D9*G14*G17 1178.798 20 Equated Monthly Insallments (EMI)= [P*r*(1+r)^n]/[(1+r)^n]-1 G19/618 15159.81 21 Rounded to 15159.81 22

Part (b):

It is assumed that the lender charges prepayment penalty (@1%) on the remaining balance. Balance after payment of 16^th monthly installment is $ 119,590.60 as shown in the amortization schedule below:

Н K L o au wn Shedule of amortization Month Beginning Monthly Monthly Interest Principal End balance interest Payments component Component balance =Col (2)*(3) Col (4)-(5) =Col (2)-(6) (1) (2) (3) (4) (5) (6) 1 350000 0.003125 15159.81 1093.75 1 4066.06 335933.94 2 335933.94 0.003125 15159.81 1049.79357 14110.01 321823.93 rate (7) 21 22 23 15 16 17 149024.73 0.003125 134330.62 0.003125 119590.60 0.003125 15159.81 465.7022684 15159.81 419.7831893 15159.81 373.7206131 14694.11 14740.02 14786.09 134330.62 119590.60 104804.51 do 1000 ao 22

Part (b):

It is assumed that the lender charges prepayment penalty (@1%) on the remaining balance. Balance after payment of 16^th monthly installment is $ 119,590.60 as shown in the amortization schedule below:

Prepayment penalty @1% = $ 119,590.60*1% = $1,195.91

Therefore, total amount required to pay off the loan= $ 119,590.60*(1+1%)= $120,786.50

Part (c):

After 10 months (after making 10 monthly payments), remaining number of payments is 14. Market price will be the present value of future monthly payments, discounted at the current yield. The price is ascertained at $206,832.46 as follows:

A B C D E F G 1 Present Value of Annuity Payments at the end of each period 2 3 Present value of annuity is calculated using the formula PVA=P*{[1-(1+r)^-n], 4 Where P= Periodical payments, 5 n= Number of payments. 6 r= Rate of interest per period in decimals 7 All amounts in $ 8 Given that 9 Periodical payments (P)= 15159.81 10 Rate of interest (% p.a)= R= 4.15 No. of payments a year (t)= 11 12 Calculations: 13 Formula Cell reference Value 14 Interest rate per period in decimals (r )= R/(100*t) D10/(100*G10) 0.003458333 15 No. of periods (n)= G9*G10 14 16 (1+r)= 1+G14 1.003458333 17 (1+r)^-n= G164-G15 0.952816314 18 [1-(1+r)^-n]= 1-G17 0.047183686 [1-(1+r)^-n]/r= G18/614 13.64347541 20 Present value of annuity PV= P*{[1-(1+r)^-n]/r]} D9*G19 206832.462 21 Rounded to $206,832.46 22 N*t 19

After 10 months, balance amount at the current rate is $207,344.81 as follows:

Month Shedule of amortization Beginning Monthly Monthly Interest balance interest Payments component rate =Col (2)*(3) (2) (3) (4) (5) 350000 0.003125 15159.81 1093.75 Principal End Component balance Col (4)-(5) -Col (2)-(6) (6) . (7) 14066.06 335933.94 (1) 8 250609.69 0.003125 15159.81 783.1552784 9 236233.04 0.003125 15159.81 738.22824 10 221811.46 0.00312515159.81 693.1608045 11 207344.81 0.003125 15159.81 647.9525334 12 192832.96 0.003125 15159.81 602.6029863 1 4376.65 14421.58 14466.65 14511.86 1 4557.20 236233.04 221811.46 207344.81 192832.96 178275.75

Since the sale proceeds is less than the present balance, it suggested not to sell.