Develop a spreadsheet model to determine how much a person or a couple can afford to...
Fantastic news! We've Found the answer you've been seeking!
Question:
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 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 $5,500 Nonmortgage housing expenses = $330 Monthly installment debt = $400 Monthly payment per $1,000 mortgage = $6.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 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 Complete the following spreadsheet model. B C D $5,500.00 =B1*D2 $330.00 =B2-B3 $400.00 Max Percentage 0.28 =B1*D6-(B5+B3) Max Percentage 0.36 =MIN(B4,B6) $6.75 =B7/B8*1000 15% =B9/(100%-B10) (Type integers or decimals rounded to two decimal places as needed. 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 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 B $5,500.00 $ $330.00 $ $400.00 $ $ $6.75 $ 15% $ 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 $5,500 Nonmortgage housing expenses = $330 Monthly installment debt = $400 Monthly payment per $1,000 mortgage = $6.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 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 Complete the following spreadsheet model. B C D $5,500.00 =B1*D2 $330.00 =B2-B3 $400.00 Max Percentage 0.28 =B1*D6-(B5+B3) Max Percentage 0.36 =MIN(B4,B6) $6.75 =B7/B8*1000 15% =B9/(100%-B10) (Type integers or decimals rounded to two decimal places as needed. 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 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 B $5,500.00 $ $330.00 $ $400.00 $ $ $6.75 $ 15% $
Expert Answer:
Posted Date:
Students also viewed these finance questions
-
Competency Review and Instructions Competency 6: Students will Engage with Individuals, Families, Groups, Organizations, and Communities within generalist social work practice. Social workers...
-
Learning Target 14: I can apply properties and theorems involving angles inscribec Determine the measure of the identified arc or inscribed angle. Type in the va number only, nothing else! (Example:...
-
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...
-
How should employers that operate in different states and cities deal with lack of uniformity in employment laws?
-
Shelley and Co. has the following balance sheet elements as of December 31, 2012. Required: Compute the total amount of: 1. Current assets. 2. Long-term assets. 3. Current liabilities. 4. Long-term...
-
Your Personal Balance Sheet and Income Statement on page 56, we learned that individuals have financial statements just like firms. Prepare your personal balance sheet using the following items: (i)...
-
How do cultures become dangerous, as in this case? Eastern Medical Center (EMC) struggled with its quality indicators, consistently ranking in the bottom quartile of its corporations quality...
-
Consider each of the following unrelated cases, involving the ownership of the common shares of Canadian-controlled private corporations, for taxation years of all corporations ending on December 31:...
-
Movies Unlimited Inc. Movies Unlimited, Inc., a family-owned wholesale company, is located in Fort Myers, Florida and sells DVDs, CD's, books, and magazines at wholesale prices to convenience stores...
-
1. Calculate the hydrogen ion concentration,[H + ], for the two weak acids(pH=-log[H + ], or [H + ]=antilog(-pH). If you have difficulty finding or using the antilog functionon your calculator,...
-
It is common to design columns at reflux ratios slightly above (L/D) opt. Use a curve of total cost/yr vs. L/D to explain why there is not a large cost penalty if L/D > (L/D) opt.
-
The New England Trading Company, whose accounting year ends on December 31, had the following normal balances in its general ledger at December 31: During the year, the accounting department prepared...
-
Detroit Foundry, a large manufacturer of heavy equipment components, has determined the following activity cost pools and cost driver levels for the year: The following data are for the production of...
-
Water at \(10^{\circ} \mathrm{C}\) flows in a 3-cm-diameter pipe at a velocity of \(1.25 \mathrm{~m} / \mathrm{s}\). The Reynolds number for this flow is (a) 19,770 (b) 23,520 (c) 28,680 (d) 32,940...
-
The following account balances, in alphabetical order, are from the general ledger of The Nina Service Company at January 31. The firm's business began on January 1 . All accounts have normal...
-
Explain Prospect Theory and explain how this theory accounts for the framing effect.
-
3.16. For a system with non-identical service rates (see Sect. 3.5) and a limit of N jobs in the system (Eq. 3.13), obtain an expression for the mean service time per job, E[Ts], as a function of the...
-
Explain the tllree rules Fadiman proposes for dealing with brlbeiy. Do they resolve the business and ethical dilemmas that U.S. businesspeople may lace overseas?
-
Fadlman lists several reasons why tt may be in the commercial interest of Americans to play tile brlbeiy game. Should they do it? Does playing the bribery game make good business sense? Is tt morally...
-
Americans view bribery as immoral. But if bribery is an accepted practice in a foreign country, is it still immoral? Explain your answer. Why do we consider bribery wrong in the first place?
Study smarter with the SolutionInn App