Question

Consider a standard mortgage (360 months) with monthly payments and a nominal rate of 5.00%. What portion of the payments during the first 27 months goes toward interest? 68.97% O 79.93% O 78.25% 76.35% 82.36%

Answer 1

=> We will make a sample loan amortization table with a mortgage amount $ 100,000 ( 360 monthly payments) and interest rate of 5% to answer the first part:

C F G H 1 J K L M D Total Loan amount Interest rate Number of payments monthly interest Size of payment E $ 1,00,000.00 5.00% 360 0.004166667 S 536.82 Payment Number 0 1 2 3 4 5 $ $ $ $ $ $ 6 7 8 $ $ $ $ $ $ $ $ $ Interest Principal Principal Payment portion Portion Balance $ 1,00,000.00 536.82 $ 416.67 $ 120.15 $ 99,879.85 536.82 $ 416.17 $ 120.66 $ 99,759.19 536.82 $ 415.66 $ 121.16 $ 99,638.03 536.82 $ 415.16 $ 121.66 $ 99,516.37 536.82 $ 414.65 $ 122.17 $ 99,394.20 536.82 $ 414.14 $ 122.68 $ 99,271.52 536.82 $ 413.63 $ 123.19 $ 99.148.33 536.82 $ 413.12 $ 123.70 $ 99,024.62 536.82 $ 412.60 $ 124.22 $ 98,900.41 536.82 $ 412.09 $ 124.74 $ 98,775.67 536.82 $ 411.57 $ 125.26 $ 98,650.41 536.82 $ 411.04 $ 125.78 $ 98,524.63 536.82 $ 410.52 $ 126.30 $ 98,398.33 536.82 $ 409.99 $ 126.83 $ 98,271.50 536.82 $ 409.46 $ 127.36 $ 98,144.15 536.82 $ 408.93 $ 127.89 $ 98,016.26 536.82 $ 408.40 $ 128.42 $ 97,887.84 536.82 $ 407.87 $ 128.96 $ 97,758.88 536.82 $ 407.33 $ 129.49 $ 97,629.39 536.82 $ 406.79 $ 130.03 $ 97,499.36 536.82 $ 406.25 $ 130.57 $ 97,368.78 536.82 $ 405.70 $ 131.12 $ 97,237.66 536.82 $ 405.16 $ 131.66 $ 97,106.00 536.82 $ 404.61 $ 132.21 $ 96,973.79 536.82 $ 404.06 $ 132.76 $ 96,841.02 536.82 $ 403.50 $ 133.32 $ 96,707.71 536.82 $ 402.95 $ 133.87 $ 96,573.83 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 $ $ $ $ $ $ $ $ $ $ $ $ S 14,494.18 S 11,068.02

=> We can see after 27 payments, total payment amount = $ 14,494.18 and total interest payment = $ 11,068.02:

* Therefore the answer = [(11,068.02/14,494.18)*100] = 76.35 %

Formula used in excel:

