Lenders in global financial institutions generally allow clients to borrow as much as they believe borrowers can afford, based on their income, debts, and credit history. When deciding whether or not a potential buyer qualifies for a first mortgage on a home, lenders usually look at two ratios, called the front-end and the back-end ratios. Each ratio generally produces a different home price that a buyer can afford. The maximum home price a buyer can afford is the lesser of the two affordable home prices produced by the two ratios.

The front-end ratio is the monthly cost of ownership, which includes the monthly payments on the mortgage (principal plus interest), taxes, insurance, and any home-owners association dues, as a percentage of the buyer's monthly income. Lenders used to limit front-end ratios up to 33 percent, but later they allowed up to 40 percent.

The back-end ratio is the sum of the monthly cost of ownership (as in part a) plus other debt payments, such as loans for cars, furniture, and home appliances, as a percentage of the buyer's monthly income. Lenders used to limit back-end ratios up to 36 percent, but later they allowed up to 42 percent (and sometimes higher).

* * * * * * * * * ** * * * ** * * * ** * * * ** * * * * * * * * ** * * * ** * * * ** * * * *

Marilyn and Paul Jones are potential first-time home owners. Their combined annual income is $115,000. They are making $510 monthly payments on a 3-year old car and $90 monthly payments for the furniture they have had in the apartment they have rented for the past two years. They have saved enough money to make a down payment of $90,000 on the purchase of a home.

The annual cost of home ownership and liability insurance would be 0.5% of the selling price of the home, and the annual taxes would be 1% of the selling price of the home. The lender will charge an annual interest rate of 6% on a conventional 30-year first mortgage.

a.Using the guide lines above, what is the maximum mortgage and home price Marilyn and Paul can afford? Include both a front-end and back-end analysis side-by-side on a single worksheet. Use maximum allowable values of 40% for the front-end ratio and 42% for the back-end ratio. Show all input data and all calculations or results. Use an IF statement to identify the maximum mortgage and home price Marilyn and Paul can afford.

b.Suppose Marilyn and Paul used $10,000 to pay off the entire balance of their car and furniture loans, thereby reducing their home down payment to $80,000.How would this change the maximum mortgage and home price they can afford? Show your Excel calculations.