Question

Let's assume you just received a check out of nowhere that is enough to pay the downpayment and closing costs for a house. Assuming your career as a Financial Analyst for a bank earned $75,350 in 2010 and has increased at the rate of inflation.

All calculations must be done in Excel, input each number only once, in other words, use Excel functions and formulas and let Excel do the math.  

Assuming an average inflation rate of 1.5%, what is the salary in 2020. Use the Excel FV function. You will get a negative number, ignore the sign, the explanation will be in a future chapter. What is the monthly gross income of your answer in 1 above? Remember to let Excel do your calculations. Lenders use the front-end ratio as one of the measures of determining your maximum monthly payment allowed for a mortgage. If this lender uses 25% as the front-end ratio, what is the your maximum monthly mortgage payment? Lenders also use the back-end ratio as the maximum monthly total debt allowed including your mortgage payment. If this lender uses 40% as the front-end ratio and you have $500 as debt repayment, what is left for a mortgage payment?   (hint: maxdebt=(monthly income * .40) - existing debt repayment) You are required to use the smaller of the two, what is the maximum payment you will be allowed to purchase a house? Your banker tells you the maximum sales price you can afford is 400,000 with 20% down payment at a 3.2% annual rate (approx 0.27% monthly) with monthly payments over 30 years (360 payments). How much is your downpayment? What is the balance which is your loan amount? Use the PMT function in Excel to calculate your monthly payments. Hint: use the PMT function in Excel. Only need answers to 5 and 6.

Answer 1

Annual income in 2020 = annual income in 2010*(1+inflation rate)^10 = 75,350*(1+1.50%)^10 = 87,446.75

Monthly gross income = annual income/12 = 87,446.75/12 = 7,287.23

Front end ratio = 25% so monthly mortgage payment = front end ratio*monthly gross income

= 25%*7,287.23 = 1,821.81

If fron end ratio (with debt) = 40% then monthly mortgage payment = 40%*monthly gross income - debt payment

= (40%*7,287.23) - 500 = 2,414.89

Ans.5). Smaller of the two payments is 1,821.81 so that is the maximum payment which you will be allowed.

Ans.6). Down payment = down payment percentage*house price = 20%*400,000 = 80,000

Ans.7). Loan balance = house price - down payment = 400,000 - 80,000 = 320,000

Ans.8). PV = -320,000; N = 360; rate = 3.2%/12 = 0.267%, solve for PMT.

Monthly payment = 1,383.89 (Note: this answer will be 1,390.90 if monthly rate is rounded off to 0.27% instead of the exact monthly rate)