C D E F G H K L M 3 4 5 6 7 8 Total Loan amount Interest rate Number of payments monthly interest 100000 0.05 360 =E6/12 Size of payment = PMT(E8.E7,15) Payment Number Payment Interest portion Principal Portion 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 0 1 =111+1 =112+1 =113+1 =114+1 =115+1 =116+1 =117+1 =118+1 =119+1 =120+1 =121+1 =122+1 =123+1 124+1 =125+1 =126+1 =127+1 =128+1 =129+1 =130+1 =131+1 =132+1 =133+1 =134+1 =135+1 =136+1 =SESO =SESO =SESO =SESO =SESO =SES9 =SESE =SES =SESE =SESO =SESO =SESE =SESE =SESO =SESE =SESE =SESO =SES =SES =SES =SESO =SES =SES9 =SESO =SESO =SES9 =SES9 =E8*ES =SESS*M11 =SESS*M12 =SES8*M13 =SES8*M14 =SES8*M15 =SESS*M16 =SES8*M17 =SESS*M18 =SESS*M19 =SES8*M20 =SES8*M21 =SES8*M22 =SESS*M23 =SES8*M24 =SES8*M25 =SES8*M26 =SESS*M27 =SESS*M28 =SES8*M29 =SES8*M30 =SESS*M31 =SES8*M32 =SESS*M33 =SES8*M34 =SESS*M35 =SES8*M36 =J11-K11 =J12-K12 =J13-K13 =J14-K14 =J15-K15 =J16-K16 =J17-K17 =J18-K18 =J19-K19 =J20-K20 =J21-K21 =J22-K22 =J23-K23 =J24-K24 =J25-K25 =J26-K26 =J27-K27 =J28-K28 =J29-K29 =J30-K30 =J31-K31 =J32-K32 =J33-K33 =J34-K34 =J35-K35 =J36-K36 =J37-K37 Principal Balance =ES =M10-L11 =M11-L 12 =M12-L13 =M13-L 14 =M14-L15 =M15-L 16 =M16-L17 =M17-L18 =M18-L 19 =M19-L20 =M20-L21 =M21-L 22 =M22-L23 =M23-L24 =M24-L25 =M25-L26 =M26-L27 =M27-L28 =M28-L29 =M29-L30 =M30-L31 =M31-L32 =M32-L33 =M33-L34 =M34-L35 =M35-L36 =M36-L37 =SUM(J11:J37) =SUM(K11:K37)

=> Now we will make another loan amortization table with a mortgage amount $ 225,000 ( 360 monthly payments) and interest rate of 6.90% to answer the second part:

A B C F G H 1 J K L M 5 6 7 8 D Total Loan amount Interest rate Number of payments monthly interest Size of payment E $ 2,25,000.00 6.90% 360 0.00575 1,481.85 $ 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 Payment Number 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 Interest Payment Principal Principal portion Portion Balance $ 2,25,000.00 $ 1,481.85 $ 1.293.75 $ 188.10 $ 2,24,811.90 $ 1,481.85 $ 1.292.67 $ 189.18 $ 2,24,622.72 $ 1,481.85 $ 1,291.58 $ 190.27 $2,24,432.45 1,481.85 $ 1,290.49 $ 191.36 $ 2,24,241.08 $ 1,481.85 $ 1,289.39 $ 192.46 $2,24,048,62 $ 1,481.85 $ 1,288.28 $ 193.57 $2,23,855.05 $ 1,481.85 $ 1,287.17 $ 194.68 $ 2,23,660.37 $ 1,481.85 $ 1,286.05 $ 195.80 $2,23,464.56 $ 1,481.85 $ 1,284.92 $ 196.93 $ 2,23,267.63 $ 1,481.85 $ 1,283.79 $ 198.06 $ 2,23,069.57 $ 1,481.85 $ 1,282.65 $ 199.20 $2,22,870.37 $ 1,481.85 $ 1,281.50 $ 200.35 $ 2,22,670.03 $ 1,481.85 $ 1,280.35 $ 201.50 $2,22,468.53 $ 1,481.85 $ 1,279.19 $ 202.66 $2,22,265.87 $ 1,481.85 $ 1,278.03 $ 203.82 $2,22,062.05 $ 1,481.85 $ 1,276.86 $ 204.99 $ 2,21,857.06 $ 1,481.85 $ 1,275.68 $ 206.17 $ 2,21,650.89 $ 1,481.85 $ 1,274.49 $ 207.36 $ 2,21,443.53 $ 1,481.85 $ 1,273.30 $ 208.55 $ 2,21,234.98 $ 1,481.85 $ 1,272.10 $ 209.75 $ 2,21,025.23 $ 1,481.85 $ 1,270.90 $ 210.96 $ 2,20,814.27 $ 1,481.85 $ 1,269.68 $ 212.17 $ 2,20,602.10 $ 1,481.85 $ 1,268.46 $ 213.39 $2,20,388.72 $ 1,481.85 $ 1,267.24 $ 214.62 $ 2,20.174.10 $ 1,481.85 $ 1,266.00 $ 215.85 $ 2.19,958.25 $ 1,481.85 $ 1,264.76 $ 217.09 $ 2.19,741.16 $ 1,481.85 $ 1,263.51 $ 218.34 $ 2.19,522.82 $ 1,481.85 $ 1,262.26 $ 219.59 $ 2.19,303.23 $ 1,481.85 $ 1,260.99 $ 220.86 $ 2.19,082.37 $ 1,481.85 $ 1,259.72 $ 222.13 $ 2,18,860.25 $ 1,481.85 $ 1,258.45 $ 223.40 $ 2,18,636.84 $ 1,481.85 $ 1,257.16 $ 224.69 $ 2,18,412.15 $ 1,481.85 $ 1,255.87 $ 225.98 $ 2.18.186.17 $ 1,481.85 $ 1,254.57 $ 227.28 $ 2,17,958.89 $ 1,481.85 $ 1,253.26 $ 228.59 $ 2,17,730.31 $ 1,481.85 $ 1,251.95 $ 229.90 $ 2.17,500.41 $ 1,481.85 $ 1,250.63 $ 231.22 $ 2.17.269.18 $ 1,481.85 $ 1,249.30 $ 232.55 $ 2.17,036.63 $ 1,481.85 $ 1,247.96 $ 233.89 $ 2,16,802.74 $ 1,481.85 $ 1,246.62 $ 235.23 $ 2,16,567.51 $ 1,481.85 $ 1,245.26 $ 236.59 $ 2.16,330.92 $ 1,481.85 $ 1,243.90 $ 237.95 $ 2.16,092.97 $ 1,481.85 $ 1,242.53 $ 239.32 $ 2,15,853.66 $ 1481 85 $ 1241 16$ 240 69 $ 215 612 96

