Question

As part of the purchase of a home on January 1, 2014, you negotiated a mortgage in the amount of $110 000. The amortization period for calculation of the level payments (principal and interest) was 25 years and the initial interest rate was 6% compounded semi-annually. (Do not round intermediate calculations. Round your answers to 2 decimal places.)

a)	What was the initial monthly payment?

b)	During 2014–2018 inclusive (and January 1, 2019) all monthly mortgage payments were made as they became due. What was the balance of the loan owing just after the payment made January 1, 2019?

c)	At January 1, 2019 (just after the payment then due) the loan was renegotiated at 8% compounded semi-annually (with the end date of the amortization period unchanged). What was the new monthly payment?

d)	All payments, as above, have been faithfully made. How much of the September 1, 2019, payment will be principal and how much represents interest?

Answer 1

DE G H I MNOP | RS т OLAWN Particulars principal amt rate of interest rate of interest Duration Duration amount unit 1 10000 $ 6.00% p.a 3.00% semi annually(2) 25 years 50 Semi annual years $4,275.20 $ Particulars amount unit principal amt $97,509.78 $ rate of interest 8% NEW p.a TERMS rate of interest 4% semiannually(2) Duration years Duration 39 Semi annual years Eml $4,978.93 $ Eml 11 sl no month 1 11/2014 2 1-Jul-14 Amortisation schedule on half yearly basis Interest EMI calculati amount Principal amour Closing balance $4,275.20 $3,300.00 $975.20 $109,024.80 $4,275.20 $3,270.74 $1,004.46 $108,020.34 Amortisation schedule on half yearly basis Principal Closing sl no month E MI calculai Interest amount amount balance 26 1-Jul-26 $4,978.93 $3,111.25 $1,867.68 $75,913.56 1-Jan-27 $4,978.93 $3,036.54 $1,942.39 $73,971.18 The answer aksed in question is coloured in yellow allnitial monthly payment $4,275.20 b)Balance after jan 1, 19payment $97,509.78 c)new monthly payment after jan1 2019 $4,978.93 month 1-Jul-19 1-Aug-19 1-Sep-19 d]September 2019 payment details Emi payment interest $4,978.93 $3,900.39 $829.82 $642.87 $829.82 $641.63 principal closing balance $1,078.54 $96,431.24 $186.95 $96,244.30 $188.19 $96,056.10 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 1-Jan-15 1-Jul-15 1-Jan-16 1-Jul-16 1-Jan-17 1-Jul-17 1-Jan-18 1-Jul-18 1-Jan-19 1-Jul-19 1-Jan-20 1-Jul-20 1-Jan-21 1-Jul-21 1-Jan-22 1-Jul-22 1-Jan-23 1-Jul-23 1-Jan-24 1-Jul-24 1-Jan-25 1-Jul-25 1-Jan-26 $4,275.20 $3,240.61 $4,275.20 $3,209.57 $4,275.20 $3,177.60 $4,275.20 $3,144.68 $4,275.20 $3,110.76 $4,275.20 $3,075.83 $4,275.20 $3,039.84 $4,275.20 $3,002.78 $4,275.20 $2,964.61 $4.978.93 $3,900.39 $4,978.93 $3,857.25 $4,978.93 $3,812.38 $4,978.93 $3,765.72 $4,978.93 $3,717.19 $4,978.93 $3,666.72 $4,978.93 $3,614.23 $4,978.93 $3,559.65 $4,978.93 $3,502.88 $4,978.93 $3,443.83 $4.978.93 $3,382.43 $4,978.93 $3,318.57 $4,978.93 $3,252.15 $4,978.93 $3,183.08 $1,034.59 $1,065.63 $1,097.60 $1,130.53 $1,164.45 $1,199.38 $1,235.36 $1,272.42 $1,310.59 $1,078.54 $1,121.68 $1,166.55 $1,213.21 $1,261.74 $1,312.21 $1,364.70 $1,419.28 $1,476.05 $1,535.10 $1,596.50 $1,660.36 $1,726.78 $1,795.85 $106,985.74 $105,920.11 $104,822.51 $103,691.98 $102,527.53 $ 101,328.15 $100,092.80 $98,820.37 $97,509.78 $96,431.24 $95,309.56 $94,143.01 $92,929.80 $91,668.07 $90,355.86 $88.991.16 $87,571.88 $86,095.83 $84,560.73 $82,964.23 $81,303.87 $79,577.09 $77,781.24 1-Jul-27 1-Jan-28 1-Jul-28 1-Jan-29 1-Jul-29 1-Jan-30 1-Jul-30 1-Jan-31 1-Jul-31 1-Jan-32 1-Jul-32 1-Jan-33 1-Jul-33 1-Jan-34 1-Jul-34 1-Jan-35 1-Jul-35 1-Jan-36 1-Jul-36 1-Jan-37 1-Jul-37 1-Jan-38 1-Jul-38 $4.978.93 $4,978.93 $4,978.93 $4,978.93 $4,978.93 $4,978.93 $4,978.93 $4,978.93 $4,978.93 $4,978.93 $4,978.93 $4,978.93 $4,978.93 $4,978.93 $4.978.93 $4.978.93 $4,978.93 $4,978.93 $4,978.93 $4,978.93 $4.978.93 $4,978.93 $4,978.93 $2,958.85 $2,020.08 $71,951.09 $2,878.04 $2,100.89 $69,850.21 $2,794.01 $2,184.92 $67,665.29 $2,706.61 $2,272.32 $65,392.97 $2,615.72 $2,363.21 $63,029.75 $2,521.19 $2,457.74 $60,572.02 $2,422.88 $2,556.05 $58,015.97 $2,320.64 $2,658.29 $55,357.67 $2,214.31 $2,764.62 $52,593.05 $2,103.72 $2,875.21 $49.717.84 $1,988.71 $2,990.22 $46,727.63 $1,869.11 $3,109.83 $43,617.80 $1,744.71 $3,234.22 $40,383.58 $1,615.34 $3,363.59 $37,020.00 $1,480.80 $3,498.13 $33,521.87 $1,340.87 $3,638.06 $29,883.81 $1,195.35 $3,783.58 $26,100.23 $1,044.01 $3,934.92 $22,165.31 $886.61 $4,092.32 $18,072.99 $722.92 $4,256.01 $13,816.98 $552.68 $4,426.25 $9,390.73 $375.63 $4,603.30 $4,787.43 $191.50 $4,787.43 $0.00) 46 49 50

