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 affordabl 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 = $7,000 Nonmortgage housing expenses $350 Monthly installment debt = $400 Monthly payment per $1,000 mortgage = $7.75 Enter the formulas for the spreadsheet that implements this model. 1 Total Monthly Gross Income 2 Allowable Monthly Housing Expenditure B D $7,000.00 Max Percentage 0.28 $350.00 3 Total Non-Mortgage Housing Expenses 4 Affordable Monthly Mortgage Payment 5 Monthly Installment Debt $400.00 6 Total Affordable Monthly Debt Payments Max Percentage 0.36 7 Affordable Monthly Mortgage 8 Monthly Payment per $1000 mortgage $7.75 9 Maximum that can be borrowed 10 Down Payment 11 What one can afford to spend on a house 20%

FINANCIAL ACCOUNTING
10th Edition
ISBN:9781259964947
Author:Libby
Publisher:Libby
Chapter1: Financial Statements And Business Decisions
Section: Chapter Questions
Problem 1Q
icon
Related questions
Question
None
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 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 = $7,000
Nonmortgage housing expenses = $350
Monthly installment debt = $400
Monthly payment per $1,000 mortgage = $7.75
Enter the formulas for the spreadsheet that implements this model.
A
1 Total Monthly Gross Income
2 Allowable Monthly Housing Expenditure
3 Total Non-Mortgage Housing Expenses
B
C
D
$7,000.00
Max Percentage
0.28
$350.00
4 Affordable Monthly Mortgage Payment
5 Monthly Installment Debt
$400.00
6 Total Affordable Monthly Debt Payments
Max Percentage
0.36
7 Affordable Monthly Mortgage
8 Monthly Payment per $1000 mortgage
$7.75
9 Maximum that can be borrowed
10 Down Payment
11 What one can afford to spend on a house
20%
Transcribed Image Text: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 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 = $7,000 Nonmortgage housing expenses = $350 Monthly installment debt = $400 Monthly payment per $1,000 mortgage = $7.75 Enter the formulas for the spreadsheet that implements this model. A 1 Total Monthly Gross Income 2 Allowable Monthly Housing Expenditure 3 Total Non-Mortgage Housing Expenses B C D $7,000.00 Max Percentage 0.28 $350.00 4 Affordable Monthly Mortgage Payment 5 Monthly Installment Debt $400.00 6 Total Affordable Monthly Debt Payments Max Percentage 0.36 7 Affordable Monthly Mortgage 8 Monthly Payment per $1000 mortgage $7.75 9 Maximum that can be borrowed 10 Down Payment 11 What one can afford to spend on a house 20%
Expert Solution
trending now

Trending now

This is a popular solution!

steps

Step by step

Solved in 3 steps with 2 images

Blurred answer
Recommended textbooks for you
FINANCIAL ACCOUNTING
FINANCIAL ACCOUNTING
Accounting
ISBN:
9781259964947
Author:
Libby
Publisher:
MCG
Accounting
Accounting
Accounting
ISBN:
9781337272094
Author:
WARREN, Carl S., Reeve, James M., Duchac, Jonathan E.
Publisher:
Cengage Learning,
Accounting Information Systems
Accounting Information Systems
Accounting
ISBN:
9781337619202
Author:
Hall, James A.
Publisher:
Cengage Learning,
Horngren's Cost Accounting: A Managerial Emphasis…
Horngren's Cost Accounting: A Managerial Emphasis…
Accounting
ISBN:
9780134475585
Author:
Srikant M. Datar, Madhav V. Rajan
Publisher:
PEARSON
Intermediate Accounting
Intermediate Accounting
Accounting
ISBN:
9781259722660
Author:
J. David Spiceland, Mark W. Nelson, Wayne M Thomas
Publisher:
McGraw-Hill Education
Financial and Managerial Accounting
Financial and Managerial Accounting
Accounting
ISBN:
9781259726705
Author:
John J Wild, Ken W. Shaw, Barbara Chiappetta Fundamental Accounting Principles
Publisher:
McGraw-Hill Education