A B 二 口 E F G H - 55 56 45 46 47 48 49 0 51 品 59 ED 1 2 3 4 的 6 了 68 69 53 4 5 56 7 58 59 0 1 之 3 4 巧 63 4 65 67 8 9 J K L M $ 1481.85 $ 1233. $ 242.0g $25.370.9 $ 1.481.85 $ 1.228.38 $ 243.47 $ 2.5.7.42 $ 14815 $ 12.36.8 $ 244.7 $214.255 本 1.481.85 $ 1235.7 $ 246.28 $2.14.636.28 $ 1481.85 $ 1234.16 $ 247.9 $2.14.38.8 本 1.481.5 $ 12323 本 249.2 $ 214.147 $ 14815 本 1.231.30 $ 250.55 $28.92 $ 1481.5 $ 1229.6 $ 2519 本2.3.636.93 $ 1481 85 $ 1228.41 $253.44 $2383.49 事 1481.85 本 1.226.96 $ 254.如本 2.13.128.60 $ 14815 $ 1225.49 $ 256.36 22,87224 本 1.481.85 $ 1224.2 $ 27.3 $2.12.614.40 $ 1481.85 $ 1222.53 $ 259.32 $2.12.355.09 $ 1.481.5 $ 1221.04 本 260.81 $2.2.094.28 事 1481.85 $129.54 本 262.31 本 2.1317 本 1.481.85 $ 1218.03 $ 263.2 $ 2.11.568.5 本 1.481.85 本 1.216.52 $ 265.33 $ 2.11.302.82 $ 14831.5 $ 1214.9 $ 266.6 $ 2.11.035.6 $ 1481.5 $ 1213.46 $ 28. 本 $ 2.10.767.57 $ 1.481.5 本 1.211.91 $ 269.94 $ 2.10.497.63 $ 1.481.5 1.210.36 本 271.49 $ 2.0.226. 4 $ 14831.5 $ 1208.0 $ 273.05 $2.09.953.09 $ 1481.5 $ 1207.23 $ 274.2 本 $ 2.09.68.47 $ 1481.85 $ 1205.65 276.20 $2.09.402.27 本 1.481.85 $ 1204.06 $ 27. $ 2.09.24.48 本 1.481.85 $ 1202.47 $ 279 8 $ 2.08.845.10 $ 1481.85 $ 12006 $ 280.9 $ 2.08.564.11 本 1.481.85 $ 19 24 本 282.61 本 2.08.281.50 本 1.481.85 $ 17.2 $ 284.23 $27997.27 本 1.481.85 $1195.98 $ 285.7 $ 27.11.40 本 1.481.5 本 1.194.34 本 287.51 $27423.89 $ 1.481.5 本 1929 中 29. 6 本 2.7.134.73 $ 1.481.5 中 1.191.02 $ 290 83 $ 2.06.843.91 本 1.481.85 本 1.35 $ 92.50 $ 2.06.551.41 本 1.481.5 $1187.67 本 294.8 2.06.257.23 本 1.481.85 $ 185.98 $ 295.7 $2.05.961.36 $ 1481.85 $ 184.28 $ 297.57 $2.05.663. 本 1.481.85 $1182.7 $ 99.28 $25.364.50 $ 1481.85 $ 1.10.85 本 01.0 本2.05 063.50 本 1.481.85 本 1.19.2 $ 32.4 $2.04. L. 6 $ 1481.85 $ 117.37 304.48 $2.04.456.29 本 1.481.85 本 1.175.2 $ 306.23 $ 2.4.150.06 本 1.481.85 本 1.173.6 $ 307.9 $23.842.07 本 1.481.85 本 1172.9 $300.6 $ 2.3.532.31 $ 1.481.5 中 1.10.31 本 311.54 本 $ 2.03.220 $ 1481.85 $ 1168.52 本 313.33 $2.02.07.44 本 1.481.85 $ 1166.2 本 315.13 $2.02.592.31 $ 1.481.5 中 1.164.91 中 36.94 $22.275.37 $ 1.481.5 主 163.8 中 31.7 $ 2.01.956.60 1.481.5 本 1161.25 $320.60 $2.1.636.00 宋1481號 1159 41 2244 宋71313.56 1 了? 3 4 5 了 沒 沒 的 引 配 3 與 5 6 7 品 的 90 1 2 3 4 95 6 97 98 9 100 01 U2 0G 04 05 了 的 引 2 83 4 5 6 7 8 99 91 92 93 94 195

