ADVANCED PROBLEMS FOR SPREADSHEET APPLICATION 1. Monthly amortization schedule.

Question

ADVANCED PROBLEMS FOR SPREADSHEET APPLICATION 1. Monthly amortization schedule. Sherry and Sam want to purchase a condo at the coast. They will spend $650,000 on the condo and are taking out a loan for the whole amount for the condo for twenty years at 7.0% interest. a. What is the monthly payment on the mortgage? Construct the amortization of the loan for the twenty years in a spreadsheet to show the interest cost, the principal reduction, and the ending balance each month. b. Then change the amortization to reflect that after ten years, Sherry and Sam will increase their monthly payment to $7,500 per month. When will they fully repay the mortgage with this increased payment if they apply all the extra dollars above the original payment to the principal? ADVANCED PROBLEMS FOR SPREADSHEET APPLICATION 1. Monthly amortization schedule. Sherry and Sam want to purchase a condo at the coast. They will spend $650,000 on the condo and are taking out a loan for the whole amount for the condo for twenty years at 7.0% interest. a. What is the monthly payment on the mortgage? Construct the amortization of the loan for the twenty years in a spreadsheet to show the interest cost, the principal reduction, and the ending balance each month. b. Then change the amortization to reflect that after ten years, Sherry and Sam will increase their monthly payment to $7,500 per month. When will they fully repay the mortgage with this increased payment if they apply all the extra dollars above the original payment to the principal?

Explanation / Answer

Interest is calculated using the formula

Present value = 650000

R = 0.07/12 = 0.005833

N = 12*20 = 240

= (650000*0.005833 * (1+0.005833)240)/((1+0.005833)240-1)

= 5039.443

Amortization table

3. Amortization table, make the changes to above table.

In this case, the loan is fully paid by 191th installment.

