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 monthly gross income. From this, you must subtract total nonmortgage housing exponkos, 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 det 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 38% 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 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,800 Nonmortgage housing expenses $370 Monthly instaliment debt-$700 Monthly payment per $1,000 mortgage - $6.75 Enter the formulas for the spreadsheet that implements this model. D B $6,800.00 Max Percentage 0.28 $370.00 1 Total Monthly Gross income 2 Allowablo Monthly Housing Expenditure 3 Total Non-Mortgage Housing Expenses 4 Affordable Monthly Mortgage Payment 5 Monthly installment Debt 6 Total Affordable Monthly Debt Payments 7. Affordable Monthly Mortgage $700.00 Max Percentage 0,36 7 Affordable Monthly Mortgage 8 Monthly Payment per $1000 mortgage $6.75 9 Maximum that can be borrowed 10 Down Payment 20% 11 What one can afford to spend on a house Complete the following spreadsheet model, (Type Integers or decimals rounded to two decimal places as needed.) B 1 Total Monthly Gross Income $6,800.00 HUI MUUUUUUUUUUUUUUUUUUUUUUU B 1 Total Monthly Gross Income $6,800.00 2 Allowable Monthly Housing Expenditure $1 3 Total Non-Mortgage Housing Expenses $370.00 4 Affordable Monthly Mortgage Payment 5 Monthly Installment Debt $700.00 6 Total Affordable Monthly Debt Payments 7 Affordable Monthly Mortgage 8 Monthly Payment per $1000 mortgage $6.75 9 Maximum that can be borrowed Time R $ $370.00 $700.00 $ 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 7 Affordable Monthly Mortgage 8 Monthly Payment per $1000 mortgage 9 Maximum that can be borrowed 10 Down Payment 11 What one can afford to spend on a house $ $6.75 $[ 20% ord pe can allora to ise. Lender guidelines suggest that expenditure should be no more than 28% of monthly gross income. From this, you must subtract total nonmortgage housing expenses, whic property taxes and any other additional expenses. This defines the affordable monthly mortgage payment. In addition, guidelines also sugge payments, including housing expenses, should not exceed 36% of gr This is calculated by subtracting total nonmortgag installment debt, such as car loans, student loans, credit card debt, a of total monthly gross income. The smaller of the a and the total affordable monthly debt payments is the affordable mon culate the maximum that can be borrowed, find the mortgage based on the current interest rate and duration of the loan monthly mortgage amount by this monthly payme Assuming a 20% down payment, the maximum price of a house wou nortgage divided by 0.8. Use the following data tot =B1/D6 Total monthly gross income = $6,800 Nonmortgage housing expenses = $370 Monthly installment debt = $700 =B1/D2 Monthly payment per $1,000 mortgage = $6.75 =B1D6 =B1'D2 Enter the formulas for the spreadsheet that implements this mo B =B2-B3 D Max Percentage 0.28 $370.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 7 Affordable Monthly Mortgage $700.00 Max Percentage 0.36 and the total affordable monthly debt payments is the affordable mor mortgage based on the current interest rate and duration of the loan Assuming a 20% down payment, the maximum price of a house wou Total monthly gross income = $6,800 Nonmortgage housing expenses = $370 Monthly installment debt = $700 Monthly payment per $1,000 mortgage = $6.75 sf total monthly gross income. The smaller o culate the maximum that can be borrowed, monthly mortgage amount by this monthly Mortgage divided by 0.8. Use the following da =B2+B3 =B3+B5 =B2-B3 Enter the formulas for the spreadsheet that implements this mo =B3D2 B -B1-B3 D -B36D6 ercentage 0.28 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 7 Affordable Monthly Mortgage $700.00 Max Percentage 0.36 Total monthly gross income $5,800 Nonmortgage housing expenses = $370 Monthly installment debt = $700 Monthly payment per $1,000 mortgage = $6.75 =B1-D2-(B5+B3) =B1D2-B5+B3 Enter the formulas for the spreadsheet that implement =B1D6-B5+B3 D =(B1-B5-B3) D2 srcentage 0.28 =(B1-B5-B3)*D6 A 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 7 Affordable Monthly Mortgage | =B1-06-(B5+B3) Max Percentage 0.36 Enter the formulas for the spreadsheet that implem =MAX(B4,B6) =AVERAGE(B4,B6) ercentage 0.28 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 7 Affordable Monthly Mortgage =MIN(B4,B6) =B6-B4 ercentage 0.36 culate the maximum that can be borrowed, find th monthly mortgage amount by this monthly payme Sortgage divided by 0.8. Use the following data to =B5/B8 and the total affordable monthly debt payments is the affordabl mortgage based on the current interest rate and duration of the Assuming a 20% down payment, the maximum price of a hous Total monthly gross income = $6,800 Nonmortgage housing expenses = $370 Monthly installment debt = $700 Monthly payment per $1,000 mortgage = $6.75 =B3/B8*1000 =B7/B8*1000 =B5/B8*1000 =B7/B8 Prcentage 0.36 =B3/B8 5 Monthly Installment Debt 6 Total Affordable Monthly Debt Payments 7 Affordable Monthly Mortgage 8 Monthly Payment per $1000 mortgage 9 Maximum that can be borrowed 10 Down Payment 11 What one can afford to spend on a house 20% Complete the following spreadsheet model. Monthly installment debt = $700 Monthly payment per $1,000 mortgage = $6.75 =B9'B10 =B9' (100%-B10) =B9/(100%-B10) =B9/(100%-D2) ercentage 0.36 5 Monthly Installment Debt 6 Total Affordable Monthly Debt Payments 7 Affordable Monthly Mortgage 8 Monthly Payment per $1000 mortgage 9 Maximum that can be borrowed 10 Down Payment 11 What one can afford to spend on a house =B9/(100%-06) =B9/B10 Complete the following spreadsheet model Time R