Create an amortization table for a 25-year mortgage loan that has monthly payments. The values in the input range should be loan face value ($500,000), APR (4.5%), and monthly prepayment amount ($100). Make sure the spreadsheet is fully interactive, so the entire sheet correctly updates when a change in any input is made
Add a column to the right of the table developed in (a), to keep track of the month that the loan is paid off after taking prepayments into account. Also, just beneath the input range create a cell that automatically updates the payoff time
Loan Amount 500000
Scheduled Payment
2779
Annual Interest Rate 0.045
Scheduled Number of Payments
300
Loan Period in Years 25
Actual Number of Payments
282
Number of Payments Per Year 12
Total Early Payments
28200
Start Date of Loan 01-01-2020
Total Interest
310251
Payment Date Beginning Balance Scheduled
Payment Extra Payment Principal
Interest Cumulative Interest
01-02-2020 500000 2779
100 1004 1875 1875
01-03-2020 498996 2779
100 1008 1871 3746
01-04-2020 497988 2779
100 1012 1867 5614
01-05-2020 496976 2779
100 1016 1864 7477
01-06-2020 495961 2779
100 1019 1860 9337
01-07-2020 494941 2779
100 1023 1856 11193
01-08-2020 493918 2779
100 1027 1852 13045
01-09-2020 492891 2779
100 1031 1848 14894
01-10-2020 491860 2779
100 1035 1844 16738
01-11-2020 490826 2779
100 1039 1841 18579
01-12-2020 489787 2779
100 1042 1837 20416
01-01-2021 488745 2779
100 1046 1833 22248
01-02-2021 487698 2779
100 1050 1829 24077
01-03-2021 486648 2779
100 1054 1825 25902
01-04-2021 485594 2779
100 1058 1821 27723
01-05-2021 484536 2779
100 1062 1817 29540
01-06-2021 483474 2779
100 1066 1813 31353
01-07-2021 482407 2779
100 1070 1809 33162
01-08-2021 481337 2779
100 1074 1805 34967
01-09-2021 480263 2779
100 1078 1801 36768
01-10-2021 479185 2779
100 1082 1797 38565
01-11-2021 478103 2779
100 1086 1793 40358
01-12-2021 477016 2779
100 1090 1789 42147
01-01-2022 475926 2779
100 1094 1785 43932
01-02-2022 474832 2779
100 1099 1781 45712
01-03-2022 473733 2779
100 1103 1776 47489
01-04-2022 472630 2779
100 1107 1772 49261
01-05-2022 471524 2779
100 1111 1768 51029
01-06-2022 470413 2779
100 1115 1764 52793
01-07-2022 469298 2779
100 1119 1760 54553
01-08-2022 468178 2779
100 1123 1756 56309
01-09-2022 467055 2779
100 1128 1751 58060
01-10-2022 465927 2779
100 1132 1747 59807
01-11-2022 464795 2779
100 1136 1743 61550
01-12-2022 463659 2779
100 1140 1739 63289
01-01-2023 462519 2779
100 1145 1734 65024
01-02-2023 461374 2779
100 1149 1730 66754
01-03-2023 460225 2779
100 1153 1726 68480
01-04-2023 459071 2779
100 1158 1722 70201
01-05-2023 457914 2779
100 1162 1717 71918
01-06-2023 456752 2779
100 1166 1713 73631
01-07-2023 455585 2779
100 1171 1708 75340
01-08-2023 454415 2779
100 1175 1704 77044
01-09-2023 453240 2779
100 1180 1700 78743
01-10-2023 452060 2779
100 1184 1695 80439
01-11-2023 450876 2779
100 1188 1691 82129
01-12-2023 449688 2779
100 1193 1686 83816
01-01-2024 448495 2779
100 1197 1682 85498
01-02-2024 447298 2779
100 1202 1677 87175
01-03-2024 446096 2779
100 1206 1673 88848
01-04-2024 444890 2779
100 1211 1668 90516
01-05-2024 443679 2779
100 1215 1664 92180
01-06-2024 442463 2779
100 1220 1659 93839
01-07-2024 441243 2779
100 1224 1655 95494
01-08-2024 440019 2779
100 1229 1650 97144
01-09-2024 438790 2779
100 1234 1645 98789
01-10-2024 437556 2779
100 1238 1641 100430
01-11-2024 436318 2779
100 1243 1636 102066
01-12-2024 435075 2779
100 1248 1632 103698
01-01-2025 433827 2779
100 1252 1627 105325
01-02-2025 432575 2779
100 1257 1622 106947
01-03-2025 431318 2779
100 1262 1617 108564
01-04-2025 430056 2779
100 1266 1613 110177
01-05-2025 428790 2779
100 1271 1608 111785
01-06-2025 427519 2779
100 1276 1603 113388
01-07-2025 426243 2779
100 1281 1598 114987
01-08-2025 424962 2779
100 1286 1594 116580
01-09-2025 423676 2779
100 1290 1589 118169
01-10-2025 422386 2779
100 1295 1584 119753
01-11-2025 421091 2779
100 1300 1579 121332
01-12-2025 419791 2779
100 1305 1574 122906
01-01-2026 418486 2779
100 1310 1569 124476
01-02-2026 417176 2779
100 1315 1564 126040
01-03-2026 415861 2779
100 1320 1559 127599
01-04-2026 414541 2779
100 1325 1555 129154
01-05-2026 413217 2779
100 1330 1550 130704
01-06-2026 411887 2779
100 1335 1545 132248
01-07-2026 410553 2779
100 1340 1540 133788
01-08-2026 409213 2779
100 1345 1535 135322
01-09-2026 407868 2779
100 1350 1530 136852
01-10-2026 406519 2779
100 1355 1524 138376
01-11-2026 405164 2779
100 1360 1519 139896
01-12-2026 403804 2779
100 1365 1514 141410
01-01-2027 402439 2779
100 1370 1509 142919
01-02-2027 401069 2779
100 1375 1504 144423
01-03-2027 399694 2779
100 1380 1499 145922
01-04-2027 398314 2779
100 1385 1494 147415
01-05-2027 396928 2779
100 1391 1488 148904
01-06-2027 395538 2779
100 1396 1483 150387
01-07-2027 394142 2779
100 1401 1478 151865
01-08-2027 392741 2779
100 1406 1473 153338
01-09-2027 391334 2779
100 1412 1468 154806
01-10-2027 389923 2779
100 1417 1462 156268
01-11-2027 388506 2779
100 1422 1457 157725
01-12-2027 387083 2779
100 1428 1452 159176
01-01-2028 385656 2779
100 1433 1446 160622
01-02-2028 384223 2779
100 1438 1441 162063
01-03-2028 382785 2779
100 1444 1435 163499
01-04-2028 381341 2779
100 1449 1430 164929
01-05-2028 379892 2779
100 1455 1425 166353
01-06-2028 378437 2779
100 1460 1419 167772
01-07-2028 376977 2779
100 1465 1414 169186
01-08-2028 375512 2779
100 1471 1408 170594
01-09-2028 374041 2779
100 1477 1403 171997
01-10-2028 372564 2779
100 1482 1397 173394
01-11-2028 371082 2779
100 1488 1392 174786
01-12-2028 369594 2779
100 1493 1386 176172
01-01-2029 368101 2779
100 1499 1380 177552
01-02-2029 366602 2779
100 1504 1375 178927
01-03-2029 365098 2779
100 1510 1369 180296
01-04-2029 363588 2779
100 1516 1363 181659
01-05-2029 362072 2779
100 1521 1358 183017
01-06-2029 360551 2779
100 1527 1352 184369
01-07-2029 359024 2779
100 1533 1346 185715
01-08-2029 357491 2779
100 1539 1341 187056
01-09-2029 355952 2779
100 1544 1335 188391
01-10-2029 354408 2779
100 1550 1329 189720
01-11-2029 352858 2779
100 1556 1323 191043
01-12-2029 351302 2779
100 1562 1317 192361
01-01-2030 349740 2779
100 1568 1312 193672
01-02-2030 348173 2779
100 1574 1306 194978
01-03-2030 346599 2779
100 1579 1300 196277
01-04-2030 345020 2779
100 1585 1294 197571
01-05-2030 343434 2779
100 1591 1288 198859
01-06-2030 341843 2779
100 1597 1282 200141
01-07-2030 340246 2779
100 1603 1276 201417
01-08-2030 338643 2779
100 1609 1270 202687
01-09-2030 337033 2779
100 1615 1264 203951
01-10-2030 335418 2779
100 1621 1258 205209
01-11-2030 333797 2779
100 1627 1252 206460
01-12-2030 332169 2779
100 1634 1246 207706
01-01-2031 330536 2779
100 1640 1240 208945
01-02-2031 328896 2779
100 1646 1233 210179
01-03-2031 327250 2779
100 1652 1227 211406
01-04-2031 325598 2779
100 1658 1221 212627
01-05-2031 323940 2779
100 1664 1215 213842
01-06-2031 322276 2779
100 1671 1209 215050
01-07-2031 320605 2779
100 1677 1202 216253
01-08-2031 318928 2779
100 1683 1196 217449
01-09-2031 317245 2779
100 1689 1190 218638
01-10-2031 315556 2779
100 1696 1183 219822
01-11-2031 313860 2779
100 1702 1177 220999
01-12-2031 312157 2779
100 1709 1171 222169
01-01-2032 310449 2779
100 1715 1164 223333
01-02-2032 308734 2779
100 1721 1158 224491
01-03-2032 307013 2779
100 1728 1151 225642
01-04-2032 305285 2779
100 1734 1145 226787
01-05-2032 303550 2779
100 1741 1138 227926
01-06-2032 301809 2779
100 1747 1132 229057
01-07-2032 300062 2779
100 1754 1125 230183
01-08-2032 298308 2779
100 1761 1119 231301
01-09-2032 296548 2779
100 1767 1112 232413
01-10-2032 294781 2779
100 1774 1105 233519
01-11-2032 293007 2779
100 1780 1099 234617
01-12-2032 291226 2779
100 1787 1092 235710
01-01-2033 289439 2779
100 1794 1085 236795
01-02-2033 287646 2779
100 1800 1079 237874
01-03-2033 285845 2779
100 1807 1072 238946
01-04-2033 284038 2779
100 1814 1065 240011
01-05-2033 282224 2779
100 1821 1058 241069
01-06-2033 280403 2779
100 1828 1052 242121
01-07-2033 278575 2779
100 1835 1045 243165
01-08-2033 276741 2779
100 1841 1038 244203
01-09-2033 274899 2779
100 1848 1031 245234
01-10-2033 273051 2779
100 1855 1024 246258
01-11-2033 271196 2779
100 1862 1017 247275
01-12-2033 269334 2779
100 1869 1010 248285
01-01-2034 267465 2779
100 1876 1003 249288
01-02-2034 265588 2779
100 1883 996 250284
01-03-2034 263705 2779
100 1890 989 251273
01-04-2034 261815 2779
100 1897 982 252254
01-05-2034 259918 2779
100 1904 975 253229
01-06-2034 258013 2779
100 1912 968 254197
01-07-2034 256102 2779
100 1919 960 255157
01-08-2034 254183 2779
100 1926 953 256110
01-09-2034 252257 2779
100 1933 946 257056
01-10-2034 250324 2779
100 1940 939 257995
01-11-2034 248383 2779
100 1948 931 258926
01-12-2034 246435 2779
100 1955 924 259850
01-01-2035 244480 2779
100 1962 917 260767
01-02-2035 242518 2779
100 1970 909 261677
01-03-2035 240548 2779
100 1977 902 262579
01-04-2035 238571 2779
100 1985 895 263473
01-05-2035 236587 2779
100 1992 887 264361
01-06-2035 234595 2779
100 1999 880 265240
01-07-2035 232595 2779
100 2007 872 266113
01-08-2035 230588 2779
100 2014 865 266977
01-09-2035 228574 2779
100 2022 857 267834
01-10-2035 226552 2779
100 2030 850 268684
01-11-2035 224522 2779
100 2037 842 269526
01-12-2035 222485 2779
100 2045 834 270360
01-01-2036 220440 2779
100 2053 827 271187
01-02-2036 218388 2779
100 2060 819 272006
01-03-2036 216328 2779
100 2068 811 272817
01-04-2036 214260 2779
100 2076 803 273621
01-05-2036 212184 2779
100 2083 796 274416
01-06-2036 210100 2779
100 2091 788 275204
01-07-2036 208009 2779
100 2099 780 275984
01-08-2036 205910 2779
100 2107 772 276756
01-09-2036 203803 2779
100 2115 764 277521
01-10-2036 201688 2779
100 2123 756 278277
01-11-2036 199565 2779
100 2131 748 279025
01-12-2036 197435 2779
100 2139 740 279766
01-01-2037 195296 2779
100 2147 732 280498
01-02-2037 193149 2779
100 2155 724 281222
01-03-2037 190994 2779
100 2163 716 281939
01-04-2037 188831 2779
100 2171 708 282647
01-05-2037 186660 2779
100 2179 700 283347
01-06-2037 184481 2779
100 2187 692 284038
01-07-2037 182294 2779
100 2196 684 284722
01-08-2037 180098 2779
100 2204 675 285397
01-09-2037 177894 2779
100 2212 667 286065
01-10-2037 175682 2779
100 2220 659 286723
01-11-2037 173462 2779
100 2229 650 287374
01-12-2037 171233 2779
100 2237 642 288016
01-01-2038 168996 2779
100 2245 634 288650
01-02-2038 166751 2779
100 2254 625 289275
01-03-2038 164497 2779
100 2262 617 289892
01-04-2038 162234 2779
100 2271 608 290500
01-05-2038 159964 2779
100 2279 600 291100
01-06-2038 157684 2779
100 2288 591 291691
01-07-2038 155397 2779
100 2296 583 292274
01-08-2038 153100 2779
100 2305 574 292848
01-09-2038 150795 2779
100 2314 565 293414
01-10-2038 148481 2779
100 2322 557 293971
01-11-2038 146159 2779
100 2331 548 294519
01-12-2038 143828 2779
100 2340 539 295058
01-01-2039 141488 2779
100 2349 531 295589
01-02-2039 139140 2779
100 2357 522 296110
01-03-2039 136782 2779
100 2366 513 296623
01-04-2039 134416 2779
100 2375 504 297127
01-05-2039 132041 2779
100 2384 495 297623
01-06-2039 129657 2779
100 2393 486 298109
01-07-2039 127264 2779
100 2402 477 298586
01-08-2039 124862 2779
100 2411 468 299054
01-09-2039 122451 2779
100 2420 459 299513
01-10-2039 120031 2779
100 2429 450 299964
01-11-2039 117602 2779
100 2438 441 300405
01-12-2039 115164 2779
100 2447 432 300836
01-01-2040 112717 2779
100 2456 423 301259
01-02-2040 110260 2779
100 2466 413 301673
01-03-2040 107794 2779
100 2475 404 302077
01-04-2040 105319 2779
100 2484 395 302472
01-05-2040 102835 2779
100 2494 386 302857
01-06-2040 100342 2779
100 2503 376 303234
01-07-2040 97839 2779
100 2512 367 303601
01-08-2040 95327 2779
100 2522 357 303958
01-09-2040 92805 2779
100 2531 348 304306
01-10-2040 90274 2779
100 2541 339 304645
01-11-2040 87733 2779
100 2550 329 304974
01-12-2040 85183 2779
100 2560 319 305293
01-01-2041 82623 2779
100 2569 310 305603
01-02-2041 80054 2779
100 2579 300 305903
01-03-2041 77475 2779
100 2589 291 306194
01-04-2041 74886 2779
100 2598 281 306474
01-05-2041 72288 2779
100 2608 271 306745
01-06-2041 69680 2779
100 2618 261 307007
01-07-2041 67062 2779
100 2628 251 307258
01-08-2041 64434 2779
100 2638 242 307500
01-09-2041 61797 2779
100 2647 232 307732
01-10-2041 59149 2779
100 2657 222 307953
01-11-2041 56492 2779
100 2667 212 308165
01-12-2041 53825 2779
100 2677 202 308367
01-01-2042 51147 2779
100 2687 192 308559
01-02-2042 48460 2779
100 2697 182 308741
01-03-2042 45763 2779
100 2708 172 308912
01-04-2042 43055 2779
100 2718 161 309074
01-05-2042 40337 2779
100 2728 151 309225
01-06-2042 37609 2779
100 2738 141 309366
01-07-2042 34871 2779
100 2748 131 309497
01-08-2042 32123 2779
100 2759 120 309617
01-09-2042 29364 2779
100 2769 110 309727
01-10-2042 26595 2779
100 2779 100 309827
01-11-2042 23816 2779
100 2790 89 309916
01-12-2042 21026 2779
100 2800 79 309995
01-01-2043 18226 2779
100 2811 68 310064
01-02-2043 15415 2779
100 2821 58 310121
01-03-2043 12593 2779
100 2832 47 310169
01-04-2043 9761 2779
100 2843 37 310205
01-05-2043 6919 2779
100 2853 26 310231
01-06-2043 4066 2779
100 2864 15 310246
01-07-2043 1202 2779
100 1197 5 310251
Create an amortization table for a 25-year mortgage loan that has monthly payments. The values in...
Amortization of a Loan EXAMPLE EXERCISES For the following exercises, create an amortization table for a $21,500 car loan with 4.5% APR over 72 months. 1. What is the monthly payment? 2. How much is actually paid over the life of the loan? How much interest? 3. How much is saved if you round up to the next $50 increment? Next $100? For the following exercises, create an amortization table for $135,000 home purchase at 4.6% fixed APR. Remember mortgages...
5. Create a complete amortization table for the following loan: Plain vanilla fully amortizing constant payment loan with monthly payments 30 year term Rate on loan 4.5% Loan amount $777,777 ****do work in excel please****
A $152,000 mortgage loan at 4.8% compounded monthly requires monthly payments during its 25-year amortization period. (Do not round the intermediate calculations. Round your answers to 2 decimal places.) a. Calculate the monthly payments rounded to the cent. PMT is $ b. Calculate the balance owing on the loan after eight years using the Retrospective Method. After 8 years of payments, the balance outstanding on the loan is $
PLEASE EXPLAIN HOW TO CREATE THE AMORTIZATION TABLE IN EXCEL! Mini-Project 1: Mortgage Loan Analysis (50 points): You plan to buy a house in December 2019. The sale price is $385,000. You need to pay 20% down payments and borrow additional 80% from Bank of America with a 25-year, 3.0% fixed-rate mortgage loan. You are expected to pay an equal MONTHLY payment starting from January 2020 for a total of 25 years. (1) Calculate your expected monthly mortgage payment. (2)...
Loans Excel Homework Assignment Mortgage Amortization Schedule Excel Assignment 1. Excel: Complete the amortization table provided in the Excel document posted in in the homework area by setting the appropriate values for a $165,000, 30-year mortgage at 4.5% interest and using Excel's autofill (drag) feature to fill in the cells to the end of the mortgage period. Use this to answer the following: a. How much of the first payment goes towards the principal? How much goes toward the interest?...
Vanna has just financed the purchase of a home for $200 000. She agreed to repay the loan by making equal monthly blended payments of $3000 each at 4%/a, compounded monthly. a. Create an amortization table using a Microsoft Excel spreadsheet. In your answer include all the formulas used.b.How long will it take to repay the loan?c. How much will be the final payment?d. Determine how much interest she will pay for her loan.e. Use Microsoft Excel to graph the amortization...
7A) Use excel to build a monthly amortization table for a 30-year 5% fix-rate mortgage on a $320,000 home, with a $15,000 down payment. [Please be good to the forests and cut and paste only the first and last few rows of the table, not the whole thing.] 7B) Using the amortization table you built for the previous question, answer the following [Again, just cut and paste the segment of the amortization table that helps you answer the question.]: a....
Create an amortization schedule for a 20-year, $200,000 loan at 7.5% interest with monthly payments. During which payment number does the amount applied to the principal exceed the amount paid to interest? True or False? After half of the time has passed in making payments, we also note that exactly half of the overall balance has been paid off. Explain. After completing the schedule, show two different ways for finding the total amount of interest paid over the 20 years.
Create an amortization schedule for a 20-year, $200,000 loan at 7.5% interest with monthly payments. During which payment number does the amount applied to the principal exceed the amount paid to interest? True or False? After half of the time has passed in making payments, we also note that exactly half of the overall balance has been paid off. Explain. After completing the schedule, show two different ways for finding the total amount of interest paid over the 20 years.