1. You work in the Mortgage Banking Division of Denver Federal Credit Union (DFCU). Andrea Petersen is thinking of buying a house worth $600,000. She has approached your bank to find out whether she can get a loan to finance the home purchase, and if yes, what her monthly mortgage payments will be. Andrea has saved up $60,000, which she intends to use as down payment on the house. After interviewing Andrea, you find out that she makes enough money to be able to afford the mortgage payments, so your bank offers Andrea a 30-year-loan of $540,000 (she puts 10% down payment) to buy the house. Assume you use a mortgage rate of 7.00% per year (APR) to estimate Andreas monthly payments. Using MS Excel, a. What is Andreas monthly payment? b. Create an amortization schedule for Andrea indicating her outstanding loan balance at the beginning of each month, her monthly payment, interest payment each month, principal payment each month, outstanding loan balance at the end of each month, and accumulated interest payment at each point. c. How much in interest will Andrea have paid by the time her mortgage is paid off? d. What will be Andreas outstanding balance at the end of 15 years? e. Make a plot of Andreas interest payments and principal payments against time. How do the principal payments and interest payments change over time? f. How much in interest payments will Andrea save should she decide to pay off her mortgage in 15 years instead of in 30 years? [Here, use an amortization schedule of 15 years instead of 30 years and compare the amount of interest paid in each case.]