=> We can see remaining balance after 95 payments is close to $ 201,315:

* Therefore the answer is 201,315

Formula used in excel:

B C D E F G H 1 J K L M 1 2 3 4 5 6 7 8 Total Loan amount Interest rate Number of payments monthly interest Size of payment 225000 0.069 360 =E6/12 =-PMT(E8.E7,E5) Payment Number Payment Interest portion Principal Portion 0 1 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 Γη =111+1 =112+1 =113+1 =114+1 =115+1 =116+1 =117+1 = 118+1 =119+1 =120+1 =121+1 =122+1 =123+1 =124+1 =125+1 =126+1 =127+1 =128+1 =129+1 =130+1 =131+1 =132+1 =133+1 =134+1 =135+1 =136+1 =137+1 =138+1 =139+1 =140+1 =141+1 =142+1 =143+1 =144+1 =145+1 =146+1 =147+1 =148+1 101 = $E$9 = $E$9 = $E$9 = $E$9 = $E$9 = $E$9 =$E$9 = $E$9 = $E$9 = $E$9 = $E$9 = $E$9 = $E$9 = $E$9 =$E$9 = $E$9 = $E$9 = $E$9 = $E$9 = $E$9 = $E$9 =$E$9 = $E$9 = $E$9 = $E$9 = $E$9 = $E$9 = $E$9 = $E$9 = $E$9 = $E$9 = $E$9 = $E$9 = $E$9 = $E$9 = $E$9 = $E$9 =$E$9 = $E$9 =E8-E5 = $E$8 M11 = $E$8*M12 = $E$8 M13 = $E$8 M14 = $E$8*M15 =$E$8 M16 = $E$8*M17 = $E$8 M18 = $E $8 M19 = $E$8 M20 = $E$8M21 = $E$8 M22 = $E$8*M23 = $E$8 M24 = $E$8 M25 =$E$8 M26 = $E $8 M27 = $E$8 M28 = $E$8 M29 = $E$8*M30 = $E$8 M31 = $E$8 M32 = $E$8 M33 = $E$8 M34 = $E$8*M35 = $E$8 M36 = $E$8 M37 = $E$8 M38 = $E $8 M39 = $E$8*M40 =$E$8-M41 = $E$8 M42 = $E$8*M43 = $E$84M44 = $E$8 M45 = $E$8*M46 = $E$8 M47 = $E$8-M48 trton 10 =J11-K11 = J12-K12 =J13-K13 = J14-K14 = J15-K15 = J16-K16 =J17-K17 =J18-K 18 = J19-K19 = J20-K20 =J21-K21 = J22-K22 = J23-K23 = J24-K24 =J25-K25 =J26-K26 = J27-K27 =J28-K28 =J29-K29 =J30-K30 =J31-K31 = J32-K32 =J33-K33 = J34-K34 = J35-K35 =J36-K36 = J37-K37 =J38-K38 =J39-K39 = J40-K40 = J41-K41 = J42-K42 = J43-K43 =J44-K44 =J45-K45 =J46-K46 =J47-K47 =J48-K48 =J49-K49 ICO VEO Principal Balance =E5 =M10-L11 =M11-L 12 =M12-L13 =M13-L14 =M14-L15 =M15-L16 =M16-L17 =M17-L18 =M18-L 19 =M19-L20 =M20-L21 =M21-L22 =M22-L23 =M23-L24 =M24-L25 =M25-L26 EM26-L27 =M27-L28 =M28-L29 =M29-L30 =M30-L31 =M31-L32 =M32-L33 =M33-L34 =M34-L35 =M35-L 36 =M36-L37 =M37-L38 =M38-L39 =M39-L40 =M40-L41 =M41-L42 =M42-L43 =M43-L44 =M44-L45 =M45-L46 =M46-L47 =M47-L48 =M48-L49 tran bi

