Reference no: EM131981495

You would like to take out a $300,000 loan for a 30-year fixed mortgage and the bank is offering a 30-year fixed rate annual rate of 5.0% with monthly payments. (Note: the interest rate given here is an annual value and you must correct for monthly payments.) Use the APR convention for rate conversion. Call the worksheet “Simple Loan”. 1. Create a spreadsheet model with a vertical (by column) timeline to calculate the beginning principal, payment amount, interest paid, principal component, and ending principal in each month for 30 years. If after five years (5*12=60 monthly mortgage payments) you receive a promotion and are transferred to another city, how much money is left on the principal? 2. Create a new worksheet called “Down Payment Decision” and copy your completed answer from part 1 to it. Modify the worksheet and formulas as needed for the following changes: a. The bank is willing to offer a lower interest rate (annual rate of 4.85%) if you put $30,000 as a down payment, reducing the amount of the loan. If the alternative to putting $30,000 down is to leave the money in a bank savings account that yields 1% annually, what financial decision makes more sense? Defend your answer and be sure to explain any assumptions made. b. Now use Goal Seek to determine the interest rate required for the bank account to make the decision alternatives equivalent.