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 carpenter ’ s apprentice, and Alice is a customer service specialist at a local bank. In 2011, Ted s 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.
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 buyers 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 Alice s take home wages (after taxes and deductions) were $24,000, and Alice ’ “ ” ’ s other living expenses (such as car payments, food, and medical bills), the utilities expenses for either renting or buying, and estimated house maintenance expenses are listed in the Constants section (see Figure C-32).

Ted and Alice ’ s primary concern is their cash on hand at the end of years 2016 and 2017. They are thinking of starting a family, but they know it will be difficult without adequate savings. ’

Getting Started on the Practice Exercise
If you closed Excel after the first tutorial exercise, start Excel again it will automatically open a new workbook for you. If your Excel workbook from the first tutorial is still open, you may find it useful to start a new worksheet in the same workbook. Then you can refer back to the first tutorial when you need to structure or format the spreadsheet; the formatting of both exercises in this tutorial is similar. Set up your new worksheet as explained in the following sections.

— Constants Section
Your spreadsheet should have the constants shown in Figure C-32. An explanation of the line items follows the figure. FIGURE C-32.

Description truncated due to space constraint here. Solution file contains solution for the complete case though.