205 Introduction to Quantitative Methods I Winter 2018 Assignment #4 Due: 1 pm,
ID: 1150760 • Letter: 2
Question
205 Introduction to Quantitative Methods I Winter 2018 Assignment #4 Due: 1 pm, Friday, Feb 02 C oc27 The Morenos are buying a house costing $430,000. They will make a $90,000 down payment and finance the rest with a 20-year mortgage charging 3.34% interest compounded monthly a. What is the size of each monthly payment b. Prepare an amedu for the Morenos' first two mortgage payments by hand. Period Balance Interest paid Payment Principal paid New balance 2 u c. Construct an Excel spreadsheet for the amortization schedule. Your spreadsheet should show the :last three rows of the amortization schedule. 24. d. What will be the total interest paid over the 20 years? e. The mortgage will come up for renewal in 5 years. How much will Morenos owe at that time? f. The mortgage contract allows Morenos to pay in advance, but the upper limit is 10% of the original loan each year. Suppose that Morenos would like to pay off the mortgage in less than 20 years and has determined that they can pay extra $30,000 after the monthly payment at the end of the first 5 years. How long will it take Morenos to pay off the remained mortgage?Explanation / Answer
a. To calculate each monthly payment or EMI we can use excel as PMT formula
In the PMT formula we can use as PMT(rate,nper,pv).
In rate we will use rate = 3.34%/ 12 = 0.27833% ,
We are dividing by 12 as interest is being compounded monthly.
Nper is the number of installments which will be
Nper = 20*12 = 240 months
Pv is the present value of outstanding principal amount
Pv = 430000 – 90000 = 340000 ( as 90000 is paid as down payment already)
Putting these values in pmt formula we get EMI as,
EMI = $ 1944
-------------------------------
b.
period
beginning balance
payment
principal
interest
ending balance
m1
340000
$1,944.02
$997.69
946.3333
$339,002.31
m2
$339,002.31
$1,944.02
$1,000.47
943.5564
$338,001.84
--------------------
c.The last three rows of amortization schedule will look like. Here m 238, 239, and 240 signifies the months of payments. (Sorry whole excel sheet can not be insseted here.)
period
beginning balance
payment
principal
interest
ending balance
m238
$5,799.76
$1,944.02
$1,927.88
16.14266
$3,871.88
m239
$3,871.88
$1,944.02
$1,933.25
10.77672
$1,938.63
m240
$1,938.63
$1,944.02
$1,938.63
5.395848
($0.00)
-----------------
d. The total interest paid will be equal to summation of all the interest paid. We can calculate it easily in excel by auto sum function. After adding the interst column in excel we get. Total interst paid = $126565.74
period
beginning balance
payment
principal
interest
ending balance
m1
340000
$1,944.02
$997.69
946.3333
$339,002.31
m2
$339,002.31
$1,944.02
$1,000.47
943.5564
$338,001.84
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.