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 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 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 amount by this monthly payment to find the affordable mortgage. Assuming a 15​% down​ payment, the maximum price of a house would be the affordable mortgage divided by 0.85. Use the following data to test your​ model:

Total monthly gross income​ = ​$6,100

Nonmortgage housing expenses​ =​$320

Monthly installment debt​ = ​$500

Monthly payment per​ $1,000 mortgage​ = ​$7.15

Enter the formulas for the spreadsheet that implements this model.

$6,100.00 Max Percentage 0.28 $320.00 1 Total Monthly Gross Income 2 Allowable Monthly Housing Expenditure 3 Total Non-Mortgage Housing Expenses 4 Affordable Monthly Mortgage Payment 5 Monthly Installment Debt 6 Total Affordable Monthly Debt Payments $500.00 Max Percentage 0.36 7 Affordable Monthly Mortgage 8 Monthly Payment per $1000 mortgage $7.15 9 Maximum that can be borrowed 10 Down Payment 15% 11 What one can afford to spend on a house

Answer 1

Answer:

Gross income refers to the initial point pertaining to which determination of the state and federal income taxes for a corporation, individual, trust, and estate whether nonresident or resident is undertaken. Gross income is the aggregate sum of all the income received from different sources.

Household expenditure refers to the amount pertaining to final consumption which is taken into consideration by a resident in order to comply with the daily need such as miscellaneous services, leisure, health costs, durable goods, transport, energy, housing, clothing, and food.

The computation of the amount which an individual can afford to spend the same on household expenditure is done with the appropriate formula using a spreadsheet is shown below:

A B с D 6100 Max Percentage 0.28 =B1%D2 320 =B2-B3 500 1 Total Monthly Gross Income 2 Allowable Monthly Housing Expenditure 3 Total Non-Mortgage Housing Expenses 4 Affordable Monthly Mortgage Payment 5 Monthly Installment Debt 6 Total Affordable Monthly Debt Payment 7 Affordable Monthly Mortgage 8 Monthly Payment Per $1,000 Mortgage 9 Maximum that can be borrowed 10 Down payment 11 What one can afford to spend on a house =B1*D6-(B5+B3) Max Percentage 0.36 =MIN(B4,B6) 7.15 =B7/B8*1000 0.15 =B9/(1-B10)

The Corresponding Values are:

Hence, it is ascertained that the amount which an individual can afford for the purpose of expenditure accounts for $226,408,89