In: Finance
Assume that 8 years ago you borrowed $200,000 as a 30-year mortgage on your home with an annual percentage rate of 7% at monthly payments (12 payments per year). You plan to refinance this mortgage with a new 30 year low at the current rate of 5%.
a. What is the monthly payment of the original mortgage.
b. How much do you still owe of the original principal after seven years? (Hint: for a loan that is amortized, like a mortgage, the amount you still owe at any time is the present value of the remaining payments that have not yet been made).
c. How much money can you borrow now at the new interest rate if you keep the same monthly payments as the original mortgage?
Sub question (a)
Loan repayment together with interest is made in equated monthly instalments (EMIs). EMIs comprise both principal and interest portions and the monthly payment is constant.
EMIs can be calculated using the following formula
EMI= [P*r*(1+r)^n]/[(1+r)^n]-1
Where P= Loan principal, r= rate of interest in decimals for each period of installment and n= number of monthly installments.
In the given case, Principal P=$200,000, r=7/(12*100)= 0.00416667 and n=30*12=360.
EMI= [200000*0.00416667 *(1+0.00416667)^360]/[(1+0.00416667)^360]-1
Using an Excel Worksheet, this calculation is completed and EMI of original loan is arrived at $1330.60
==========
Screen shot of the relevant portion of Excel sheet is appended below:
Sub question (b):
Amount still owe is the present value of the unpaid instalments, discounted at the original interest rate
For this purpose, number of remaining EMIs is (360-84)= 276
PV= ∑ A[1/(1+r)^ni]
Where A= Amount of EMI, r=R/100= 7/100 = 0.07 and ni =each of the time period ranging from 1 to 276.
The PV is calculated using the PV function of Excel Work Sheet with the following input variables:
Rate: Interest rate for the period, ie. Month= r/12= 0.583333
Nper= No. payments= 276
Pmt= $1,330.60, Fv= Future value=0, Type= 0 (by default) which is payment at the end of each period
The amount still owe thus arrived at is $182,294
========
Screen shot of the function arguments of Excel sheet is appended below:
Sub question (c):
Amount that that can be borrowed now for another term of 30 years at the interest rate of 5%, keeping the EMI at the original level.
This can be calculated by finding the value of P in the formula for EMI discussed in sub question 1
EMI= [P*r*(1+r)^n]/[(1+r)^n]-1
For this purpose, EMI= $1,330.60, r=5/(12*100) =0.00416667, n=360
1330.60=[P*0.00416667 *(1.00416667)^360]/[(1.00416667)^360]-1
1330.60=(P*0.00416667*4.467744)/(4.467744-1)= 4.467744*0.00416667*P/3.367744
1330.60*3.367744= 0.018616 P
Therefore, new loan amount P= (1330.60*3.367744)/ 0.018616= $247,866.10
=============