Ted and Alices House Purchase Decision Ted and Alice are a young couple who have been living in an apartment for the first two years of their marriage. They would like to buy their first house but do not know if they would be able to make ends meet. Ted works as a carpenters apprentice, and Alice is a customer service specialist at a local bank. In 2011, Teds take-home wages (after taxes and deductions) were $24,000, and Alices take-home salary was $30,000. Ted gets a 2% raise every year, and Alice gets a 3% raise. Their apartment rent is $1,200 per month ($14,400 per year), but the lease is up for renewal and the landlord has said he will increase the rent for the next lease. Their cash-on-hand at the end of 2011 is $4000. Ted and Alice have been looking at houses and have found one that they can buy, but they will need to borrow $200,000 for a mortgage. Their parents are helping them with the down payment and closing costs. After talking to several lenders, Ted and Alice have learned that the state legislature is voting on a first-time home buyer's mortgage bond. If the bill passes, they will be able to get a 30-year fixed mortgage at 3% interest. Otherwise, they will have to pay 6% interest on the mortgage. Because of the depressed housing market, Ted and Alice are not figuring equity value into their calculations. In addition, although the mortgage interest and real estate taxes will be deductible on their income taxes, those deductions will not be higher than the standard allowable tax deduction, so they are not figuring on any savings there either. Ted and Alices other living expenses (such as car payments, food, and medical bills), the utility expenses for either renting or buying an estimated house maintenance expenses are listed as follows. 2011 2012 2013 Non-Housing Living Expenses (Cars, Food, Medical, etc.) NA $36,000 $39,000 Real Estate Taxes and Insurance on Home NA $3,000 $3,150 Utilities Expenses (Heat & Electric) - Apartment NA $2,000 $2,200 Utility Expenses (Heat, Electric, Water, Trash) - House NA $2,500 $2,600 House Repair and Maintenance Expenses NA $1,200 $1,400 Ted and Alices primary concern is their cash on hand at the end of the years 2012 and 2013. They are thinking of starting a family, but they know it will be difficult without adequate savings. Some more related information are given below. Non-Housing Living ExpensesThis value represents Ted and Alices estimate of all their other living expenses for 2012 and 2013. Real Estate Taxes and Insurance on HomeA lender has given Ted and Alice estimates for these values; they are usually paid monthly with the house mortgage payment. The money is placed in an escrow account and then paid by the mortgage company to the state or county and the insurance company. Utility ExpenseApartmentThis value is Ted and Alices estimate for 2012 and 2013 based on their 2011 bills. Utility ExpenseHouseCurrently the apartment rent includes fees for water, sewer, and trash disposal. If they get a house, Ted and Alice expect the utilities to be higher. House Repair and Maintenance ExpensesIn an apartment, the landlord is responsible for repair and maintenance. Ted and Alice will have to budget for the repair and maintenance of the house. Rental Occupancy (H=High, L=Low)When the housing market is depressed (in other words, people are not buying homes), rental housing occupancy percentages are high, which allows landlords to charge higher rents when leases are renewed. Ted and Alice think their rent will increase in 2012. The amount of the increase depends on the Rental Occupancy. If the occupancy is high, Ted and Alice expect to see a 10% increase in rent in both 2012 and 2013. If occupancy is low, they only expect a 3% increase. First Time Buyer Bond Loans Available (Y=Yes, N=No)As described earlier, when housing markets are depressed, local governments will frequently pass a bond bill to provide low-interest mortgage money to first-time home buyers. If the bond loans are available, Ted and Alice can obtain a 30-year fixed mortgage at only 3%, which is half the interest rate they would otherwise pay for a conventional mortgage. Note that house mortgage interest is always compounded monthly, not annually. Hint: To use the PMT function here, divide the annual interest rate by 12, multiply the 30-year mortgage by 12 to get 360 payments and then multiply the PMT formula by 12 to get the total amount for annual repayments.

a. Build a spreadsheet model to help Ted and Alice take a decision. Share the detailed excel sheet b. How do changes in input parameters like Rental Occupancy and Bond Availability affect Ted and Alices end-of-the-year cash-on-hand with a detailed excel sheet?

spreadsheet model is to be prepared for given problem