Question: 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

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.

Excel Lay out

Develop a spreadsheet model to determine how much a person or a

Use the Scenario Manager to calculate the maximum home price a buyer can afford based on the following 2 scenarios: - Scenario 1: total monthly gross income = $7,250; nonmortgage housing expenses= $525; monthly installment debt = $575; monthly payment per $1,000 mortgage = $8.00

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

1. According to your Scenario Summary for the Affordable Home Purchase model, what is the maximum home price in Scenario 1? (Format your answer rounded to the nearest cent. Do not include a dollar sign.)

2. According to your Scenario Summary for the Affordable Home Purchase model, what is the maximum home price in Scenario 3? (Format your answer rounded to the nearest cent. Do not include a dollar sign.)

Affordable Home Purchase Data Maximum allowable monthly housing expenditure (%) Maximum total affordable monthly debt payments (%) Down payment (%) of monthly gross income of monthly gross income Total monthly gross income Nonmortgage housing expenses Monthly installment debt Monthly payment per $1,000 mortgage Model Allowable monthly housing expenditure Nonmortgage housing expenses Affordable monthly mortgage payment (based on housing expenses only) Total affordable monthly debt payments Nonmortgage housing expenses Monthly installment debt Remaining affordable monthly debt payments Affordable monthly mortgage payment Affordable total mortgage Maximum home price Affordable Home Purchase Data Maximum allowable monthly housing expenditure (%) Maximum total affordable monthly debt payments (%) Down payment (%) of monthly gross income of monthly gross income Total monthly gross income Nonmortgage housing expenses Monthly installment debt Monthly payment per $1,000 mortgage Model Allowable monthly housing expenditure Nonmortgage housing expenses Affordable monthly mortgage payment (based on housing expenses only) Total affordable monthly debt payments Nonmortgage housing expenses Monthly installment debt Remaining affordable monthly debt payments Affordable monthly mortgage payment Affordable total mortgage Maximum home price

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer

Step: 1 Unlock blur-text-image

Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock

Step: 3 Unlock

Students Have Also Explored These Related Finance Questions!

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...

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 lowable monny nown expenditure should be no more than 28% of...

11.3.19 Question Help 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...

I needed help with the following. Please take a look. QUESTION \fHousing Affordability Total Monthly Gross Income $6,500.00 Allowable Monthly Housing Expenditure Max Percentage 28% Total Non-Mortgage...

The Vilmore Corporation is considering two word-processing programs for its PCs. One factor that will influence its decision is the ease of use in preparing a business report. Consequently, Jody...

Chapman Department Store is located in midtown Metropolis. During the past several years, net income has been declining because suburban shopping centers have been attracting business away from city...

Lala invest in 1 0 , 0 0 0 and one of long life insurance companies s annuity contract when issued the contract was paying a 4 % of return two years later large increase this rate to 6 % underlying...

A company is preparing completing their Cash Budget. The following data has been prepared for cash receipts and payments. January February March Cash receipts $1,061,200 $1,182,400 $1,091,700 Cash...