Affordable Home Purchase Develop a spreadsheet model to determine how much a person or a couple can afford to spend on a house. Lender guidelines suggest that the allowable monthly housing expenditure should be no more than 28% of monthly gross income. From this, you must subtract total nonmortgage housing expenses, which would include insurance and property taxes and any other additional expenses. This defines the affordable monthly mortgage payment. In addition, guidelines also suggest that total affordable monthly debt payments, including housing expenses, should not exceed 36% of gross monthly income. This is calculated by subtracting total nonmortgage housing expenses and any other installment debt, such as car loans, student loans, credit card debt, and so on, from 36% of total monthly gross income. The smaller of the affordable monthly mortgage payment and the total remaining affordable monthly debt payments is the affordable monthly mortgage. To calculate the maximum that can be borrowed, find the monthly payment per $1,000 mortgage based on the current interest rate and duration of the loan. Divide the affordable monthly mortgage payment by this monthly payment to find the affordable mortgage. Assuming a 20% down payment, the maximum price of a house would be the affordable mortgage divided by 0.8.

Use the following data to test your model: total monthly gross income = $6,500; nonmortgage housing expenses = $350; monthly installment debt = $500; monthly payment per $1,000 mortgage = $7.25.

Use the Scenario Manager to calculate the maximum home price a buyer can afford based on the following 4 scenarios:

o Scenario 1: total monthly gross income = $7,250; nonmortgage housing expenses = $525; monthly installment debt = $575; monthly payment per $1,000 mortgage = $8.00

o Scenario 2: total monthly gross income = $6,000; nonmortgage housing expenses = $325; monthly installment debt = $800; monthly payment per $1,000 mortgage = $8.50

o Scenario 3: total monthly gross income = $4,500; nonmortgage housing expenses = $400; monthly installment debt = $775; monthly payment per $1,000 mortgage = $7.75

o Scenario 4: total monthly gross income = $8,000; nonmortgage housing expenses = $850; monthly installment debt = $850; monthly payment per $1,000 mortgage = $6.90

B D E F G I H - J K - M N. 1 Affordable Home Purchase 28% of monthly gross income 36% of monthly gross income 20% $6,500 $350 $500 $7.25 2 3 Data 4 Maximum allowable monthly housing expenditure (%) 5 Maximum total affordable monthly debt payments (%) 6 Down payment (%) 7 8 Total monthly gross income 9 Nonmortgage housing expenses 10 Monthly installment debt 11 Monthly payment per $1,000 mortgage 12 13 14 Model 15 Allowable monthly housing expenditure 16 Nonmortgage housing expenses 17 Affordable monthly mortgage payment (based on housing expenses only) 18 19 Total affordable monthly debt payments 20 Nonmortgage housing expenses 21 Monthly installment debt 22 Remaining affordable monthly debt payments 23 24 Affordable monthly mortgage payment 25 26 Affordable total mortgage 27 Maximum home price total gross income * max percentage $1,820 $350 $1,470 total gross income * max percentage debt-(monthly installment debt+nonmortgage housing expense $1,490 $350 $500