=> Now, we will make another sample loan amortization table with a mortgage amount $ 100,000 ( 360 monthly payments) and interest rate of 5.70% to answer the third part:

A B с F G H 1 J K L M S 5 6 7 8 D Total Loan amount Interest rate Number of payments monthly interest Size of payment E 1,00,000.00 5.70% 360 0.00475 $ 580.40 Payment Number 9 Payment 1 2 3 4 n S S S S S S S $ S S 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 S S S S S S S S S S S 580.40 $ 580.40 S 580.40 $ 580.40 S 580.40 S 580.40 $ 580.40 S 580.40 S 580.40 S 580.40 S 580.40 S 580.40 S 580.40 S 580.40 S 580.40 S 580.40 $ 580.40 S 580.40 S 580.40 S 580.40 S 580.40 S 580.40 S 580.40 S 580.40 S 580.40 S 580.40 S 580.40 S 580.40 S 580.40 S 580.40 $ Interest Principal Principal portion Portion Balance $ 1,00,000.00 475.00 $ 105.40 S 99,894.60 474.50 S 105.90 S 99,788.70 474.00 $ 106.40 S 99,682.29 473.49 $ 106.91 $ 99,575.38 472.98 S 107.42 S 99,467.97 472.47 S 107.93 $ 99,360.04 471.96 $ 108.44 $ 99,251.60 471.45 S 108.96 $ 99.142.64 470.93 S 109.47 S 99,033.17 470.41 S 109.99 $ 98,923.18 469.89 $ 110.52 S 98,812.66 469.36 S 111.04 S 98,701.62 468.83 S 111.57 S 98,590.06 468.30 S 112.10 $ 98,477.96 467.77 S 112.63 $ 98,365.33 467.24 S 113.17 S 98.252.16 466.70 S 113.70 S 98,138.46 466.16 S 114.24 $ 98,024.22 465.62 S 114.79 S 97,909.43 465.07 S 115.33 S 97,794.10 464.52 S 115.88 S 97,678.22 463.97 $ 116.43 $ 97,561.79 463.42 $ 116.98 S 97,444.81 462.86 $ 117.54 S 97,327.27 462.30 $ 118.10 S 97,209.18 461.74 S 118.66 S 97,090.52 461.18 $ 119.22 S 96,971.30 460.61 S 119.79 $ 96,851.51 460.04 S 120.36 S 96,731.16 459.47 S 120.93 96,610.23 S S S S S S S S $ 17,412.01 $ 14,022.24