A34 A B C F G H I K L M N O W E 110000 0.06 =E412 25 -E6'2 =PMT(E5,E7,-E3,0,0) principal amt rate of interest rate of interest Duration Duration Eml = $G$22 0.08 =K4/2 NEW TERMS principal amt rate of interest rate of interest Duration Duration Ipa semi annually(2) years Semi annual years pa semi annually(2) years Semi annual years $ =E7-B22 =PMT(K5,K7,-K3,0,0) ET = 80 Amortisation schedule on half yearly basis Amortisation schedule on half yearly basis sl no sl no month 1 41640 2 =EOMONTH(C12,6)-30 EMI calculation =E$8 =E$8 Interest amount =E3'$E$5 =G12"$E$5 Principal amount =D12-E12 1 =D13-E13 Closing balance =E3-F12 =G12-F13 month =EOMONTH(C36,6)-30 =EOMONTH(J12,6)-30 EMI calculation = $K$8 = $K$8 Interest amount =G36'$K$5 =N12"$K$5 Principal amount =K12-L12 =K13-L13 Closing balance =G36-M12 =N12-M13 13 6 =E$8 E$8 =E$8 EE$8 =E$8 E$8 =E38 13 =EOMONTH(C13,6)-30 =EOMONTH(C14,6)-30 =EOMONTH(C15,6)-30 =EOMONTH(C16,6)-30 =EOMONTH(C17,6)-30 =EOMONTH(C18,6)-30 =EOMONTH(C19,6)-30 =EOMONTH(C20,6)-30 =EOMONTH(C21,6)-30 =EOMONTH(C22,6)-30 =EOMONTH(C23,6)-30 =EOMONTH(C24,6)-30 EOMONTH(C25,6)-30 =EOMONTH(C26,6)-30 =EOMONTH(C27,6)-30 =EOMONTH(C28,6)-30 =EOMONTH(C29,6)-30 =EOMONTH(C30,6)-30 =EOMONTH(C31,6)-30 =EOMONTH(C32,6)-30 =EOMONTH(C33,6)-30 =EOMONTH(C34,6)-30 =EOMONTH(C35,6)-30 =G13'$E$5 =G14'$E$5 =G15'$E$5 =G16'$E$5 =G17"$E$5 =G18'$E$5 =G19' $E$5 =G20'$E$5 =G21 $E$5 =G22' $K$5 =G23'$K$5 =G24'$K$5 -G25 $K$5 =G26'$K$5 =G27'$K$5 =G28'$K$5 =G29'$K$5 =G30'$K$5 =G31'$K$5 =G32 $K$5 =G33'$K$5 =G34"$K$5 =G35'$K$5 = $K$8 = $K$8 = $K$8 = $K$8 = $K$8 = $K$8 = $K$8 = $K$8 = $K$8 = $K$8 = $K$8 = $K$8 = $K$8 = $K$8 1 =D14-E14 =D15-E15 1 =D16-E16 =D17-E17 1 =D18-E18 =D19-E19 =D20-E20 =D21-E21 1 =D22-E22 =D23-E23 =D24-E24 =D25-E25 =D26-E26 =D27-E27 1 =D28-E28 =D29-E29 1 =D30-E30 =D31-E31 1 =D32-E32 =D33-E33 1 =D34-E34 =D35-E35 =D36-E36 =G13-F14 G14-F15 =G15-F16 =G16-F17 =G17-F18 =G18-F19 =G19-F20 =G20-F21 =G21-F22 =G22-F23 =G23-F24 =G24-F25 -G25-F26 =G26-F27 =G27-F28 -G28-F29 =G29-F30 =G30-F31 -G31-F32 =G32-F33 =G33-F34 =G34-F35 =G35-F36 =EOMONTH(J13,6)-30 =EOMONTH(J14, 6)-30 =EOMONTH(J15,6)-30 =EOMONTH(J16,6)-30 =EOMONTH(J17,6)-30 -EOMONTH(J18,6)-30 =EOMONTH(J19,6)-30 =EOMONTH(J20,6)-30 =EOMONTH(J21,6)-30 =EOMONTH(J22,6)-30 =EOMONTH(J23,6)-30 =EOMONTH(J24,6)-30 =EOMONTH(J25,6)-30 =EOMONTH(J26,6)-30 =EOMONTH(J27,6)-30 =EOMONTH(J28,6)-30 =EOMONTH(J29,6)-30 =EOMONTH(J30,6)-30 =EOMONTH(J31,6)-30 =EOMONTH(J32,6)-30 =EOMONTH(J33,6)-30 =EOMONTH(J34,6)-30 =EOMONTH(J35,6)-30 = $K$8 = $K$8 = $K$8 = $K$8 = $K$8 = $K$8 = $K$8 = $K$8 1 = $K$8 = $K$8 = $K$8 = $K$8 = $K$8 =$K$8 = $K$8 = $K$8 = $K$8 = $K$8 = $K$8 = $K$8 =$K$8 = $K$8 = $K$8 =N13'$K$5 =N14'$K$5 =N15'$K$5 =N16'$K$5 =N17"$K$5 =N18' $K$5 =N19'$K$5 =N20' $K$5 =N21' $K$5 = N22 $K$5 =N23'$K$5 =N24'$K$5 =N25 $K$5 = N26'$K$5 =N27' $K$5 =N28' $K$5 =N29'$K$5 =N30'$K$5 =N31'$K$5 =N32 $K$5 =N33' $K$5 =N34'$K$5 =N35'$K$5 =K14-L14 =K15-L15 =K16-L16 =K17-L17 =K18-L18 =K19-L19 =K20-L20 =K21-L21 =K22-L22 =K23-L23 =K24-L24 =K25-L25 =K26-L26 =K27-L27 =K28-L28 =K29-L29 =K30-L30 =K31-L31 =K32-L32 =K33-L33 =K34-L34 =K35-L35 =K36-L36 =N13-M14 =N14-M15 =N15-M16 =N16-M17 =N17-M18 =N18-M19 =N19-M20 =N20-M21 =N21-M22 =N22-M23 =N23-M24 =N24-M25 =N25-M26 = N26-M27 =N27-M28 =N28-M29 =N29-M30 =N30-M31 =N31-M32 =N32-M33 =N33-M34 =N34-M35 =N35-M36 18 21 22 34 .23 24

The answer aksed in question is coloured in yellow allnitial monthly payment b)Balance after jan 1,19payment =D12 =G22 c)new monthly payment after jan 1,2019 = $D$23 month =C$23 =P18+31 =P19+31 d)September 2019 payment details Emi payment =D23 =D2316 =D24/6 interest =E23 = T18' $K$5/6 =T19'$K$5/6 principal =F23 =Q19-R19 =Q20-R20 closing balance =G23 =T18-S19 = T19-S20