Please use Excel to answer the following questions and show all your work including formulas/calculations. When solving problems in a spreadsheet, make sure to format the output very carefully in order to ensure that it is legible and presentable. (Remember that the work you turn in reflects you. Even if your work is correct, if it looks unprofessional it is unlikely to get the full attention of its intended audience.) Be sure to turn in a printout of the answers (carefully labeled) and the formulas (the spreadsheet with your work or print the formulas by pressing Ctrl + 2. Mortgage Amortization and Refinancing: Paul and Evelyn Peters were house hunting five years ago when mortgage rates were pretty high. The fixed rate they could get on a 30-year mortgage was 8.75% while the 15 year fixed rate was at 8%. After looking at many houses, they decided to buy a $200,000 two-story townhome in the Midwest, and to avoid paying PMI, they borrowed from family members and friends and collected the 20% down payment. Since they already had significant credit card debt, and student loans they were paying off, Evelyn and Paul decided on the 30-year mortgage even though the interest rate is higher. Currently, due to the recession, they have refinancing offers on the table (with no closing costs) for 15-year loan at 5% and a 30-year rate at 5.75%. They realize the hassle of refinancing with all the paperwork but they also do not want to pass up the opportunity of these low rates. They decide to refinance with the 15-year 5% rate loan. - How much is Evelyn and Paul's mortgage payment before refinancing? - Prepare an amortization table for their mortgage. How much has the couple paid towards their house, how much towards interest? Had the couple originally opted for the 15 year, 8% mortgage, how much higher would their payment be? Create an amortization table for this loan as well. How much interest and principal would they have paid on their loan after 5-years? If the house is worth $245,000 now and most lenders are willing to lend up to 90% of the home value, how much home equity can the Peters cash out? Assuming that money market investment rates are at 4%, do you think they should cash out? - If the Peters had increased their payment by $100 each month since the beginning of the loan, what would their current loan balance be? How much quicker would they be able to pay their loan off? Do you think that Evelyn and Paul should go ahead and close the 5% refinancing? What would your recommendation be to them and why? Support with calculations and numbers
