You work in the Mortgage Banking of Alabama Federal Credit Union (AFCU). Shiloh Jones is thinking of buying a house worth $375,000. he has approached your bank to find out whether he can get a loan to finance the home purchase, and if yes, what his monthly mortgage payments will be. Shiloh has saved up $37,500, which he intends to use as down payment on the house. After interviewing Shiloh, you find out that he makes enough money to be able to afford the mortgage payments, so your bank offers Shiloh a 30-year-loan of $337,500 (he puts 10% down payment) to buy the house. Assume you use a mortgage rate of 4.11% per year (APR) to estimate

Shilohs monthly payments. Using MS Excel,

1. What is Shilohs monthly payment?

B. Create an amortization schedule for Shiloh indicating his outstanding loan balance at the

beginning of each month, his 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 Shiloh have paid by the time his mortgage is paid off?

D. What will be Shilohs outstanding balance at the end of 15 years?

E. Make a plot of Shilohs 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 Shiloh save should he decide to pay off his mortgage in

15 years instead of in 30 years? [Here, use an amortization schedule of 15 years instead of 30

years]