EXCEL PRBLEMS

SPREADSHEET FUNCTIONS AND ANALYSIS

Randy and Brandi (R&B) have moved to the Michiana area recently. They are debating whether they should purchase a house or rent an apartment. They are not sure about how long they would stay in the Michiana area. Based on their preferences and initial research they found following housing details about the Michiana area. Average house price: $200000 Average apartment rental: $750 Average increase in rents/year: 5% Average increase in house value/year: 2% Housing loan term: 30 years Current housing market conditions require them to pay at least 20% down payment. Average standard interest rate on housing loans is 5%. Borrowers also need to pay additional interest rates based on their credit profile. Additional interest rates for a loan depends on the credit score and current information that they have received from the bankrate.com suggest following rates table. Assume that there are no other mortgage loan related costs (i.e. closing costs, points etc…) Credit score Additional Interest rate >=800 0% 750-799 0.15% 700-749 0.50% 650-699 1.5% 600-649 3.0%

(1) If R&B decide to purchase a house, what will be their monthly housing payments? R&B’s credit score is 720. However, your worksheet should be able to determine monthly payments for any credit score.

(2) Tightening credit situations in the housing industry may require banks to decide to double the additional interest rates if the annual household income is less than $100000 and triple the additional interest rates if the annual household income is less than $50000. Incorporate this in your results and determine new monthly household payments. R&B’s combined household income is $74000. Your worksheet should be able to determine monthly payments for any credit score.

(3) What is the gain from selling a house after 30 years? What is the gain from selling a house after 5 years? What is the gain if the house value increases only by 1% every year?

(4) What should be the base interest rate, if the maximum desired monthly payment is $850? What should be the down payment, if the maximum desired monthly payment is $850?