Please i need help with this question. should be answered in excel format with explanations. All information needed is above. Thanks.

IE 3840: Engineering Economic Analysis, Summer 2017 Case Study i-Material for Chapters 1 to 4 People sometimes buy houses at unplanned times. Generally, it is accepted wisdom that a 20% down payment is desirable, but perhaps a potential buyer needs to relocate for work or a major change in life (such as elderly parents who must move in with children). The amount not paid up-front (the down payment) is borrowed from the bank as the mortgage The monthly payment is composed of four elements: the mortgage payment, property tax, home owner's insurance, and, in the cases of 5% and 10% down, private mortgage insurance (PMI). The bank estimates the amount of the property tax and home Owner nsurance. Consider the following scenario: A family in the sandwich generation (kids still at home, elderly parents requiring home care) needs to buy a house. The house will be bought for $250,000 at a 3.625% annual interest rate, compounded monthly. There are three potential down payment amounts: 5%, 10% and 20%. The following table shows the down payment options, resulting monthly payments (assuming a 30 year mortgage with 360 monthly payments) and the portion of the monthly payment that is PMI 1 2 3 tion Purchase price $250,000 $250,000 S250,000 Down payment 6 206 106 596 $50,000 $25,000 $12,500 Down Payment Loan Amount $200,000 $225,000 $237,500 S1 206 81.498 566 Monthly Payment Portion yment that is PMI SOI S176 $186 This family was not expecting to make any of these down payments, and has no spare cash on hand However, they have a college savings account from which they can withdraw funds. But, if they withdraw funds for non-college uses, they must pay taxes on the earnings. The account has S70,000 available, of which 75% was deposited and 25% is earnings. In this family's case, hey will pay a one-time tax of 27.5% on 25% of the amount withdrawn to cover the down payment. This family wants you to advise them on which option to take: the 5%, 10% or 20% option. In addition to the material provided in the Case Study Guidelines, be sure to answer these questions as you prepare your report: 1. What is the monthly mortgage payment in each option? What is your best estimate for the monthly total for property tax and homeowner's insurance? 3. How much money must be withdrawn from the college account to cover the down payment and he tax owed on that down payment? In the case of the 10% and 20% down payments, assume the family deposits the difference between the corresponding monthly payment and the 5% option monthly payment into the college account. So, for example, if the family chose to make a 10% down payment, they would college account each month. If the college account earns 2% deposit $1566-$1498 $68 into the per year, compounded monthly, how long would it take to recover the amount withdrawn to cover the higher down payment 5. Which option should the family take and why? This must be explicitly justified! 6. What additional information do you wish you had