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?

Explanation / Answer

555402

b) 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 after 10 years :

It will take time of 15 years 9 months to clear the loan amount.

Principal amount of Loan $       6,50,000 Interest rate per annum 7.00% Period per annum 12 Months Interest rate per month 7.00% / 12 Months = 0.5833 No. Of years of the loan 20 years or 240 instalments Due to words restriction, given only for 5 years or 60 instalments Monthly payent on the mortgage : ( $ ) Years Opening Instalment Interest Principal Closing Balance ie., EMI amount Repayment Balance First Yr: jan 650000 5113 3792 1321 648679 feb 648679 5113 3784 1329 647350 mar 647350 5113 3776 1337 646013 apr 646013 5113 3768 1345 644668 may 644668 5113 3761 1352 643316 june 643316 5113 3753 1360 641955 july 641955 5113 3745 1368 640587 aug 640587 5113 3737 1376 639211 sept 639211 5113 3729 1384 637827 oct 637827 5113 3721 1392 636434 nov 636434 5113 3713 1400 635034 dec 635034 5113 3704 1409 633625 Second Yr: jan 633625 5113 3696 1417 632208 feb 632208 5113 3688 1425 630783 mar 630783 5113 3680 1433 629350 apr 629350 5113 3671 1442 627908 may 627908 5113 3663 1450 626458 june 626458 5113 3654 1459 624999 july 624999 5113 3646 1467 623532 aug 623532 5113 3637 1476 622056 sept 622056 5113 3629 1484 620572 oct 620572 5113 3620 1493 619079 nov 619079 5113 3611 1502 617577 dec 617577 5113 3603 1510 616067 third Yr: jan 616067 5113 3594 1519 614548 feb 614548 5113 3585 1528 613019 mar 613019 5113 3576 1537 611482 apr 611482 5113 3567 1546 609936 may 609936 5113 3558 1555 608381 june 608381 5113 3549 1564 606817 july 606817 5113 3540 1573 605244 aug 605244 5113 3531 1582 603662 sept 603662 5113 3521 1592 602070 oct 602070 5113 3512 1601 600469 nov 600469 5113 3503 1610 598859 dec 598859 5113 3493 1620 597239 Fourth Yr: jan 597239 5113 3484 1629 595610 feb 595610 5113 3474 1639 593971 mar 593971 5113 3465 1648 592323 apr 592323 5113 3455 1658 590665 may 590665 5113 3446 1667 588998 june 588998 5113 3436 1677 587321 july 587321 5113 3426 1687 585634 aug 585634 5113 3416 1697 583937 sept 583937 5113 3406 1707 582230 oct 582230 5113 3396 1717 580514 nov 580514 5113 3386 1727 578787 dec 578787 5113 3376 1737 577050 Fifth Yr: jan 577050 5113 3366 1747 575303 feb 575303 5113 3356 1757 573546 mar 573546 5113 3346 1767 571779 apr 571779 5113 3335 1778 570001 may 570001 5113 3325 1788 568213 june 568213 5113 3315 1798 566415 july 566415 5113 3304 1809 564606 aug 564606 5113 3294 1819 562787 sept 562787 5113 3283 1830 560956 oct 560956 5113 3272 1841 559116 nov 559116 5113 3262 1851 557264 dec 557264 5113 3251 1862

555402

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

---

---

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