Principal 650000 No. of Payments 240 Interest rate per month 0.005833 Monthly payment 5039.443 Total payment in 20years 1209466 Total interest paid 559466.3 Payment Total Amount to Amount to Balance Number Payment Interest Principal $650,000.00 1 5039.443 3791.667 1247.776 648752.2 2 5039.443 3784.388 1255.055 647497.2 3 5039.443 3777.067 1262.376 646234.8 4 5039.443 3769.703 1269.74 644965.1 5 5039.443 3762.296 1277.147 643687.9 6 5039.443 3754.846 1284.597 642403.3 7 5039.443 3747.353 1292.09 641111.2 8 5039.443 3739.815 1299.628 639811.6 9 5039.443 3732.234 1307.209 638504.4 10 5039.443 3724.609 1314.834 637189.5 11 5039.443 3716.939 1322.504 635867 12 5039.443 3709.224 1330.219 634536.8 13 5039.443 3701.465 1337.978 633198.8 14 5039.443 3693.66 1345.783 631853.1 15 5039.443 3685.81 1353.634 630499.4 16 5039.443 3677.913 1361.53 629137.9 17 5039.443 3669.971 1369.472 627768.4 18 5039.443 3661.982 1377.461 626391 19 5039.443 3653.947 1385.496 625005.5 20 5039.443 3645.865 1393.578 623611.9 21 5039.443 3637.736 1401.707 622210.2 22 5039.443 3629.559 1409.884 620800.3 23 5039.443 3621.335 1418.108 619382.2 24 5039.443 3613.063 1426.38 617955.8 25 5039.443 3604.742 1434.701 616521.1 26 5039.443 3596.373 1443.07 615078 27 5039.443 3587.955 1451.488 613626.6 28 5039.443 3579.488 1459.955 612166.6 29 5039.443 3570.972 1468.471 610698.1 30 5039.443 3562.406 1477.037 609221.1 31 5039.443 3553.79 1485.653 607735.4 32 5039.443 3545.123 1494.32 606241.1 33 5039.443 3536.407 1503.037 604738.1 34 5039.443 3527.639 1511.804 603226.3 35 5039.443 3518.82 1520.623 601705.7 36 5039.443 3509.95 1529.493 600176.2 37 5039.443 3501.028 1538.415 598637.7 38 5039.443 3492.054 1547.39 597090.4 39 5039.443 3483.027 1556.416 595533.9 40 5039.443 3473.948 1565.495 593968.4 41 5039.443 3464.816 1574.627 592393.8 42 5039.443 3455.631 1583.812 590810 43 5039.443 3446.392 1593.051 589217 44 5039.443 3437.099 1602.344 587614.6 45 5039.443 3427.752 1611.691 586002.9 46 5039.443 3418.35 1621.093 584381.8 47 5039.443 3408.894 1630.549 582751.3 48 5039.443 3399.382 1640.061 581111.2 49 5039.443 3389.815 1649.628 579461.6 50 5039.443 3380.193 1659.25 577802.3 51 5039.443 3370.514 1668.929 576133.4 52 5039.443 3360.778 1678.665 574454.7 53 5039.443 3350.986 1688.457 572766.3 54 5039.443 3341.137 1698.306 571068 55 5039.443 3331.23 1708.213 569359.8 56 5039.443 3321.265 1718.178 567641.6 57 5039.443 3311.243 1728.2 565913.4 58 5039.443 3301.161 1738.282 564175.1 59 5039.443 3291.021 1748.422 562426.7 60 5039.443 3280.822 1758.621 560668.1 61 5039.443 3270.564 1768.879 558899.2 62 5039.443 3260.245 1779.198 557120 63 5039.443 3249.867 1789.576 555330.4 64 5039.443 3239.427 1800.016 553530.4 65 5039.443 3228.927 1810.516 551719.9 66 5039.443 3218.366 1821.077 549898.8 67 5039.443 3207.743 1831.7 548067.1 68 5039.443 3197.058 1842.385 546224.7 69 5039.443 3186.311 1853.132 544371.6 70 5039.443 3175.501 1863.942 542507.6 71 5039.443 3164.628 1874.815 540632.8 72 5039.443 3153.692 1885.752 538747.1 73 5039.443 3142.691 1896.752 536850.3 74 5039.443 3131.627 1907.816 534942.5 75 5039.443 3120.498 1918.945 533023.6 76 5039.443 3109.304 1930.139 531093.4 77 5039.443 3098.045 1941.398 529152 78 5039.443 3086.72 1952.723 527199.3 79 5039.443 3075.329 1964.114 525235.2 80 5039.443 3063.872 1975.571 523259.6 81 5039.443 3052.348 1987.095 521272.5 82 5039.443 3040.756 1998.687 519273.8 83 5039.443 3029.097 2010.346 517263.5 84 5039.443 3017.37 2022.073 515241.4 85 5039.443 3005.575 2033.868 513207.6 86 5039.443 2993.711 2045.732 511161.8 87 5039.443 2981.777 2057.666 509104.2 88 5039.443 2969.774 2069.669 507034.5 89 5039.443 2957.701 2081.742 504952.7 90 5039.443 2945.558 2093.885 502858.9 91 5039.443 2933.343 2106.1 500752.8 92 5039.443 2921.058 2118.385 498634.4 93 5039.443 2908.701 2130.743 496503.6 94 5039.443 2896.271 2143.172 494360.5 95 5039.443 2883.769 2155.674 492204.8 96 5039.443 2871.195 2168.249 490036.5 97 5039.443 2858.546 2180.897 487855.6 98 5039.443 2845.825 2193.619 485662 99 5039.443 2833.028 2206.415 483455.6 100 5039.443 2820.158 2219.285 481236.3 101 5039.443 2807.212 2232.231 479004.1 102 5039.443 2794.191 2245.253 476758.8 103 5039.443 2781.093 2258.35 474500.5 104 5039.443 2767.92 2271.524 472229 105 5039.443 2754.669 2284.774 469944.2 106 5039.443 2741.341 2298.102 467646.1 107 5039.443 2727.936 2311.508 465334.6 108 5039.443 2714.452 2324.991 463009.6 109 5039.443 2700.889 2338.554 460671 110 5039.443 2687.248 2352.195 458318.8 111 5039.443 2673.527 2365.917 455952.9 112 5039.443 2659.725 2379.718 453573.2 113 5039.443 2645.844 2393.599 451179.6 114 5039.443 2631.881 2407.562 448772 115 5039.443 2617.837 2421.606 446350.4 116 5039.443 2603.711 2435.732 443914.7 117 5039.443 2589.502 2449.941 441464.8 118 5039.443 2575.211 2464.232 439000.5 119 5039.443 2560.836 2478.607 436521.9 120 5039.443 2546.378 2493.065 434028.9 121 5039.443 2531.835 2507.608 431521.3 122 5039.443 2517.207 2522.236 428999 123 5039.443 2502.494 2536.949 426462.1 124 5039.443 2487.695 2551.748 423910.3 125 5039.443 2472.81 2566.633 421343.7 126 5039.443 2457.838 2581.605 418762.1 127 5039.443 2442.779 2596.664 416165.4 128 5039.443 2427.632 2611.811 413553.6 129 5039.443 2412.396 2627.047 410926.6 130 5039.443 2397.072 2642.371 408284.2 131 5039.443 2381.658 2657.785 405626.4 132 5039.443 2366.154 2673.289 402953.1 133 5039.443 2350.56 2688.883 400264.2 134 5039.443 2334.875 2704.568 397559.7 135 5039.443 2319.098 2720.345 394839.3 136 5039.443 2303.229 2736.214 392103.1 137 5039.443 2287.268 2752.175 389350.9 138 5039.443 2271.214 2768.229 386582.7 139 5039.443 2255.066 2784.377 383798.3 140 5039.443 2238.824 2800.62 380997.7 141 5039.443 2222.487 2816.956 378180.7 142 5039.443 2206.054 2833.389 375347.4 143 5039.443 2189.526 2849.917 372497.4 144 5039.443 2172.902 2866.541 369630.9 145 5039.443 2156.18 2883.263 366747.6 146 5039.443 2139.361 2900.082 363847.6 147 5039.443 2122.444 2916.999 360930.6 148 5039.443 2105.428 2934.015 357996.5 149 5039.443 2088.313 2951.13 355045.4 150 5039.443 2071.098 2968.345 352077.1 151 5039.443 2053.783 2985.66 349091.4 152 5039.443 2036.367 3003.077 346088.3 153 5039.443 2018.849 3020.595 343067.7 154 5039.443 2001.228 3038.215 340029.5 155 5039.443 1983.506 3055.938 336973.6 156 5039.443 1965.679 3073.764 333899.8 157 5039.443 1947.749 3091.694 330808.1 158 5039.443 1929.714 3109.729 327698.4 159 5039.443 1911.574 3127.869 324570.5 160 5039.443 1893.328 3146.115 321424.4 161 5039.443 1874.976 3164.467 318259.9 162 5039.443 1856.516 3182.927 315077 163 5039.443 1837.949 3201.494 311875.5 164 5039.443 1819.274 3220.169 308655.4 165 5039.443 1800.49 3238.954 305416.4 166 5039.443 1781.596 3257.847 302158.6 167 5039.443 1762.592 3276.852 298881.7 168 5039.443 1743.477 3295.966 295585.7 169 5039.443 1724.25 3315.193 292270.5 170 5039.443 1704.911 3334.532 288936 171 5039.443 1685.46 3353.983 285582 172 5039.443 1665.895 3373.548 282208.5 173 5039.443 1646.216 3393.227 278815.3 174 5039.443 1626.422 3413.021 275402.2 175 5039.443 1606.513 3432.93 271969.3 176 5039.443 1586.488 3452.955 268516.3 177 5039.443 1566.345 3473.098 265043.2 178 5039.443 1546.086 3493.357 261549.9 179 5039.443 1525.708 3513.735 258036.2 180 5039.443 1505.211 3534.232 254501.9 181 5039.443 1484.595 3554.849 250947.1 182 5039.443 1463.858 3575.585 247371.5 183 5039.443 1443 3596.443 243775 184 5039.443 1422.021 3617.422 240157.6 185 5039.443 1400.919 3638.524 236519.1 186 5039.443 1379.695 3659.748 232859.4 187 5039.443 1358.346 3681.097 229178.3 188 5039.443 1336.873 3702.57 225475.7 189 5039.443 1315.275 3724.168 221751.5 190 5039.443 1293.551 3745.893 218005.6 191 5039.443 1271.699 3767.744 214237.9 192 5039.443 1249.721 3789.722 210448.2 193 5039.443 1227.614 3811.829 206636.3 194 5039.443 1205.379 3834.064 202802.3 195 5039.443 1183.013 3856.43 198945.8 196 5039.443 1160.517 3878.926 195066.9 197 5039.443 1137.89 3901.553 191165.4 198 5039.443 1115.131 3924.312 187241 199 5039.443 1092.239 3947.204 183293.8 200 5039.443 1069.214 3970.229 179323.6 201 5039.443 1046.054 3993.389 175330.2 202 5039.443 1022.76 4016.683 171313.5 203 5039.443 999.329 4040.114 167273.4 204 5039.443 975.7616 4063.681 163209.7 205 5039.443 952.0568 4087.386 159122.4 206 5039.443 928.2138 4111.229 155011.1 207 5039.443 904.2316 4135.211 150875.9 208 5039.443 880.1095 4159.334 146716.6 209 5039.443 855.8467 4183.596 142533 210 5039.443 831.4424 4208.001 138325 211 5039.443 806.8958 4232.547 134092.4 212 5039.443 782.2059 4257.237 129835.2 213 5039.443 757.372 4282.071 125553.1 214 5039.443 732.3933 4307.05 121246.1 215 5039.443 707.2688 4332.174 116913.9 216 5039.443 681.9978 4357.445 112556.5 217 5039.443 656.5794 4382.864 108173.6 218 5039.443 631.0127 4408.43 103765.2 219 5039.443 605.2968 4434.146 99331.02 220 5039.443 579.431 4460.012 94871.01 221 5039.443 553.4142 4486.029 90384.98 222 5039.443 527.2457 4512.197 85872.78 223 5039.443 500.9246 4538.519 81334.26 224 5039.443 474.4499 4564.993 76769.27 225 5039.443 447.8207 4591.622 72177.65 226 5039.443 421.0363 4618.407 67559.24 227 5039.443 394.0956 4645.348 62913.89 228 5039.443 366.9977 4672.445 58241.45 229 5039.443 339.7418 4699.701 53541.75 230 5039.443 312.3269 4727.116 48814.63 231 5039.443 284.752 4754.691 44059.94 232 5039.443 257.0163 4782.427 39277.51 233 5039.443 229.1188 4810.324 34467.19 234 5039.443 201.0586 4838.384 29628.81 235 5039.443 172.8347 4866.608 24762.2 236 5039.443 144.4461 4894.997 19867.2 237 5039.443 115.892 4923.551 14943.65 238 5039.443 87.17128 4952.272 9991.377 239 5039.443 58.28303 4981.16 5010.217 240 5039.443 29.22626 5010.217 3.78E-09

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.