Question

Ted and Alice’s 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 carpenter’s apprentice, and Alice is a customer service specialist at a local bank. In 2011, Ted’s “take-home” wages (after taxes and deductions) were $24,000, and Alice’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. 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 Alice’s 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 Alice’s 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 Expenses—This value represents Ted and Alice’s estimate of all their other living expenses for 2012 and 2013.
Real Estate Taxes and Insurance on Home—A 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 Expense—Apartment—This value is Ted and Alice’s estimate for 2012 and 2013 based on their 2011 bills.
Utility Expense—House—Currently 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 Expenses—In 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 Alice’s end-of-the-year cash-on-hand with a detailed excel sheet?

spreadsheet model is to be prepared for given problem

Answer 1

Solution:-

(a)

Ted and Alice's House Decision
Constants	2011	2012	2013
Non Hosing Living Expenses (Cars,Food ,Medical,etc.)	NA	$36,000	$39,000
Mortgage Amount for Home Purchase	NA	$2,00,000	NA
Real Estate taxes and Insurance on Home	NA	$3,000	$3,150
Utilities Expenses (Heat and Electric )- Apartment	NA	$2,000	$2,200
Utilities Expense (Heat ,car ,Water,and trash) house	NA	$2,500	$2,600
House repair and maintenance Expenses	NA	$1,200	$1,400

Inputs	2011	2012	2013
Rental Occupancy (H-High,L=Low)	NA	H	NA
First Time Buyer Bond Loans Available (Y = Yes, N=No)	NA	Y	NA
Summary of Key results	2011	2012	2013
End of year cash on Hand (Rent)	NA	$5,540	3,713
End of year cash on Hand (Buy)	NA	$6,562	$7,090

Calculations	2011	2012	2013
Apartment Rent	14,400	$15,840	$17,424
House Payments	NA	$10,118	$10,118
Interest rate for Mortgage House	3%	NA	NA
Income and Cash Flow Statement (Continue to rent)	2011	2012	2013
Beginning of year Cash on Hand	NA	$4,000	$5,540
Ted's take Home Salary	$24,000	$24,480	$24,970
Alice's take Home Salary	$30,000	$30,900	$31,827
Total take home Salary	$54,000	$55,380	$56,797
Apartment Rent	NA	$15,840	$17,424
Utilities (Apartment)	NA	$2,000	$2,200
Non -House Living Expenses	NA	$36,000	$39,000
Total Expenses	NA	$53,840	$58,624
End of Year Cash on Hand	$4,000	$5,540	$3,713

Income and Cash Flow Statement (Continue to rent)	2011	2012	2013
Beginning of year Cash on Hand	NA	$4,000	$6,562
Ted's take Home Salary	$24,000	$24,480	$24,970
Alice's take Home Salary	$30,000	$30,900	$31,827
Total take home Salary	$54,000	$55,380	$56,797
House Payments	NA	$10,118	$10,118
Real Estate Taxes and Insurance	NA	$3,000	$3,150
Utilities(House)	NA	$2,500	$2,600
House repair and Maintenance expenses	NA	$1,200	$1,400
Non Housing Living Expenses	NA	$36,000	$39,000
Total Expenses	NA	$52,818	$56,268
End of Year Cash on Hand	$4,000	$6,562	$7,090

(b)