Question

1. The Taylors are buying a house costing $400,000. They will make a $60,000 down payment and finance the rest with a 20-year mortgage charging 5.4% interest compounded monthly. a. What is the size of each monthly payment? (Spoints) b. How much is still owing after 15 years (5 points) C. The Taylors have decided to pay $2500 per month. How many years will it take to pay off the mortgage? (7 points) d. Construct an Excel spreadsheet for the amortization schedule. Your spreadsheet should show the last first 10 rows of the amortization schedule. (9 points) Xo IV. A 5. P W SM 111 112 IL 112 1444 .06 SAMA e. Based on the amortization table how much interest was paid in the first 10 payments? (1 points)

Answer 1

Answer (a):

Cost of house = $400,000

Down payment = $60,000

Loan amount = 400000 - 60,000 = $340,000

Annual interest = 5.40%

Monthly interest = 5.40% / 12 = 0.45%

Number of payments = 20 * 12 = 240

Monthly Payment = PMT (rate, nper, pv, fv, type) = PMT(0.45%, 240, -340000, 0, 0) = $2319.6554

Monthly Payment = $2319.66

Answer (b):

Amount Taylor's owe after 15 years = Present value remaining monthly payments

Remaining number of monthly payments = (20 - 15) *12 = 60

Amount Taylor's owe after 15 years = PV (rate, nper, pmt, fv, type) = PV (0.45%, 60, -2319.6554, 0, 0) = $121,734.48

Amount Taylor's owe after 15 years = $121,734.48

Answer (c)

PMT = $2,500

Number of months required = NPER (rate, pmt, pv, fv, type) = NPER(0.45%, 2500, -340000, 0, 0) = 210.862 months

Number of years required = 210.862 / 12 = 17.57 Years

Number of years required = 17.57 Years

Answer (d):

The first 10 rows of amortization schedule is as follows:

F Interest 1 Taylor's Mortgage 2 Loan (PV) $340,000 3 Annual Interest 5.40% 4 Periods per year 12 5 Number of Years 20 6 Rate per period 0.0045 7 Number of payments (NPER) 240 8 Monthly Payment $2,319.66 9 10 Beginning Payment # Payment 11 Balance Prev. E $340,000.00 $2,319.66 $339,210.34 $2,319.66 $338,417.14 $2,319.66 $337,620.36 $2,319.66 $336,819.99 $2,319.66 $336,016.03 $2,319.66 $335,208.44 $2,319.66 $334,397.23 $2,319.66 $333,582.36 $2,319.66 $332,763.82 $2,319.66 23 Total B*0.45% $1,530.00 $1,526.45 $1,522.88 $1,519.29 $1,515.69 $1,512.07 $1,508.44 $1,504.79 $1,501.12 $1,497.44 $15,138.16 Principal Ending Repayment Balance C-D B-E $789.66 $339,210.34 $793.21 $338,417.14 $796.78 $337,620.36 $800.36 $336,819.99 $803.97 $336,016.03 $807.58 $335,208.44 $811.22 $334,397.23 $814.87 $333,582.36 $818.53 $332,763.82 $822.22 $331,941.61

The above table with 'show formula' is as below:

340000 0.054 12 1 Taylor's Mortgage 2 Loan (PV) 3 Annual Interest 4 Periods per year 5 Number of Years 6 Rate per period 7 Number of payments (NPER) 8 Monthly Payment =B3/B4 =B4*B5 =PMT(B6,B7,-B2,,0) А Beginning Balance Payment Interest Principal Repayment Ending Balance Prev. E =B8 -C13 Payment # 11 12 13 1 14 2 15 3 16 4 17 5 18 6 19 7 20 8 21 9 =C14 =C15 -C16 =B2 =F13 =F14 =F15 =F16 =F17 =F18 =F19 =F20 =F21 B* 0.45% =B13*$B$6 =B14*$B$6 =B15*$B$6 |=B16*$B$6 =B17*$B$6 =B18*$B$6 =B19*$B$6 =B20*$B$6 =B21*$B$6 =B22*$B$6 -SUM(D13:D22) C-D =C13-D13 =C14-014 =C15-D15 =C16-D16 =C17-017 =C18-D18 =C19-019 =C20-D20 =C21-D21 =C22-D22 B-E =B13-E13 =B14-E14 =B15-E15 =B16-E16 =B17-E17 =B18-E18 =B19-E19 =B20-E20 =B21-E21 =B22-E22 =C19 =C20 22 10 =C21 23 Total

Answer (e):

Total Interest paid in first 10 months = $15,138.16

Working:

Please refer to amortization table in answer d above. The total of first 10 months expense = $15,138.16