=> We can see after 30 payments, total payment amount = $ 17,412.01 and total interest payment = $ 14,022.24:

* Therefore the answer = [(14,022.24/17,412.01)*100] = 80.53 %

Formula used in excel:

C D E F G H 1 K L M 5 6 7 8 Total Loan amount Interest rate Number of payments monthly interest Size of payment 100000 0.057 360 =E6/12 =-PMT(E8.E7,E5) Payment Number Payment Interest portion Principal Portion 0 1 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 =111+1 =112+1 =113+1 =114+1 =115+1 =116+1 =117+1 =118+1 =119+1 =120+1 =121+1 =122+1 =123+1 =124+1 =125+1 =126+1 =127+1 =128+1 =129+1 =130+1 =131+1 =132+1 =133+1 =134+1 =135+1 =136+1 =137+1 =138+1 =I39+1 =SESE =SESO =SESE =SES =SES9 =SESE =SESS =SESO =SESE =SESE =SES9 =SESE =SESO =SESO =SESE =SESO =SESO =SES9 =SESE =SESE =SESO =SES =SESE =SESE =SESO =SESO =SESE =SESO =SESE =SESE =E8*E5 =SESS*M11 =SES8*M12 =SES8*M13 =SES8*M14 =SES8*M15 =SES8*M16 =SESSM17 =SES8*M18 =SES8*M19 =SE$8*M20 =SES8*M21 =SESS*M22 =SES8*M23 =SES8*M24 =SES8*M25 =SES8*M26 =SES8*M27 =SES8*M28 =SES8*M29 =SESS*M30 =SES8*M31 =SESS*M32 =SES8*M33 =SES8*M34 =SES8*M35 =SES8*M36 =SESSM37 =SES8*M38 =SESS*M39 =J11-K11 =J12-K12 =J13-K13 =J14-K14 =J15-K15 =J16-K16 =J17-K17 =J18-K18 =J19-K19 =J20-K20 =J21-K21 =J22-K22 =J23-K23 =J24-K24 =J25-K25 =J26-K26 =J27-K27 =J28-K28 =J29-K29 =J30-K30 =J31-K31 =J32-K32 =J33-K33 =J34-K34 =J35-K35 =J36-K36 =J37-K37 =J38-K38 =J39-K39 =J40-K40 Principal Balance =ES =M10-L 11 =M11-L 12 =M12-L13 =M13-L 14 =M14-L 15 =M15-L 16 =M16-L17 =M17-L18 =M18-L 19 =M19-L20 =M20-L21 =M21-L22 =M22-L23 =M23-L24 =M24-L25 =M25-L26 =M26-L27 =M27-L28 =M28-L29 =M29-L30 =M30-L31 =M31-L32 =M32-L33 =M33-L34 =M34-L35 =M35-L36 =M36-L37 =M37-L38 =M38-L39 =M39-L 40 =SUM(J11:J40) =SUM(K11:K40)