urchase a condo in downtown Boston for $1,200,000. You can finance 80% of its co

Question

urchase a condo in downtown Boston for $1,200,000. You can finance 80% of its cost by receiving a 20-year mortgage. Considering your income and your credit score, your bank offered 4.5% interest on your mortgage. For simplicity assume that you make yearly payments to your mortgage. Build an amortization schedule for this loan in Excel and answer the following questions. a. How much in total do you have to pay back to the bank? b. How much do you owe to the bank at the end of year 10? C. At the end of year 10, you decide to refinance your home with a new 15-year 3.5% mortgage. How much is your new yearly payment? d. Consider the initial mortgage (not the case where you refinanced). Starting from year 11, you decide to increase your yearly payments by 10% hoping that it helps you pay off your mortgage sooner. In this case, how long does it take for you to pay off your mortgage?

Explanation / Answer

Loan Amount 960000 1200000*80% No of yrs 20 yrs Interest Rate 4.50% First we would calculate the yearly payment to be made under this mortgage Using the PMT function in excel, we can calculate the yearly payments ($73,801.10) PMT(4.5%,20,960000) Amortization Schedule Years Beginning Bal Interest @ 4.50% Principal Total Payment Closing Bal 1 $960,000.0 $43,200.0 $30,601.1 $73,801.1 $929,398.9 2 $929,398.9 $41,823.0 $31,978.1 $73,801.1 $897,420.8 3 $897,420.8 $40,383.9 $33,417.2 $73,801.1 $864,003.6 4 $864,003.6 $38,880.2 $34,920.9 $73,801.1 $829,082.6 5 $829,082.6 $37,308.7 $36,492.4 $73,801.1 $792,590.3 6 $792,590.3 $35,666.6 $38,134.5 $73,801.1 $754,455.7 7 $754,455.7 $33,950.5 $39,850.6 $73,801.1 $714,605.1 8 $714,605.1 $32,157.2 $41,643.9 $73,801.1 $672,961.3 9 $672,961.3 $30,283.3 $43,517.8 $73,801.1 $629,443.4 10 $629,443.4 $28,325.0 $45,476.1 $73,801.1 $583,967.3 11 $583,967.3 $26,278.5 $47,522.6 $73,801.1 $536,444.7 12 $536,444.7 $24,140.0 $49,661.1 $73,801.1 $486,783.6 13 $486,783.6 $21,905.3 $51,895.8 $73,801.1 $434,887.8 14 $434,887.8 $19,570.0 $54,231.1 $73,801.1 $380,656.6 15 $380,656.6 $17,129.5 $56,671.6 $73,801.1 $323,985.1 16 $323,985.1 $14,579.3 $59,221.8 $73,801.1 $264,763.3 17 $264,763.3 $11,914.3 $61,886.8 $73,801.1 $202,876.6 18 $202,876.6 $9,129.4 $64,671.7 $73,801.1 $138,204.9 19 $138,204.9 $6,219.2 $67,581.9 $73,801.1 $70,623.0 20 $70,623.0 $3,178.0 $70,623.1 $73,801.1 ($0.0) $516,022.0 $960,000.0 $1,476,022.0 a Total payment to the Bank is $ 1476022 b Amount owed to bank at the end of year 10 is $ 583967.30 c Loan Amount $583,967.30 No of yrs 15 Interest Rate 3.50% Using the PMT function in excel we can calculate the yearly payment ($50,703.00) PMT(3.5%,15,583967.3) d Yearly Payment $73801.10*1.10 PMT 81181.21 Present Value of Loan $583,967.30 Using the NPER function in excel we can calculate the no of years it would take to repay the loan 8.88571788 It would take 8.89 that is approx 9 yrs to repay the loan

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

---

---

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